-----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]
