MySQL Forums
Forum List  »  Optimizer & Parser

Re: Index optimizations for huge table
Posted by: Sergey Petrunya
Date: May 22, 2007 03:38PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
3012
April 30, 2007 08:57PM
Re: Index optimizations for huge table
2368
May 22, 2007 03:38PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.