MySQL Forums
Forum List  »  Optimizer & Parser

Re: Help Optimizing My SELECT Query
Posted by: Rick James
Date: July 21, 2010 01:06PM

LIMIT is applied after ORDER BY is done. So,
SELECT * from `players` ORDER BY 'score' ASC LIMIT 500
Would give you all info about each player with the 500 lowest value of the constant 'score'. So, first, let's fix that type by using different quotes:
SELECT * from `players` ORDER BY `score` ASC LIMIT 500
Now, it returns 500 rows, those with the lowest values in the `score` column.

(Did you mean DESC instead of ASC? Or is this like golf, where low score is 'good'?)

Do you you really need 500? Do you really need all columns?

Is the provider complaining about bandwidth? Or about MySQL's CPU/disk/etc usage?

SELECT * and LIMIT 500 imply a lot of bandwidth needed.

ORDER BY score -- If there is not an index on score, it simply scans the entire table, sorts it (by score), then delivers 500. This is costly in other (non-bandwidth) resources. With INDEX(score), it _might_ be more efficient.

To discuss further, see if you can provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

Options: ReplyQuote

Written By
July 19, 2010 12:54PM
Re: Help Optimizing My SELECT Query
July 21, 2010 01:06PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.