Optimizing queries
I have 2 requests.
First:
SELECT * FROM `table` WHERE col='xyz' LIMIT 100
//Time Taken: 0.0047s
Secondly:
SELECT * FROM `table` WHERE col='xyz' ORDER BY Id DESC LIMIT 100
//Time Taken: 1.8208s
The second takes much longer. I know why this is because first I have to select the whole table and then do the ordering, whereas the first query only returns the first 100 rows.
Is there a way to ORDER BY using a different method, like selecting the last 100 rows and then doing the order? Or am I making the request wrong and can it be done faster?
Note that the ID is auto-increment, so selecting the last rows will still return the correct details when ordering it.
CREATE TABLE `table`(
`Id` BIGINT NOT NULL AUTO_INCREMENT,
`dateReg` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM
source to share
For sequential identifiers:
SELECT t.*
FROM TABLE t
JOIN (SELECT MAX(t.id) 'maxid'
FROM TABLE t) max ON t.id BETWEEN max.maxid-100 AND max.maxid
WHERE t.col = 'xyz'
For non-sequential identifiers:
SELECT a.*
FROM (SELECT t.*,
@rownum := @rownum+1 AS rownum
FROM TABLE t, (SELECT @rownum := 0) r
WHERE t.col = 'xyz') a,
(SELECT COUNT(t.*) 'max'
FROM TABLE t
WHERE t.col = 'xyz') m
WHERE a.rownum BETWEEN m.max-100 AND m.max
source to share
Create a composite index on (col, id)
if you are using MyISAM
for your table.
B InnoDB
, is PRIMARY KEY
implicitly included in your table as a row pointer, since tables are InnoDB
ordered by design index.
In case InnoDB
, to create a composite index on (col, id)
, it is enough to create an index on col
and make sure that it id
is PRIMARY KEY
.
This index will be used to filter by col
and order id
.
The index represents the structure B-Tree
, so it can iterate ASC
and DESC
with the same efficiency.
source to share