Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an INNODB table levels:

| Field              | Type         | Null | Key | Default | Extra |
| id                 | int(9)       | NO   | PRI | NULL    |       |
| level_name         | varchar(20)  | NO   |     | NULL    |       |
| user_id            | int(10)      | NO   |     | NULL    |       |
| user_name          | varchar(45)  | NO   |     | NULL    |       |
| rating             | decimal(5,4) | NO   |     | 0.0000  |       |
| votes              | int(5)       | NO   |     | 0       |       |
| plays              | int(5)       | NO   |     | 0       |       |
| date_published     | date         | NO   | MUL | NULL    |       |
| user_comment       | varchar(255) | NO   |     | NULL    |       |
| playable_character | int(2)       | NO   |     | 1       |       |
| is_featured        | tinyint(1)   | NO   | MUL | 0       |       |

There are ~4 million rows. Because of the front-end functionality, I need to query this table with a variety of filters and sorts. They are on playable_character, rating, plays, and date_published. The date_published can be filtered to show by the last day, week, month, or anytime(last 3 years). There's also paging. So, depending on the user choices, the queries can look, for example, like one of these:

SELECT * FROM levels
WHERE playable_character = 0 AND
    date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now()
ORDER BY date_published DESC
LIMIT 0, 1000;

SELECT * FROM levels
WHERE playable_character = 4 AND
    date_published BETWEEN date_sub(now(), INTERVAL 1 WEEK) AND now()
LIMIT 4000, 1000;

SELECT * FROM levels
WHERE playable_character = 5 AND
    date_published BETWEEN date_sub(now(), INTERVAL 1 MONTH) AND now()
LIMIT 1000, 1000;

I should add that rating and plays are always queried as DESC. Only date_published may be either DESC or ASC.

I started out with an index idx_date_char(date_published, playable_character) that worked great on the first example query here. Based on some other answers, I changed to two other indexes (date_published, playable_character, plays) and (date_published, playable_character, rating).

The first query still runs very fast, however there's some unusual things happening in EXPLAIN, when player_character = x exceeds a certain number of rows (~700,000): the USING WHERE pops on in EXPLAIN.

So, first question is are there any improvements in the query or indexes possible, and, second, what MySQL settings should get altered to allow for the large result sets.

Any suggestions greatly appreciated. TIA.

share|improve this question
I sure would like to see the "explains" and "show indexes from levels" output when the unusual things happen.. looks like the deep scanning (larger start value in LIMIT) triggers the use off an temporary table and using filesort. – Raymond Nijland Oct 18 '13 at 17:39
Also how slow run other queries? How much time is needed to get the results? – Mindaugas Riauba Oct 18 '13 at 17:57
Isn't this a duplicate of this question?:… – ypercubeᵀᴹ Oct 18 '13 at 17:59
@ypercube sure looks like an duplicate i thought i had an Déjà vu but i could not find the topic on stackoverflow annymore – Raymond Nijland Oct 18 '13 at 18:11
@Hal50000 If your explain shows "using filesort" i think this… could be your problem note that article is old so most likly the filesort will be hinted now with explain hints to get an estimate_rows_upper_bound() see!2/4f56a/2, i think filesort gets 30 as rows there.. i also think i need to track down the estimate_rows_upper_bound() in the source code to get an better explainment this is thoery. – Raymond Nijland Oct 19 '13 at 9:48
WHERE playable_character = 0 AND
    date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now()

Start with the "=" item, then do the range:

INDEX(playable_character, date_published);

"Pagination", a la ORDER BY rating DESC LIMIT 4000, 1000; is best done by remember where you "left off". That way, you don't have scan over the 4000 records that you don't need.

share|improve this answer

Everybody here are missing one point - SQLs in question are retrieving 1000 rows. And one cannot retrieve 1000 rows fast unless most of the data is cached. If data is not cached one has to do 1000 random reads sequentially to retrieve the data.

Good disk based storage with fast disks will give you up to ~200 reads per second. Common disks even in RAID I doubt that manage even 100. That means 10+ seconds to get results even with the best indexes.

So in the longer run such data model and queries won't work. Now the queries run fast when you hit cached data.

share|improve this answer
Even with covering indexes? – ypercubeᵀᴹ Oct 18 '13 at 19:23
That's one of the possible ideas how to reorganize the data. – Mindaugas Riauba Oct 18 '13 at 19:26
Covering indexes also give best results when they are cached in the buffer pool. Otherwise, you have to expect to wait for disk I/O. – Bill Karwin Nov 17 '13 at 21:46
And for what it's worth, SSD-based systems with thousands of IOPS are becoming mainstream for RDBMS servers. – Bill Karwin Nov 17 '13 at 21:47
Reading 1,000 rows is very unlikely to be 1,000 IO operations. I'm no expert on InnoDB's internal structures but as it is based on some variation of b-trees it can't be unrecognisably different to MS SQL Server or similar. It apparently uses 16Kb pages, so with those column sizes that is between ~40 and ~400 rows per page for the base data so assuming an index that covers everything and little fragmentation that is 25 pages or less, and depending on allocation method they might be sequential (or close to) on disk rendering random-IO-per-second figures less meaningful. – David Spillett Jan 20 at 16:26

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.