For example if your index is (username, date_posted) then you can do something like
SELECT * FROM table WHERE username='user' ORDER BY date_posted
but if you do SELECT * FROM table WHERE username IN ('user','user2') ORDER BY date_posted
then it will need a temporary table.You could change your index to (date_posted, username) to make it work, but then it will be slow when querying a small percent of the users.
Update: I just realized I have the code in an archive here so I loaded it up. I can't believe this wasn't obvious to me! Since we already had the ordered IDs in memory (to build the SELECT query!) we loaded the posts with a SELECT .. IN into a hash (with the id as the key) then just went through the ordered ID list and pulled out the posts from the hash in the right order! :-)
Just get your data directly with a search, then array sort the hash using the hash key.
If I have a table with 10m rows (it was about that) and I want 10 posts from feeds X, Y and Z in reverse date order, to get them in just one query with local sorting I'd have to retrieve every row for feed X, Y and Z down the wire before doing the local date sort.
If I did the sorting with ORDER BY to avoid that issue, the complete JOIN (meta<->full) is used in the temporary sort table so, a bigass temporary table would result for MySQL to do its sorting.. bringing us back to square one :-)
Hopefully I'm not misunderstanding your point though.. it's been known to happen.
BTW, you were doing this to avoid using too much memory at the database, but by doing this (assuming it's PHP; you didn't say) you are loading the result set twice into webserver memory.
Obviously it worked for you, maybe the webserver had more free memory.
But I use FIELD() http://dev.mysql.com/doc/refman/5.0/en/string-functions.html...
SELECT FROM x WHERE id IN(9,4,22,38,4,..) ORDER BY FIELD(`id`, 9,4,22,38,4,..)
That said, I dont know if sql or php is quicker.
The problem was that even though I denormalized my "posts" (from RSS feeds) table into a "post contents" and a "post metadata" table, MySQL wants to create a "temporary table" to do sorting and pulls every relevant row into it (I found this was even true with indexed columns at the time - I hope they improved it). This can lead to heavy disk access if the number of rows needing to be sorted is too high (consider 100,000 rows of 150 bytes each.. 15 megabytes, not good for a server under heavy load that needs to return a query in < 0.2 seconds).
My solution was to pull out ONLY the post IDs and date (or whatever column I wanted to sort by) from MySQL, do the sort in memory, then pull out the posts from the database by already ordered IDs (e.g. SELECT * FROM x WHERE id IN(9,4,22,38,4,..)). MySQL is crazy fast at giving you back rows X, Y, Z, and so on, if you specify them directly.
I ended up with many more fast queries rather than fewer deathly slow queries, and that was a great tradeoff in the end. I believe the system still runs that way under its new owner.