CODEWITHSUNDEEP
mysqlperformancejoin
user2783132
user2783132

Reputation: 225

Query takes too much time with JOIN

I need a little help improving the following query performance

SELECT * 
FROM   dle_pause 
       LEFT JOIN dle_post_plus 
              ON ( dle_pause.pause_postid = dle_post_plus.puuid ) 
       LEFT JOIN dle_post 
              ON ( dle_post_plus.news_id = dle_post.id ) 
       LEFT JOIN dle_playerfiles 
              ON ( dle_post.id = dle_playerfiles.post_id ) 
WHERE  pause_user = '2';

it takes 3 rows in set (0.35 sec) the problem is with the third join. one of the rows don't have dle_post.id = dle_playerfiles.post_id so it scans whole the table.

looks like I have all needed indexes

+----+-------------+-----------------+--------+----------------------------------+---------+---------+-----------------------------------+--------+------------------------------------------------+
| id | select_type | table           | type   | possible_keys                    | key     | key_len | ref                               | rows   | Extra                                          |
+----+-------------+-----------------+--------+----------------------------------+---------+---------+-----------------------------------+--------+------------------------------------------------+
|  1 | SIMPLE      | dle_pause       | ALL    | pause_user                       | NULL    | NULL    | NULL                              |      3 | Using where                                    |
|  1 | SIMPLE      | dle_post_plus   | ref    | puuid                            | puuid   | 36      | func                              |      1 | Using where                                    |
|  1 | SIMPLE      | dle_post        | eq_ref | PRIMARY                          | PRIMARY | 4       | online_test.dle_post_plus.news_id |      1 | NULL                                           |
|  1 | SIMPLE      | dle_playerFiles | ALL    | ix_dle_playerFiles__post_id_type | NULL    | NULL    | NULL                              | 131454 | Range checked for each record (index map: 0x2) |
+----+-------------+-----------------+--------+----------------------------------+---------+---------+-----------------------------------+--------+------------------------------------------------+

Upvotes: 0

Views: 283

Answers (2)

number50
number50

Reputation: 153

If you have not put index on dle_playerfiles' post_id, then put index on it.
If you have already put an index on it, then in your query at last join write 'use index' like this:

 SELECT * 
  FROM 
     dle_pause 
   LEFT JOIN dle_post_plus 
          ON ( dle_pause.pause_postid = dle_post_plus.puuid ) 
   LEFT JOIN dle_post 
          ON ( dle_post_plus.news_id = dle_post.id ) 
   LEFT JOIN dle_playerfiles **use index(post_id)**
          ON ( dle_post.id = dle_playerfiles.post_id ) 
   WHERE 
     pause_user = '2';

This will use index for fourth table also. Right now your explain show that it is not using any index on fourth table and hence scans 131454 rows.

Upvotes: 1

Scription
Scription

Reputation: 645

I can suggest two alternative for solving this.

First alternative:

Create a temporary tables that contain only non NULL values for the key you comparing with LEFT join.

Something like this:

select *
into #dle_post_plus 
where pause_postid is not null

Do it for all three tables.

Then use your original query on the temporary tables which does not include NULL values.

Second alternative: Create an index for each key you are comparing in the left join, in this way the index will do the job for you.

Off course you can always combine the two methods I suggested.

Upvotes: 0

Related Questions