News for hackers. Stuff that matters.
Re:Digg: 40x Performance Increase by Sorting in PHP Rather t... by petercooper on Tuesday March 23, 2010 @11:47PM (#1214891)
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.

Re:Digg: 40x Performance Increase by Sorting in PHP Rather t... by ars on Wednesday March 24, 2010 @02:06AM (#1215087)
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.

Re:Digg: 40x Performance Increase by Sorting in PHP Rather t... by petercooper on Wednesday March 24, 2010 @02:34AM (#1215125)
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 :-)