Simple: Which query is more efficient?

[gamefreak13]

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]

[btherl]

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.

[gamefreak13]

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?

[shoz]

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

[fenway]

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.