mattal999 Posted September 25, 2011 Share Posted September 25, 2011 This problem has been nagging me for a while now. I have multiple queries that contain this at the end: ORDER BY done = 0 DESC, posted DESC Which makes the results ordered in the form 0, 1, -1 which is fine. Now, because of the = 0, MySQL has to perform a filesort. Is there any way around this? Also, is there any way to order the rows by 'done' = 0 first, but then disregard the 'done' state and just sort by posted? Quote Link to comment https://forums.phpfreaks.com/topic/247829-ordering-by-a-specific-value-without-using-a-filesort/ Share on other sites More sharing options...
fenway Posted September 25, 2011 Share Posted September 25, 2011 If you sort by an expression, you're going to get a filesort --- nothing you can do about that at the moment. But yes, if you sort by "ORDER BY IF( done = 0, 1, -1 ) DESC, posted DESC", you'll effectively get what you want. Quote Link to comment https://forums.phpfreaks.com/topic/247829-ordering-by-a-specific-value-without-using-a-filesort/#findComment-1272635 Share on other sites More sharing options...
mattal999 Posted September 26, 2011 Author Share Posted September 26, 2011 Oh, ok. That sorts things out (pardon the pun). How about using multiple queries (or joins) instead? If I wanted to get all of the done = 0 first, then all others and have them all sorted by posted DESC, how would I do that? Quote Link to comment https://forums.phpfreaks.com/topic/247829-ordering-by-a-specific-value-without-using-a-filesort/#findComment-1272944 Share on other sites More sharing options...
fenway Posted September 27, 2011 Share Posted September 27, 2011 How's that any different? Quote Link to comment https://forums.phpfreaks.com/topic/247829-ordering-by-a-specific-value-without-using-a-filesort/#findComment-1273238 Share on other sites More sharing options...
mattal999 Posted September 27, 2011 Author Share Posted September 27, 2011 Well, I'm just trying to get rid of the filesort. Quote Link to comment https://forums.phpfreaks.com/topic/247829-ordering-by-a-specific-value-without-using-a-filesort/#findComment-1273241 Share on other sites More sharing options...
fenway Posted September 27, 2011 Share Posted September 27, 2011 MySQL will only use an index from the first joined table for ordering Quote Link to comment https://forums.phpfreaks.com/topic/247829-ordering-by-a-specific-value-without-using-a-filesort/#findComment-1273244 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.