SEO

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

Michaël de Groot wrote:
> 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;

How many results does this query return:
select count(*) from webstats.stats_hour where page_id LIKE 'vipPage_%';

How many rows in the table?

It could be that this grabs too many rows and it's easier for mysql to
look at the data rather than the index.


Try an index on (page_id, site, date) and see how that goes.