CODEWITHSUNDEEP
mysqljoinexplain
user1927652
user1927652

Reputation: 91

mysql wrong explain join large table

I have a 4Gb table and when I try to select row from it with left join on another table it creates very large temporary table on disc. My sql is:

 select * 
   from ads 
   left join theme 
     ON ads.theme_id = theme.id 
  ORDER BY ads.id DESC 
  limit 1

table ads has 4Gb of data but table theme is very small. When I try explain this query mysql doesn't shows me that temporary table will be created but it does. And when I use sql

SELECT * 
  from ads, 
       theme 
 where ads.theme_id = theme.id 
 ORDER BY ads.id DESC 
 LIMIT 1

mysql runs very fast however this query has same explanation by mysql.

I use innodb engine and I have only primary indexes.

Upvotes: 0

Views: 68

Answers (1)

Barmar
Barmar

Reputation: 780994

Add an index on ads.theme_id. Otherwise, it has to scan the entire ads table to check this column against theme.id.

Upvotes: 2

Related Questions