I have a table with 50Mill+ records. Each one has a unique time stamp. When I index on the timestamp field, l_date_time(int(16)), the cardinality is the maximum (because there are no 2 times the same). I am attempting to speed up the following query and can’t figure anyway to do it.
SELECT from_unixtimestamp(l_date_time), name, inet_ntoa(ip) FROM tbl__ WHERE date_format(date,’%Y-%m-%d’) = ‘2005-09-09’;
This query returns 43k rows in 1 Minute 54.59 Seconds which is way to slow. I can’t do a partial index because it is an integer field. I am at my wits end here trying to speed this up.
Thanks in advance for your help.