3 ms·
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
by petercooper 17y ago
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! :-)
- ars 17y agoSo 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.
- petercooper 17y agoJust 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.
- ars 17y agoUsing 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.
- petercooper 17y agoIt 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 :-)