Hi guys,
I have a question about index usage in MySQL. I have a query: MYSQL: ([EMAIL PROTECTED]) [webstats]> EXPLAIN SELECT sum(users) as totaal_uniek, page_id FROM webstats.stats_hour where page_id LIKE 'vipPage_%' and site = 'spelpuntVip' and date > 1166353093 group by page_id ORDER BY totaal_uniek DESC LIMIT 10; +----+-------------+------------+-------+-------------------------------+--- ---+---------+------+--------+---------------------------------------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+-------------------------------+--- ---+---------+------+--------+---------------------------------------------- + | 1 | SIMPLE | stats_hour | range | date,page_id,pageId_site_date | date | 4 | NULL | 833057 | Using where; Using temporary; Using filesort | +----+-------------+------------+-------+-------------------------------+--- ---+---------+------+--------+---------------------------------------------- + 1 row in set (0.05 sec) And a table stats_hour with indexes: +------------+------------+-------------------------------+--------------+-- -----------+-----------+-------------+----------+--------+------+----------- -+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +------------+------------+-------------------------------+--------------+-- -----------+-----------+-------------+----------+--------+------+----------- -+---------+ | stats_hour | 0 | year_mon_day_hour_pageId_site | 1 | year | A | NULL | NULL | NULL | | BTREE | | | stats_hour | 0 | year_mon_day_hour_pageId_site | 2 | mon | A | NULL | NULL | NULL | | BTREE | | | stats_hour | 0 | year_mon_day_hour_pageId_site | 3 | day | A | NULL | NULL | NULL | | BTREE | | | stats_hour | 0 | year_mon_day_hour_pageId_site | 4 | hour | A | NULL | NULL | NULL | | BTREE | | | stats_hour | 0 | year_mon_day_hour_pageId_site | 5 | page_id | A | NULL | NULL | NULL | YES | BTREE | | | stats_hour | 0 | year_mon_day_hour_pageId_site | 6 | site | A | NULL | NULL | NULL | | BTREE | | | stats_hour | 1 | date | 1 | date | A | 525625 | NULL | NULL | | BTREE | | | stats_hour | 1 | mon | 1 | mon | A | 14 | NULL | NULL | | BTREE | | | stats_hour | 1 | page_id | 1 | page_id | A | 23053 | NULL | NULL | YES | BTREE | | | stats_hour | 1 | hour | 1 | hour | A | 28 | NULL | NULL | | BTREE | | | stats_hour | 1 | day | 1 | day | A | 36 | NULL | NULL | | BTREE | | | stats_hour | 1 | day_mon_year_pageId_site | 1 | day | A | 36 | NULL | NULL | | BTREE | | | stats_hour | 1 | day_mon_year_pageId_site | 2 | mon | A | 426 | NULL | NULL | | BTREE | | | stats_hour | 1 | day_mon_year_pageId_site | 3 | year | A | 1342 | NULL | NULL | | BTREE | | | stats_hour | 1 | day_mon_year_pageId_site | 4 | page_id | A | 328515 | NULL | NULL | YES | BTREE | | | stats_hour | 1 | day_mon_year_pageId_site | 5 | site | A | 328515 | NULL | NULL | | BTREE | | | stats_hour | 1 | pageId_site_date | 1 | page_id | A | 23053 | NULL | NULL | YES | BTREE | | | stats_hour | 1 | pageId_site_date | 2 | site | A | 23892 | NULL | NULL | | BTREE | | | stats_hour | 1 | pageId_site_date | 3 | date | A | 2628125 | NULL | NULL | | BTREE | | +------------+------------+-------------------------------+--------------+-- -----------+-----------+-------------+----------+--------+------+----------- -+---------+ I dont understand why MySQL doesnt use the pageId_site_date index. Maybe someone here can explain this issue to me or know a solution? Thank you very much, Michaël de Groot
