Take the 2-minute tour ×
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. It's 100% free, no registration required.

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()
ORDER BY rating DESC
LIMIT 4000, 1000;

SELECT * FROM levels
WHERE playable_character = 5 AND
    date_published BETWEEN date_sub(now(), INTERVAL 1 MONTH) AND now()
ORDER BY plays DESC
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
1  
Isn't this a duplicate of this question?: stackoverflow.com/questions/19385610/… –  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 blogs.oracle.com/realneel/entry/… 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 sqlfiddle.com/#!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

1 Answer 1

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

Your Answer

 
discard

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.