Unix timestamp Field & Indexes

MySQL & MariaDB Other MySQL® Questions
1

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.

2

You need

  1. an index on the ‘date’ column you have in the WHERE clause
  2. do not apply any function or expression to that column in the WHERE clause, put it by itself on one side of the operator.
3

Thanks for the quick reply. I do have the date column indexed, but I don’t understand how to implement #2?

4

You should use EXPLAIN to understand how the query is executed. Try this:

SELECT from_unixtimestamp(l_date_time), name, inet_ntoa(ip) FROM tbl__ WHERE date between ‘2005-09-09 00:00:00’ and ‘2005-09-09 23:59:59’;

5

Thanks,
I was just in the process of using EXPLAIN and trying to figure out which way was best. 9.14 seconds for the query now. Here is the explain for your query

±—±------------±--------±------±--------------±-----±--------±-----±------±------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±--------±------±--------------±-----±--------±-----±------±------------+| 1 | SIMPLE | testing | range | Date | Date | 4 | NULL | 30279 | Using where |±—±------------±--------±------±--------------±-----±--------±-----±------±------------+

I didn’t realize that putting an operation in the WHERE caused such a slow down. Thanks so much as I’ve learned something new today.