I have a MySQL(InnoDB) table in which I have a large number of rows(a couple of million). I'm doing queries as such:
SELECT SQL_CALC_FOUND_ROWS `a` FROM `logs` WHERE `connect_timestamp` > 10000 ORDER BY `connect_timestamp` DESC LIMIT 1
I have a normal index added to the column, however a query like this takes up to 20 seconds, is there a better way?
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `logs` ADD INDEX `logs_idx_connect_timestamp` (`connect_timestamp`);
SELECT SQL_CALC_FOUND_ROWS `logs`.`a` FROM `logs` WHERE `logs`.`connect_timestamp` > 10000 ORDER BY `logs`.`connect_timestamp` DESC LIMIT 1