Slow query with straight_join for small result

Question

I have a performance-problem with a query in my application and I don’t understand the behavior of mysql.

The query consists of different joins, especially the join to the mentioncache-table. If the join to the mentioncache-table is a simple join, the query takes about three seconds (for a profile which has about 80.000 records in the mentioncache-table). If this join is a straight_join, the query just takes about 0.001 seconds.

The query is:

SELECT SQL_NO_CACHE `Mention`.`id`, `Mention`.`title`, `Mention`.`title_text`, `Mention`.`content_text`, `Mention`.`url`, `Mention`.`root_url`, `Mention`.`sub_type`, `Mention`.`indexed`, `Mention`.`plain_host_url`, `Favoureditem`.`foreign_id`, `Visiteditem`.`foreign_id`, `Visiteditem`.`created`, `Mentionfeedscore`.`score`, Image.id, Image.model, Image.foreign_key, Image.dirname, Image.basename 
FROM `mentions` AS `Mention` 

LEFT JOIN attachments AS `Image` ON (`Image`.`foreign_key` = `Mention`.`id` AND `Image`.`model` = 'Mention') 

LEFT JOIN favoureditems AS `Favoureditem` ON (`Favoureditem`.`model` = "Mention" AND `Favoureditem`.`foreign_id` = `Mention`.`id` AND `Favoureditem`.`owner_id` = 803) 

LEFT JOIN visiteditems AS `Visiteditem` ON (`Visiteditem`.`model` = "Mention" AND `Visiteditem`.`foreign_id` = `Mention`.`id` AND `Visiteditem`.`owner_id` = 803) 

LEFT JOIN mentionfeedscores AS `Mentionfeedscore` ON (`Mentionfeedscore`.`mention_id` = `Mention`.`id` AND `Mentionfeedscore`.`feed_id` = 'iparkmedia') 

STRAIGHT_JOIN mentioncache AS `Mentioncache` ON (`Mentioncache`.`mention_id` = `Mention`.`id` AND `Mentioncache`.`profile_id` = 803) 

WHERE `Mention`.`language` = ('de') AND 

DATE(`Mention`.`indexed`) BETWEEN "2012-11-04" AND "2012-12-04" AND 

`Mention`.`sub_type` IN ('NEWSSITE_TVRADIO', 'NEWSSITE_AGENCY') 

ORDER BY `Mention`.`indexed` DESC 
LIMIT 0, 10

The explain of this query is:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  Mention index   PRIMARY,mention_id,id_indexed,id_sub_type,id_sub_type_indexed,id_sentimentlevel,id_sentimentlevel_indexed   indexed 8   NULL    10  Using where
1   SIMPLE  Image   ref foreign_key,model_foreign_key   foreign_key 66  clippingcroc.Mention.id,const   1    
1   SIMPLE  Favoureditem    ref model_foreign_id_owner_id   model_foreign_id_owner_id   163 const,clippingcroc.Mention.id,const 1   Using index
1   SIMPLE  Visiteditem ref model_foreign_id_owner_id   model_foreign_id_owner_id   163 const,clippingcroc.Mention.id,const 1    
1   SIMPLE  Mentionfeedscore    ref mention_id,feed_id  mention_id  4   clippingcroc.Mention.id 1    
1   SIMPLE  Mentioncache    eq_ref  mention_id_profile_id,mention_id,profile_id mention_id_profile_id   8   clippingcroc.Mention.id,const   1   Using index

The explain for the query with the normal join is:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  Mentioncache    ref mention_id_profile_id,mention_id,profile_id profile_id  4   const   133001  Using temporary; Using filesort
1   SIMPLE  Mention eq_ref  PRIMARY,mention_id,id_indexed,id_sub_type,id_sub_type_indexed,id_sentimentlevel,id_sentimentlevel_indexed   PRIMARY 4   clippingcroc.Mentioncache.mention_id    1   Using where
1   SIMPLE  Image   ref foreign_key,model_foreign_key   foreign_key 66  clippingcroc.Mentioncache.mention_id,const  1    
1   SIMPLE  Favoureditem    ref model_foreign_id_owner_id   model_foreign_id_owner_id   163 const,clippingcroc.Mentioncache.mention_id,const    1   Using index
1   SIMPLE  Visiteditem ref model_foreign_id_owner_id   model_foreign_id_owner_id   163 const,clippingcroc.Mentioncache.mention_id,const    1    
1   SIMPLE  Mentionfeedscore    ref mention_id,feed_id  mention_id  4   clippingcroc.Mentioncache.mention_id    1    

The strange thing is: For a profile which has just has about 400 records in the mentioncache-table, the performance is just the other case around. In this case the query with the normal join takes about 0.015 seconds and for the straight_join about 1,5 seconds. For a profile which has just 5 records in the mentioncache (and no result for the query), the straight_join even takes about 15 seconds (and 0.01 seconds for the normal join).

So why is there such a bad performance when there are less results in the straight_join-case? I don’t understand this.

And what is the best way to get a good performance of this query?

Thanks for some help!

Best regards,

Timo

Answer 1

The query optimizer is free to rearrange the join-order of tables in a query to any logically-consistent sequence based on its estimates of the costs of the query... unless you use STRAIGHT_JOIN, which forces the optimizer to read the left table before the right table in that particular join. (In MySQL, you can also SELECT STRAIGHT_JOIN ... which forces all the tables to be handled in the order specified in the FROM clause).

The reason for doing this is for force the optimizer to choose a plan that you know to be better than the one it's choosing on its own. In your case, sometimes that's a better plan, and sometimes it isn't.

You only posted one EXPLAIN, but I strongly suspect you'll find the EXPLAIN to be different for the query without the STRAIGHT_JOIN, which will probably make the performance discrepancy more readily apparent. It's almost inconceivable that the plan is the same, since the performance is so different.

There's another problem with the design of your query, which might be contributing to the poor performance when the query plan changes:

WHERE ...
DATE(`Mention`.`indexed`) BETWEEN "2012-11-04" AND "2012-12-04"

This is syntactically valid, but bad practice, because you're telling the server "for each row we haven't eliminated with other attributes in the WHERE clause or joins, evaluate Mention.indexed using the DATE() function and eliminate the rows where the resulting answer is not between "2012-11-04" AND "2012-12-04".

Change to this:

WHERE ...
`Mention`.`indexed` BETWEEN '2012-11-04' 
                        AND DATE_SUB(DATE_ADD('2012-12-04',INTERVAL 1 DAY),INTERVAL 1 SECOND)

The optimizer will evaluate the two expressions only once, and the second expression evaluates to '2012-12-04 23:59:59'. So now you have two constants, which can be used to match rows with the index on Mention.indexed using a range scan if the optimizer thinks that's a good idea. As your query is written, that index can't be used for filtering rows.

"But wait," someone says, "the EXPLAIN says it's using that index." Yes, it's using it to sort the results, but it's not using it for eliminating non-matching rows, because putting a formula on the left side of the where clause almost always eliminates the possibility of an index being used on the columns being passed as arguments into the function.

When you see Using where in the Extra column, that is the optimizer saying "With the query plan I've selected, I'm going to have to ask the underlying storage engine for more rows from this table than we actually want, and filter them at the MySQL layer using something from the WHERE clause to find what we actually need."

Answer 2

Another issue has to do with the ENGINE used. Both MyISAM and InnoDB gather "statisics" on which they base the query plan. However, they do it in radically different ways.

ANALYZE TABLE will recompute the stats. Sometimes (not always) this will change the query plan.