how Index used in between query

Question

How can get my index work when i query using Between command?

when i explain the query:

explain select * from table where Date between date1 and date2;

the return actual key be used is NULL, how to used my index in this case?

I have read some documentation from MYSQL, they said BTree index should be used here in order for > < or between query, however it doesnt work in my case.

Pls help

EDIT

explain  select * from table where Date between '2010-05-10 00:00:00' and '2010-06-10 00:00:00';
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

|  1 | SIMPLE      | table| ALL  | date_index    | NULL | NULL    | NULL | 109024 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

Answer 1

you have to make your table lock type "AllPages";

alter table [table] lock allpages
go

after that, you have to create an index on that column (if you can create clustered index);

create nonclustered index ndx1 on [table] (col1, ...)
go

that run;

sp_recompile [table]
go

and finally look at your q-plan again. It should use an index.

Answer 2

You are comparing a datetime field with strings in a greater_than/less_than comparison. If you use a cast or function (like UNIX_TIMESTAMP()) and transform also the other dates to unixtimestamp, that would do the trick but will destroy the use of the index. Maybe a better solution would be to store the date as unix timestamp in the table and put an index on that. Then, you would only need to do:

select * 
from table 
where DateInUnixTimeStampFormat 
between UNIX_TIMESTAMP(date1) and UNIX_TIMESTAMP(date2)

OR

select * 
from table 
where DateInUnixTimeStampFormat 
> UNIX_TIMESTAMP(date1) and DateInUnixTimeStampFormat < UNIX_TIMESTAMP(date2)