Hello all,
Recently, I have been seeing some strange behavior from a particular query on
my 4.0.21 mysql server. Here is the query in question:
SELECT size_id, sum(imps) imps, sum(clicks) clicks, sum(convs) convs,
sum(imp_revenue) imp_revenue, sum(click_revenue) click_revenue,
sum(conv_revenue) conv_revenue, sum(international_imps) international_imps,
sum(adjustments) / 60 adjs, sum(publisher_compensation) / 60 comp FROM
publisher_summary WHERE ymdh >= '2004-11-01 05:00:00' AND ymdh < '2004-12-01
05:00:00' AND is_ym_advertiser=0 GROUP BY size_id;
There is an index on ymdh, another on both is_ym_advertiser and ymdh, and
another on size_id. The query is using the index that is on both
is_ym_advertiser and ymdh and is scanning 7 million rows according to explain
select. Explain select also says that it is a query of type ref. The table it
is selecting from contains approximately 27 million rows.
Until a few days ago, the query would take about 2 minutes to complete. This is
a long time, but considering how often this query is run, it was acceptable.
Now this query is taking more than 30 minutes to return data and I have no idea
why. I did some testing and replicated the table to my testing environment
without the indexes. Here the query is taking the normal 2-3 minutes without
the indexes. Is it possible to determine if the index is corrupted? I also
tried to run this query adding an IGNORE INDEX to skip all the indexes in
production, but it still took forever. So this makes me think it isn't the
indexes. I don't really have a good window to run OPTIMIZE in so I just want to
see if the table or index is corrupted before I go ahead and schedule that
window. Better yet, is there a more efficient way to write this query?
Any help is greatly appreciated.
Regards,
Max
Max Michaels
Right Media, LLC� | �276 5th Avenue, Suite 605� | New York , NY 10001
212.561.6475 | mmichaels {at} rightmedia.com | www.rightmedia.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
