CODEWITHSUNDEEP
mysqlsqloptimization
Didar_Uranov
Didar_Uranov

Reputation: 1240

How to optimize MySQL ORDER BY + huge LIMIT Offsets

Table:

CREATE TABLE `test` (
  `uid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `rating` smallint(5) unsigned NOT NULL DEFAULT '100',
  PRIMARY KEY (`uid`),
  KEY `rating` (`rating`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This query runs quick enough(0.015s):

SELECT uid FROM test ORDER BY rating DESC LIMIT 0,100

But with big LIMIT offsets it runs very slow(2.215s):

SELECT uid FROM test ORDER BY rating DESC LIMIT 10000,100

How can I rid of huge LIMIT offsets?!

Upvotes: 2

Views: 2379

Answers (3)

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324620

The easiest way to improve performance is to ORDER BY a primary key.

Since you can't really do that with the rating column, you can cheat instead.

Create this table:

CREATE TABLE `test_ranks` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Then put the following in a cron script that runs every X amount of time (1 minute, 5 minutes... basically a good compromise between update speed and the time it takes to run):

CREATE TEMPORARY TABLE `_tmp_test_ranks` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `_tmp_test_ranks` (`uid`) VALUES (SELECT `uid` FROM `test` ORDER BY `rating` DESC);

TRUNCATE `test_ranks`;

INSERT INTO `test_ranks` SELECT * from `_tmp_test_ranks`;

DROP TABLE `_tmp_test_ranks`;

Now, instead of your slow-running select, you can run the faster:

SELECT `uid` FROM `test_ranks` WHERE `id` BETWEEN 10000 AND 10100 ORDER BY `id` ASC

Upvotes: 1

Ami
Ami

Reputation: 1254

With LIMIT 10000, 100 MySQL has to scan through 10100 records. If you could remember your spot in the window, it might be better:

SELECT uid
FROM test
WHERE rating > :last_rating
ORDER BY rating DESC LIMIT 0,100

In this case :last_rating was the last rating from the previous query.

Upvotes: 6

Bryan
Bryan

Reputation: 6752

As far as I know, and after doing a little bit of digging, there's really no way for you to set a maximum limit in the database's config or anything like that. It will be up to the developer of the application implementing your database, to ensure that the maximum values are established within the application's logic.

Upvotes: 0

Related Questions