如何修复大型数据库中的“超时”错误，从连接表中查找第二个修改后的结果？

Question

I have 3 MySQL Tables user, board and pin. a user tables have around 66 columns. A pin table have 89 columns and A board table have 12 columns. A user table have almost 35000 users, pin have .3 million records and boards have 7k boards. A user can have multi boards and A board can contain multi-pins. I need last modified piece of each user so i could send them email if there is no update from a very long period of time.

I have tried the following code for a moment in codeigniter.

$this->db->select("t1.id as pin_id, t1.id, t1.user_id, `t1.title`,t1.image, t1.description, t1.profession_tag, t1.industry_tag, t1.collaborators, `t2.title` as board_title, t3.email, t3.username, t3.firstname");
        $this->db->from("pin as t1");
        $this->db->join('board as t2', 't1.board_id = t2.id', 'left');
        $this->db->join('user as t3', 't1.user_id = t3.id', 'left');
        $this->db->where('t3.status', 1);
        $this->db->where('t3.hidden_profile', 1);
        $this->db->where('t1.status', 1);
        $this->db->group_by('t1.user_id');
        $this->db->limit($limit, $start);
        $query = $this->db->get();
        return $query->result_array();

But in cases a user have more than 1000 pins its taking so much time. Please suggest what rest i can do to improve it and get the result faster. I am also not getting the last modified result yet by the query. I don't know how it will work with Having clause.

Answer 1

One suggestion is below. Use Sub queries in the Join statement. See this answer by Trendfischer and An Essential Guide to MySQL Derived Table

$this->db->select("t1.id as pin_id, t1.id, t1.user_id, `t1.title`,t1.image, t1.description, t1.profession_tag, t1.industry_tag, t1.collaborators, `t2.title` as board_title, t3.email, t3.username, t3.firstname");
$this->db->from("pin as t1");
$this->db->join('(SELECT id, title FROM board) AS t2', 't1.board_id = t2.id', 'left');
$this->db->join('(SELECT id, email, username, firstname FROM user status = 1 AND hidden_profile = 1) AS t3', 't1.user_id = t3.id', 'left');
$this->db->where('t1.status', 1);
$this->db->group_by('t1.user_id');
$this->db->limit($limit, $start);
$query = $this->db->get();
return $query->result_array();