Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers 2

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."

share|improve this answer
    
Hi Michal, thanks for your reply. I added the explain for the „normal“ join query. You are right: In this case MySQL uses a different query plan: In the straight_join-case the mention-table is the first table and the mentioncache-table is the last table. In the normal join-case the mentioncache table is the first (!) table an the mention-table the second one. –  Timo Dec 9 '12 at 19:18
    
My “problem” is that I don’t know the “better” plan than the optimizer. Especially I don’t understand why the “fast” straight_join-query is so slowly for querys (profiles) with less (or no) data in the mentioncache-table (just testet: for a small profile the normal-join query is about 300 times faster!). Do you have any idea for this huge difference? –  Timo Dec 9 '12 at 19:18
add comment

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.

share|improve this answer
    
Hi Rick, thanks for your reply. I do a optimize table per cron every 2-4 hours, so I think the statistics of the table should be up-to-date. –  Timo Dec 9 '12 at 19:08
    
It is a waste of time to OPTIMIZE that frequently. For MyISAM, the stats are not likely to change in a month. For InnoDB, you run the risk of making the stats worse (since it does 8 'random' probes). –  Rick James Dec 11 '12 at 0:21
    
But in my case I do a lot of bulk inserts to import new data (every 10 minutes). And the result of my performance-tests was that mysql slows down very hard without this OPTIMIZE-frequence. –  Timo Dec 11 '12 at 5:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.