Big tables and dates

[Benjamin Fonze]

Hello,

I have a table of about 10 Million rows and trying to execute a pretty simple query:

SELECT sum(earnings_publisher) FROM clicks WHERE date<='2007-11-11';

Since I'm using <= and not = it seems the index on date cannot be used:

mysql> EXPLAIN SELECT sum(earnings_publisher) FROM clicks WHERE date<='2007-11-11';
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | clicks | ALL | date | NULL | NULL | NULL | 9935990 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)


So it does a full table read and obviously, it is VERY slow.

Is there a way to use the index on the date field, still using <= ?