0

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.

Ethane Dev
  • 1
  • 1
  • Learn About indexing, partitioning and optimizing mysql databases. – Rahul Apr 29 '19 at 09:02
  • I tried indexing, Partitioning already. But nothing has worked in my case. – Ethane Dev Apr 29 '19 at 09:04
  • Which partitioning you have used? – Rahul Apr 29 '19 at 09:04
  • Also my client has clear requirement, and he is ready to move on local machine if it cannot be solve on server, but i think its like making the things much complex. – Ethane Dev Apr 29 '19 at 09:05
  • I have break the pin table into 9 partitions by pin id each partition have 30,000 pins except the last one – Ethane Dev Apr 29 '19 at 09:07
  • Not that, https://dev.mysql.com/doc/refman/5.7/en/partitioning.html This – Rahul Apr 29 '19 at 09:08
  • I have done range partitioning - https://dev.mysql.com/doc/refman/5.7/en/partitioning-types.html – Ethane Dev Apr 29 '19 at 09:11
  • Partitioning is especially useful when you can use it to separate old data, that is not used in most queries, from new data. Since we have no idea what your data is about (is it a game?), we can't tell whether your database contains old data. – KIKO Software Apr 29 '19 at 09:23
  • Its a social platform like pinterest. – Ethane Dev Apr 29 '19 at 09:29
  • Ah, that explains a lot. Where does your query select those users that haven't pinned anything for a long time? I don't see any sorting or dates. – KIKO Software Apr 29 '19 at 09:33
  • I already mentioned it on last line, i have no idea how do i implement it. I know one way of doing to select 1000 users and place it in where in condition to fetch the result on pins order by last date modified. – Ethane Dev Apr 29 '19 at 09:39
  • SELECT * FROM `pin` as t1 JOIN board as t2 ON t1.board_id = t2.id JOIN user as t3 ON t1.user_id = t3.id GROUP BY t1.user_id - This query is taking 14 seconds on local server to execute. – Ethane Dev Apr 29 '19 at 09:44
  • When i introduce Order By clause with modification_date in descending order it takes SELECT * FROM `pin` as t1 JOIN board as t2 ON t1.board_id = t2.id JOIN user as t3 ON t1.user_id = t3.id GROUP BY t1.user_id ORDER BY t1.date_modified desc; Query took 13.9808 seconds. – Ethane Dev Apr 29 '19 at 09:47
  • Use views instead of joins. Split the query – Sudhir Mishra Apr 29 '19 at 17:38

1 Answers1

0

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();
Saji
  • 1,374
  • 1
  • 12
  • 19
  • This query ``` 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 FROM pin as t1 JOIN (SELECT id, title FROM board) AS t2 ON t1.board_id = t2.id JOIN (SELECT id, email, username, firstname FROM `user` where status = 1 AND hidden_profile = 1) AS t3 ON t1.user_id = t3.id GROUP BY t1.user_id ORDER BY t1.date_modified desc; ``` has taken 15.7421 seconds to execute. – Ethane Dev Apr 29 '19 at 13:45
  • Can you include the table index details in your question? Also can you try after changing the `pin` table same like other two tables joined. ``$this->db->from("(SELECT id, user_id, title, image, description, profession_tag, industry_tag, collaborators FROM pin WHERE status = 1 ) AS t1"); Then remove the `$this->db->where('t1.status', 1);`. Test and let me know the execution time variation. – Saji Apr 29 '19 at 16:35