Comment by petercooper | Hacker News Reader

Comment by petercooper | Hacker News Reader

On Feed Digest (a RSS feed processor I sold a few years back but was doing ~300m "digests" per month) I had a similar experience, so I think I know why and what they were doing here..

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.

You can only do it on the index, the query from the index will return in the sort order. You have to be very careful in your index and query design to make this work.

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.

But that doesn't guarantee the results will be in that order. Just because you put your ID's in a certain order in the IN() doesn't mean you'll get them back in that order.

Interesting. It's been 3 years since I sold the business and touched the code. The basic mechanic worked as I described, but you're right (just tested), we must have had extra workarounds.

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! :-)

So then why did you bother with the first query and then the IN() ?

Just get your data directly with a search, then array sort the hash using the hash key.

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.

Using it with LIMIT makes sense, and I forgot you were doing that.

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.

It did (though less total memory). Significantly, though, the memory use was ephemeral at the application server end. Do the collation, push to a memcache server, server, and die :-)

You'd have to have something like SELECT id FROM table WHERE id in () ORDER BY FIND_IN_SET(id,""); to get them in a predefined order.

I have never used FIND_IN_SET.
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.

in MySQL at least, you can add an ORDER BY FIELD ID (4, 3, 6) to guarantee they are returned in that order.

Reply on news.ycombinator.com