gamefreak13 Posted September 22, 2006 Share Posted September 22, 2006 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: 1122SELECT 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: 1122SELECT 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: 1467SELECT 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: 1107SELECT 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] Quote Link to comment Share on other sites More sharing options...
btherl Posted September 22, 2006 Share Posted September 22, 2006 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. Quote Link to comment Share on other sites More sharing options...
gamefreak13 Posted September 22, 2006 Author Share Posted September 22, 2006 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? Quote Link to comment Share on other sites More sharing options...
shoz Posted September 22, 2006 Share Posted September 22, 2006 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.htmlhttp://dev.mysql.com/doc/refman/4.1/en/explain.html Quote Link to comment Share on other sites More sharing options...
fenway Posted September 22, 2006 Share Posted September 22, 2006 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.