2 ms·
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
by ars 17y ago
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.
- petercooper 17y agoInteresting. 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 :-)
- karipatila 17y agoYou'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.
- learnalist 17y agoI have never used FIND_IN_SET. But I use FIELD() http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_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.
- adatta02 17y agoin MySQL at least, you can add an ORDER BY FIELD ID (4, 3, 6) to guarantee they are returned in that order.