Home Optimizing a mysql date query
Reply: 0

Optimizing a mysql date query

user867
1#
user867 Published in April 20, 2018, 6:03 pm

I am trying to optimize the following query that compares the last_updated value against a datetime. My basic query is:

SELECT Count(1) AS loopCount FROM main_iteminstance mi 
WHERE mi.last_updated >= '2018-04-12 07:25:23.000';

Doing an explain gives me:

And running the query takes about 8s even though the field is indexed. Is there a better way to do the above? There are about 10M rows here.

The table looks something like this (though has many more fields):

CREATE TABLE `main_iteminstance` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `last_updated` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 PRIMARY KEY (`id`),
 KEY `last_updated` (`last_updated`)
) ENGINE=InnoDB AUTO_INCREMENT=205421031 DEFAULT CHARSET=utf8
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.310205 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO