indexing for comparators

[kurbsdude]

I have a problem. I have a table as follows

 

id date name

.. ..      ..

 

I have indexed this table with date

But my mysql query is such that it has to get rows WHERE date <= yesterday

 

After checking this query with EXPLAIN I found out that this query is reading the whole table and not the only ones that have date <= yesterday (index not working at all?)

 

How can this be solved? thanks

[Daniel0]

Can you show the EXPLAIN output and the output of SHOW CREATE TABLE?

[kurbsdude]

Here's my syntax:

SELECT * FROM name WHERE (date <= '09-09-09 08:45:25') ORDER BY date DESC

 

and the explain output:

 

id 	select_type 		table 		type 		possible_keys 		key 		key_len 		ref 		rows 		Extra
1 	SIMPLE 			name 		ALL 		date,date_2 		NULL 		NULL 			NULL 		4870 		Using where; Using filesort

 

*edit and total number of rows = 4870

so basically it's reading all rows

[Daniel0]

Yeah, but what about SHOW CREATE TABLE name;?