Jump to content

Archived

This topic is now archived and is closed to further replies.

gamefreak13

Simple: Which query is more efficient?

Recommended Posts

Which is more efficient?

SELECT [color=red]threadid, title, dateline [/color] FROM vb_thread WHERE forumid ='17' ORDER BY dateline DESC LIMIT 10;

SELECT [color=red]*[/color] FROM vb_thread WHERE forumid ='17' ORDER BY dateline DESC LIMIT 10;

My server mysql-slow.log file has this in it quite often. It is from a script I made that reads threads from a certain "news" forum of mine, which acts as a newsfeed.



[i]# Time: 060830 21:10:14
# User@Host: myusername_admin[myusername_admin] @ localhost []
# Query_time: 7  Lock_time: 0  Rows_sent: 25  Rows_examined: 1122
SELECT threadid, title, dateline FROM vb_thread WHERE forumid ='31' ORDER BY dateline DESC LIMIT 25;
# User@Host: myusername_admin[myusername_admin] @ localhost []
# Query_time: 10  Lock_time: 0  Rows_sent: 25  Rows_examined: 1122
SELECT threadid, title, dateline FROM vb_thread WHERE forumid ='31' ORDER BY dateline DESC LIMIT 25;
# Time: 060830 21:14:03
# User@Host: myusername_admin[myusername_admin] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 10  Rows_examined: 1467
SELECT threadid, title, dateline FROM vb_thread WHERE forumid ='17' ORDER BY dateline DESC LIMIT 10;
# User@Host: myusername_admin[myusername_admin] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 10  Rows_examined: 1107
SELECT threadid, title, dateline FROM vb_thread WHERE forumid ='31' ORDER BY dateline DESC LIMIT 10;
# Time: 060830 21:14:19
# User@Host: myusername_admin[myusername_admin] @ localhost []
# Query_time: 5  Lock_time: 0  Rows_sent: 6  Rows_examined: 225[/i]

Share this post


Link to post
Share on other sites
I would expect a query fetching fewer columns to be slightly more efficient.  But it depends on a lot of factors.

If all or most of those 1122 or 1467 rows must be fetched from disk, then both queries will be slow.  Speed should be around the same.  If that's the case, then you need to reduce the number of rows being fetched for each query.

Right now your query has to fetch all 1000+ rows, sort them all, then chop off the top 10.  If you can arrange your data so that fewer rows need fetching you can get better performance.  One way could be to expire old threads, or move old threads into an "old threads" table.  Then you can check the old threads table whenever you can't find 10 new threads in the "recent threads" table.

I could be totally wrong :)  So make sure you benchmark any changes and see if things have improved or gotten worse.

Share this post


Link to post
Share on other sites
Is there a way to only get, say, 25? I thought that is what the DESC LIMIT 25 part meant, descending and only grab 25. No need for it to grab them all, right?

Share this post


Link to post
Share on other sites
If there aren't already any indexes on the table then you should add this one.

[code]
ALTER TABLE vb_thread ADD INDEX(forum_id, dateline)
[/code]

Run the following before and after making the addition. Post the output of each run.
[code]
EXPLAIN SELECT threadid, title, dateline  FROM vb_thread WHERE forumid ='17' ORDER BY dateline DESC LIMIT 10;
[/code]

http://dev.mysql.com/doc/refman/4.1/en/indexes.html
http://dev.mysql.com/doc/refman/4.1/en/explain.html

Share this post


Link to post
Share on other sites
All of these comments are correct -- as far as * vs the column list, the latter is almost always better, though it makes lazy programming more cumbersome.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.