Timeline for Preordering the table by a specified column
Current License: CC BY-SA 3.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 13, 2017 at 12:13 | history | edited | CommunityBot |
replaced http://serverfault.com/ with https://serverfault.com/
|
|
| Feb 11, 2014 at 7:12 | comment | added | Buttle Butkus | I couldn't figure out why my InnoDB table was not ordered by its primary key until I saw this answer. I thought it was called a "composite index" rather than "clustered index," but either way I now see that the table is ordered by my 2nd column, which is part of such an index (along with column 3). | |
| Sep 1, 2011 at 16:41 | comment | added | RolandoMySQLDBA | Any key you create always has an additional key back to gen_clust_index attached. Shiny new would perform just as well as poor old. You could run ALTER TABLE ... ORDER BY ShinyNewKey columns to make the index pages contiguous but gen_clust_index also accessed each time back and forth with ShinyNewKey. You would have to reload table in ShinyNewKey Order for marginal results. The fewer and smaller the keys in ShinyNewKey index, the better. Try it out and let me know if table reload works for you... | |
| Sep 1, 2011 at 15:56 | comment | added | gbn | One more question: on InnoDB, is this useful when you want to add a surrogate key to a table maintaining row for row over "old" and "new" keys? So ORDER BY PoorWideOldKey gives rows in the same order as ORDER B ShinyNewNarrowKey ta | |
| Aug 29, 2011 at 14:59 | history | edited | RolandoMySQLDBA | CC BY-SA 3.0 |
added 38 characters in body
|
| Aug 29, 2011 at 14:49 | history | edited | RolandoMySQLDBA | CC BY-SA 3.0 |
added 38 characters in body
|
| Aug 29, 2011 at 14:46 | comment | added | RolandoMySQLDBA | @gbn - You are absolutely correct on InnoDB. I was just making a broader point for those interested that use MyISAM that could benefit from ORDER BY. In fact, +1 for your answer since it hits closer to home for users of InnoDB. In fact, I will add to the criteria to specifically use MyISAM. | |
| Aug 29, 2011 at 12:50 | comment | added | gbn | To clarify, assuming we stick to the rule "every table needs a clustered index" and we use InnoDB, this ORDER BY won't work. Only ISAM and heaps until the first write? | |
| Aug 29, 2011 at 12:41 | history | edited | RolandoMySQLDBA | CC BY-SA 3.0 |
added 286 characters in body
|
| Aug 29, 2011 at 12:30 | history | answered | RolandoMySQLDBA | CC BY-SA 3.0 |