-----Original Message----- From: Max Michaels [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 08, 2004 8:32 AM To: [EMAIL PROTECTED] Subject: slow query issues
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; The compound index I see that is usefull is ymdh,is_ym_advistiser,size_id If the range covers more then 30% of the data in the table mysql will turn off index lookups. Set max_seeks_keys=100 in you're my.cnf file and or ANALYZE TABLE to recompute the cardinality of the keys. Additionally you can perform tighter ranges on ymdh and use a script to simulate the additional summation to make it faster. 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]