CODEWITHSUNDEEP
mysqlindexing
Eric
Eric

Reputation: 2265

MySQL: Can I index a datetime field? MySQL won't do it

mysql> create index index_questions_on_publishedAt on questions(publishedAt);
ERROR 1317 (70100): Query execution was interrupted
mysql> select count(*) from questions;
+----------+
| count(*) |
+----------+
|   491773 | 
+----------+
1 row in set (1.02 sec)

# Is it even reasonable for this query to take 1 second?

mysql> select count(*) from questions where publishedAt <= '2011-08-23 19:52:01' and publishedAt >= '2011-08-23 19:49:44';
+----------+
| count(*) |
+----------+
|       30 | 
+----------+
1 row in set (0.71 sec)

MySQL info: Server version: 5.1.41-3ubuntu12.10 (Ubuntu)

Most of my queries test for varying time ranges, and the contents of each range are in constant flux, so I can't cache much here. Patiently awaiting any advice... Thanks.

Upvotes: 1

Views: 5005

Answers (1)

Doug Kress
Doug Kress

Reputation: 3537

I have a table with 725K records, and here are my results:

  • Before creating an index:
    • Counting with a condition with no index: 0.59 seconds
    • Counting with no condition: 0.8 seconds
  • Creating an index on a datetime field: 2.28 seconds
    • Counting with condition: 0.07 seconds

Hope that answers your question.

Your index creation does seem to be getting interrupted by something or someone, but I would double-check the log files to see if there's anything going on there.

Upvotes: 2

Related Questions