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'm adding pagination to my 'latest news' php script and running in to an issue.

SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit

I have indexes on approved, time columns.

The problem is when I do an EXPLAIN on the query, it shows it is using the 'approved' index - but it still has to scan all 1000 rows to get the 10 new entries I want to display.

Anyway to optimze the query so that doesn't happen?

share|improve this question

migrated from serverfault.com Jul 29 '11 at 6:56

This question came from our site for system and network administrators.

1  
Now these are the types of questions I enjoy answering in DBA Stack Exchange. Keep them coming and get the word out, please. +1 !!! –  RolandoMySQLDBA Jul 30 '11 at 4:15

3 Answers 3

At this point, you will have to create an index on three columns

ALTER TABLE news ADD INDEX authored_time_sid_ndx (authored,time,sid);

You must then refactor the query just slightly to reduce two things the MySQL Query Optimizer is expected to handle:

  • the number of rows read
  • the amount of data collected

Here is your original query:

SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit

What you can do is gather all the keys first:

SELECT sid FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit

This will perform a full index scan rather than a full table scan because all three columns are in the index. That's a lot less baggage to walk around with for temp table generation because you are scanning just 3 columns in a smaller resource (the index) as opposed to 6 columns as from a bigger resource (the table) as well as not sticking url and bodytext (which are probably VARCHAR(300) and TEXT fields) into the mix just for the sake of gathering keys.

Next, make the SELECT sid query into an inline table and connect them back to the news table using only the fetched keys. EXAMPLE : Suppose your LIMIT variables are 200,10. This means you want to move to the 201st row of the news table and get 10 keys from that point. This means that no matter which page you are on, you want to collect 10 keys at a time, and only 10 keys at a time.

Here is the new and improved query:

SELECT A.sid, A.title, A.time, A.bodytext, A.author, A.url
FROM
news A INNER JOIN
(SELECT sid FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit) B
USING (sid);

The only real cost here is doing a full index scan for keys rather than a full table scan. The benefit is that once you get the 10 keys from the inline SELECT, then only 10 titles, bodytexts, authors and urls are retrieved.

Give it a Try !!!

CAVEAT

As a rule of thumb, anytime you index a column whose cardinality is very low (i.e., Male/Female (2), Single/Married/Divorced/Widowed (4), True/False (2), 0/1 (2)) and the number of rows for any one value exceeds 5% of the total rows in the table, the MySQL Query Optimizer will rule out any and all indexes and you will perform can full table scan or a bad index gets chosen and you end up with a full index scan. This is why it is very imperative that you find the right column distribution, or at the very least perform index scans instead of table scans.

UPDATE 2011-08-08 11:13 EDT

What was I thinking ? This was my original propsed index:

ALTER TABLE news ADD INDEX authored_time_sid_ndx (authored,time,sid);

The field should be approved not authored. This is what it should be:

ALTER TABLE news ADD INDEX approved_time_sid_ndx (approved,time,sid);

The original query you gave was

SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit

The index I am proposing (approved_time_sid_ndx) will include approved, time, and sid.

The answer that you just submitted has this query :

SELECT sid, title, time, bodytext, author, url FROM news WHERE approved = 1 AND sid > 0 ORDER BY sid desc LIMIT 500, 10;

This being the case, the index you need now should be this:

ALTER TABLE news ADD INDEX approved_sid_ndx (approved,sid);

Both approved and sid should be together. IF they are not together, the MySQL Query Optimizer may decide to perform an internal index merge of the primary key and an index where approved is the first (or only) column. In fact, your new query should be refactored as follows:

SELECT A.sid, A.title, A.time, A.bodytext, A.author, A.url
FROM
news A INNER JOIN
(SELECT sid FROM news WHERE approved=1 and sid > 0 ORDER BY sid desc LIMIT 500,10) B
USING (sid);

Remember, you want an index that encompasses as many DB columns as possible that are embedded in WHERE and ORDER BY clauses.

share|improve this answer
    
As always, you went above and beyond, and the 5% rule is a good thing to keep in mind. –  Derek Downey Jul 29 '11 at 13:05

an index on a column with values 1/0 will not index well. This is because very quickly more than 35% of your index values will have the same value and the query optimiser knows its more efficient to do a table scan.

The way to optimise this is to store a value that is well distributed. Naturally 90% and growing of your entries will be "approved" - it could well be "sent" or similar.

Store a number which is a modulo of your primary key (assuming sid) or store the last 3 or for characters of an md5 of a column value. Then test for != 0 or ='000'. Your saving routine needs to take into account that the modulo or hash may well be 0 or "000", so you need to fudge that value to something else.

share|improve this answer
    
interesting idea on the value stored. –  Derek Downey Jul 29 '11 at 16:18
    
@user2599 - I read this and actually found this very imaginative and creative. +1 for using the right brain for thinking outside the box. –  RolandoMySQLDBA Jul 29 '11 at 16:55

I found a different solution that appears to work - any thoughts on this?

What I did was create a new 'approved' and 'sid' index (left the primary 'sid' index intact).

mysql> explain SELECT sid, title, time, bodytext, author, url FROM news WHERE approved = 1 AND sid > 0 ORDER BY sid desc LIMIT 500, 10;

+----+-------------+-------+------+------------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys    | key      | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+------------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | news  | ref  | PRIMARY,approved | approved | 2       | const |   11 | Using where; Using index |
+----+-------------+-------+------+------------------+----------+---------+-------+------+--------------------------+
share|improve this answer
    
I have proposed a better index and a new refactored query in my answer since you dropped the time column from the old query. Please look over my updated answer marked "UPDATE 2011-08-08 11:13 EDT" –  RolandoMySQLDBA Aug 8 '11 at 15:14
    
I will +1 your answer because of your initiative and effort to hunt down a better index. –  RolandoMySQLDBA Aug 8 '11 at 15:17
    
What kind of performance difference is there between your latest query and mine? (besides having to scan 1 less row) I appreciate all your help! –  dave Aug 8 '11 at 19:42
    
The performance difference becomes very apparent as the amount of data grows. I actually performed tests on generated sample data with tens of thousands of rows for another stack exchange question (stackoverflow.com/questions/5983156/…) and the empirical results from my refactored query against 10 other submitted answers was astounding. –  RolandoMySQLDBA Aug 8 '11 at 20:17
    
Thanks again for all your help - I've learned a lot and this has inspired me to go back rewrite my other project's queries :) –  dave Aug 8 '11 at 20:56

Your Answer

 
discard

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