Resort MySQL Table by Column Alphabetically

Question

I have table containing settings for an application with the columns: id, key, and value.

The id column is auto-incrementing but as of current, I do not use it nor does it have any foreign key constraints. I'm populating the settings and would like to restructure it so they are alphabetical as I've not been putting the settings in that way, but reordering alphabetically would help group related settings together.

For example, if I have the following settings:

ID     KEY             VALUE
======================================
1      App.Name        MyApplication
2      Text.Title      Title of My App
3      App.Version     0.1

I would want all the App.* settings to be grouped together sequential without having to do an ORDER BY everytime. Anyway, thats the explanation. I have tried the following and it didn't seem to change the order:

CREATE TABLE mydb.Settings2 LIKE mydb.Settings;
INSERT INTO mydb.Settings2 SELECT `key`,`value` FROM mydb.Settings ORDER BY `key` ASC;
DROP TABLE mydb.Settings;
RENAME TABLE mydb.Settings2 TO mydb.Settings;

That will make a duplicate of the table as suggested, but won't restructure the data. What am I missing here?

Without an order by what you get returned, and the order, are arbritary and not always the order in which you put them in the system. Wha's wrong with using the order by on the key field especially if it's indexed? (http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html) — xQbert, Feb 12 '13 at 19:05

G-Nugget · Accepted Answer · 2013-02-12T19:11:53.190

3

The easy way to reorder a table is with ALTER TABLE table ORDER BY column ASC. The query you tried looks like it should have worked, but I know the ALTER TABLE query works; I use it fairly often.

Note: Reordering the data in a table only works and makes sense in MyISAM tables. InnoDB always stores data in PRIMARY KEY order, so it can't be rearranged.

edited Feb 12 '13 at 19:11

answered Feb 12 '13 at 19:06

G-Nugget

8,666
1
24
31

Your answer helped me in a round about way to get it to work. I think my original attempt above where I am doing `SELECT key,value FROM...` needed to instead be `SELECT '' AS id, key, value FROM...` which forces it to generate a new auto-increment ID and not use the previous one. Thanks for your help! – Jeremy Harris Feb 12 '13 at 19:15
1

@cillosis That will only work one time. After you insert a bunch of new settings they again will be out of order. To achieve a long term effect you need to recreate the settings table with the `key` column as the `PRIMARY KEY`. Because as G-Nugget correctly said InnoDB always stores data in PRIMARY KEY order. That way you get your order intact after inserting new records. – peterm Feb 12 '13 at 19:40
@peterm Ok awesome, that makes a lot of sense. – Jeremy Harris Feb 12 '13 at 19:42

score 2 · Answer 2 · answered Feb 12 '13 at 20:04

Decided to make that an answer.

As I said in a comment to the initial answer, for you to achieve a long term effect you need to recreate the settings table with the key column as the PRIMARY KEY. Because as G-Nugget correctly said 'InnoDB always stores data in PRIMARY KEY order'.

You can do that like this

CREATE TABLE settings2
    (`id` int NULL, `key` varchar(64), `value` varchar(64), PRIMARY KEY(`key`));
INSERT INTO settings2 
SELECT id, `key`, `value` 
  FROM settings;
DROP TABLE settings;
RENAME TABLE settings2 TO settings;

That way you get your order intact after inserting new records.

And if you don't need the initial id column in settings table it's a good time to ditch it.

Here is working sqlfiddle

Disclaimer: Personally I would use ORDER BY anyway

Cool, I like the SQL Fiddle...hadn't seen that yet. – Jeremy Harris Feb 12 '13 at 20:14 — Jeremy Harris, Feb 12 '13 at 20:14

Resort MySQL Table by Column Alphabetically

2 Answers2