Re: Optimizing WHERE computations

[Craig Matthews]

Hi,

Thanks Kim. That was merely an example, not an apt one I think.

My Goal:
--------
To get a count of all articles (an indexed column) for today.


My SQL:
--------
EXPLAIN
SELECT count(article_id)
FROM articles
WHERE date_format(add_date, '%Y%m%d') = curdate()+0;

I have an index on both "article_id" and "add_date" columns. Both are in the same index, article_id is the left most field, and add_date is the third one.


EXPLAIN VALUES:
-----------------

Type: Index
Possible Keys: NULL
Key Len: 60
Ref: NULL
Rows: 711 890 (This is a full table scan!)
Extra: Using where; Using index

What am I missing? It says "Using index" but the SQL is taking 7 minutes!