Jump to content


Photo

Simple: Which query is more efficient?


  • Please log in to reply
4 replies to this topic

#1 gamefreak13

gamefreak13
  • Members
  • PipPipPip
  • Advanced Member
  • 96 posts

Posted 22 September 2006 - 04:07 AM

Which is more efficient?

SELECT threadid, title, dateline FROM vb_thread WHERE forumid ='17' ORDER BY dateline DESC LIMIT 10;

SELECT * 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.



# 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


#2 btherl

btherl
  • Staff Alumni
  • Advanced Member
  • 3,893 posts
  • LocationAustralia

Posted 22 September 2006 - 04:52 AM

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.

#3 gamefreak13

gamefreak13
  • Members
  • PipPipPip
  • Advanced Member
  • 96 posts

Posted 22 September 2006 - 05:54 AM

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?

#4 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 22 September 2006 - 07:47 AM

If there aren't already any indexes on the table then you should add this one.

ALTER TABLE vb_thread ADD INDEX(forum_id, dateline)

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

http://dev.mysql.com...en/indexes.html
http://dev.mysql.com...en/explain.html

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 September 2006 - 10:04 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users