Quote
Campaign Reach Query
select clientid, count(*), count(distinct U1) from phpads_adviews group by clientid
Full Report
SELECT clientid,bannerid,affiliateid,zoneid,COUNT(*),COUNT(DISTINCT U1)
FROM phpads_adviews
GROUP BY bannerid,zoneid
Those two are problematic because there is no WHERE clause. MySQL 5.0 and up have Loose Index Scan optimization (http://dev.mysql.com/doc/refman/5.0/en/loose-index-scan.html) for certain kinds of GROUP BY queries, but that will not work for your queries because it doesn't allow to calculate COUNT(*) and COUNT(DISTINCT U1).
The only way MySQL could use index for such query is to use "index" access method where MySQL runs through entire index instead of the entire table. To make that work, you'll need to add an index on all columns that are used by the query. That may or may not help, depending on whether the index happens to be "almost clustered", key cache size, your setup, etc etc.
Quote
Full Report
SELECT ... WHERE t_stamp>='20070423000000' AND t_stamp<'20070430000000'
Here INDEX(t_stamp) might help as WHERE clause contains a limit on # of rows MySQL will need to examine. Whether it will actually help depends on whether the WHERE clause selects a small part of table or not.
Sergey Petrunia, Software Developer
MySQL AB, www.mysql.com
My blog:
http://s.petrunia.net/blog