Database smarties -- SELECT on index help

[psili]

MySQL question as I can't find my answer in the manual and what I try and make up doesn't seem to work.

Basically, here's my table schema:


id = int(4) auto_increment
name = varchar(20)
date_live = datetime

I created an index on the date_live field:

CREATE INDEX date_live ON table (date_live);


Now, my problem is I want to use that index when I run a query against the table, but i only want to select the "date" element and not both "date" and "time" from the "date_live" field -- like a wildcard:

explain select * from table where date_live like '2004-09-22%'

Unfortunately that query doesn't seem to use the index.

This query doesn't work, because some rows have a time element and I need those rows too:

explain select * from table where date_live = '2004-09-22'

Anyone know how to query for just the DATE on a DATETIME field that can use the index I created on it ?

[aiken]

Your query isn't using the index because you're making MySQL convert the datetime field to a varchar, and then that against the "like" operator.

What you want is probably something like "where date_live>='2004-09-22' and date_live<'2004-09-23'", and then let your app deal with displaying only the date. (In this approach, the constants will be converted from char to datetime before the comparisons, as opposed to the "like" example where for all MySQL knows you're examining the fourth character, so it has to convert all of the dates to chars)

At least, that's my guess. I'm an MS SQL guy

Cheers
-b

[psili]

Yea, that seems to work better - at least when letting the engine analyze the query. It just doesn't look as "elegant" as I wanted it to...

Thanks for the quick reply.