Query Not Using Index

[The Little Guy]

Indexes:

mysql> show indexes from surf_stats;
+------------+------------+-----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name        | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+-----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| surf_stats |          0 | PRIMARY         |            1 | surf_stat_id | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| surf_stats |          1 | member_date_idx |            1 | member_id    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| surf_stats |          1 | member_date_idx |            2 | surf_date    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+------------+------------+-----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

 

Query Explain:

mysql> explain select sum(pages) as total_pages, date(from_unixtime(surf_date)) as the_date, count(distinct member_id) as members from surf_stats where level_id = 12 and surf_date between 1305522000 and 1306472400 group by the_date;
+----+-------------+------------+------+---------------+------+---------+------+----------+-----------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows     | Extra                       |
+----+-------------+------------+------+---------------+------+---------+------+----------+-----------------------------+
|  1 | SIMPLE      | surf_stats | ALL  | NULL          | NULL | NULL    | NULL | 19427694 | Using where; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+----------+-----------------------------+
1 row in set (0.00 sec)

 

Query

mysql> select sum(pages) as total_pages, date(from_unixtime(surf_date)) as the_date, count(distinct member_id) as members from surf_stats where level_id = 12 and surf_date between 1305522000 and 1306472400 group by the_date;
+-------------+------------+---------+
| total_pages | the_date   | members |
+-------------+------------+---------+
|      112513 | 2011-05-16 |    1463 |
|      112246 | 2011-05-17 |    1439 |
|      114965 | 2011-05-18 |    1480 |
|      110261 | 2011-05-19 |    1452 |
|      108967 | 2011-05-20 |    1433 |
|      101926 | 2011-05-21 |    1333 |
|      108616 | 2011-05-22 |    1416 |
|      108219 | 2011-05-23 |    1422 |
|      109157 | 2011-05-24 |    1449 |
|       92303 | 2011-05-25 |    1264 |
+-------------+------------+---------+
10 rows in set (2.01 sec)

 

 

So the above is what is happening, basically when I run this query it doesn't use any indexes, I have tried adding "and member_id > 0" to the where clause, but that still doesn't use the index. How can I get my query to use the index so this query will run faster?

 

I don't want to add another index, because that will take to long to add an index to 19M rows.

[gizmola]

It will not use an index because there is no index it can use to limit the results.  You need an index on surf_date.