Hi guys,



I have a question about index usage in MySQL.



I have a query:

MYSQL: (root@arson) [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 don’t understand why MySQL doesn’t 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