With a table over 18 million rows:
SELECT * FROM tbl WHERE id > 10000000 LIMIT 30
Took 0.0724 sec.
SELECT * FROM tbl WHERE id < 10000000 ORDER BY id DESC LIMIT 30
Took 0.0565 sec.
Is this the fastest way to SELECT certain number of records before a certain row in MySQL?
It seems good enough but doesn't MySQL have to first order those 10 million rows in descending order before SELECT-ing the 30 rows?
I'm asking this is because I'm not so sure of this query I came up. It does seem work and fast enough but looking at the grammatical semantics, I'm not so sure.
Is MySQL intelligent enough to know that it doesn't have to order all those 10 million rows?
Or is there any better way to achieve this?
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `tbl` ADD INDEX `tbl_idx_id` (`id`);
SELECT * FROM tbl WHERE tbl.id > 10000000 LIMIT 30