[ale] Indexing datetime field in mysql?

[Kofi Boateng]

I am having some issues with mysql. I have an existing table with some date and i altered it using, ALTER TABLE ff.flow ADD INDEX (st_time);
 
 
Now, I execute the statement below:
 
explain select st_time, INET_NTOA(src_ip) as source, INET_NTOA(dst_ip) as dest from ff.flow where st_time > '2005-07-01 23:55:55' and st_time < '2005-08-08 00:00:00' and proto = '6' ORDER BY source ASC;
 

+-------------+-------+---------------+---------+---------+------+-------+----------------------------+

| table       | type  | possible_keys | key     | key_len | ref  | rows  | Extra                      |

+-------------+-------+---------------+---------+---------+------+-------+----------------------------+

| flow | range | st_time       | st_time |       8 | NULL | 72621 | where used; Using filesort |

+-------------+-------+---------------+---------+---------+------+-------+----------------------------+

1 row in set (0.10 sec)

Now the problem is when i change the time on the date 2005-08-08 00:00:00 to 2005-08-08 12:30:00 say, i get 

+-------------+------+---------------+------+---------+------+--------+----------------------------+

| table       | type | possible_keys | key  | key_len | ref  | rows   | Extra                      |

+-------------+------+---------------+------+---------+------+--------+----------------------------+

| flow | ALL  | st_time       | NULL |    NULL | NULL | 389597 | where used; Using filesort |

+-------------+------+---------------+------+---------+------+--------+----------------------------+

1 row in set (0.06 sec)

Meaning mysql is not using index key st_time for the select query. Do anyone knows what may be going on? I think it has to something to do with the decision made by the optimizer but i don't know exactly why mysql is not using indexing on the second case. Any pointers will be appreciated



Sincerely,

Kofi Boateng
Applications Developer
Securiant Inc
690 Powers Ferry Road Suite 290
Atlanta, Ga 30339
http://www.securiant.com
-------------- next part --------------
An HTML attachment was scrubbed...