CODEWITHSUNDEEP
mysqlsqllinuxubuntucentos
user814584
user814584

Reputation: 325

MySql Different Execution Paths for Same Query

I have two servers (linode 3072 vps), one (older) has ubuntu 11.04 + Mysql 5.5.32 and the other (newer) has centos 6.2. + Mysql 5.5.36. The My.cnf files are same as well. However, when I run the same query on the same db (straight export/import) then I got 2 different response times and execution paths from the 2 servers.

Older one with faster response.

1   SIMPLE  ch  ref PRIMARY,channel_name    channel_name    122 const   1   Using where; Using temporary; Using filesort
1   SIMPLE  t   ref PRIMARY,channel_id  channel_id  4   bcc.ch.channel_id   1554    
1   SIMPLE  p   ref PRIMARY PRIMARY 4   bcc.t.entry_id  1   Using index
1   SIMPLE  c   eq_ref  PRIMARY,group_id    PRIMARY 4   bcc.p.cat_id    1   Using where

Newer one with slower response.

1   SIMPLE  ch  ref PRIMARY,channel_name    channel_name    122 const   1   Using where; Using temporary; Using filesort
1   SIMPLE  p   index   PRIMARY PRIMARY 8   NULL    25385   Using index; Using join buffer
1   SIMPLE  t   eq_ref  PRIMARY,channel_id  PRIMARY 4   bcc.p.entry_id  1   Using where
1   SIMPLE  c   eq_ref  PRIMARY,group_id    PRIMARY 4   bcc.p.cat_id    1   Using where

The big difference is in the 2nd step. The first server uses an index and only has to scan 1554 rows, where as the 2nd server uses index + join buffer and has to scan 25385 rows. Any thoughts?

Queries like this and others are causing an increase of a few seconds per page load on the new server on certain pages. I'm using varnish to serve front end, but still want to fix this issue.

Here's the sql being run

select SQL_NO_CACHE cat_name,cat_url_title, count(p.entry_id) as count
from exp_categories as c
join exp_category_posts as p on c.cat_id = p.cat_id
join exp_channel_titles as t on t.entry_id = p.entry_id
join exp_channels as ch on ch.channel_id = t.channel_id
where channel_name IN ('resources')
AND group_id = 2
group by cat_name
order by count desc
limit 5

Upvotes: 3

Views: 1419

Answers (1)

Neil Hampton
Neil Hampton

Reputation: 1883

The query optimizer in MySQL picks the indexes to used based on statistics it has on the indexes and tables. Sometimes the choice of index isn't that optimal and the query execution is different.

We have found on our database that at certain points in the day MySQL changes the execution path it uses for the same query.

You could try

analyze table exp_categories,exp_category_posts,exp_channel_titles,exp_channels ;

This sometimes improves the execution plan. Alternatively use index hints to determine which indexes are used.

Upvotes: 5

Related Questions