Query Optimization

Question

I have 2 queries.

First:

SELECT * FROM `table` WHERE col='xyz' LIMIT 100
//Time Taken: 0.0047s

Second:

SELECT * FROM `table` WHERE col='xyz' ORDER BY Id DESC LIMIT 100
//Time Taken: 1.8208s

The second takes a much longer time. I know why that is, it is because first I have to select the whole table, then do the ordering, whereas the first query only returns the first 100 rows.

Is there any way to ORDER BY using another method, like selecting the last 100 rows and then doing the order? Or am I doing the query wrong and it can be made faster?

Note the Id is Autoincrement, so selecting the last rows will still return the correct data when it is ordered.

CREATE TABLE `table`(
    `Id` BIGINT NOT NULL AUTO_INCREMENT,
    `dateReg` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`Id`)
) ENGINE=MyISAM

Answer 1

Your id column should be set as the primary key (or if you have some other primary key, you should put an index on it anyway). That should speed the query up quite enough.

Answer 2

Create a composite index on (col, id) if you're using MyISAM for your table.

In InnoDB, the PRIMARY KEY is implicitly included into your table as a row pointer, since InnoDB tables are index-organized by design.

In case of InnoDB, to make a composite index on (col, id) it is enough to create an index on col and make sure id is the PRIMARY KEY.

This index will be used to filter on col and order by id.

The index is a B-Tree structure so it can iterate ASC and DESC with same efficiency.

Answer 3

For consecutive ids:

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-consecutive ids:

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