tom tom - 1 month ago 5
MySQL Question

Sort mySQL table's fields

So lets say I have a table (lets say 50 rows, 50 columns) that I occasionally have to make tiny edits to. While I realize I could be (maybe some of you will tell me I should be) using the query to do this, I like using a database management program (like Sequel Pro) to make changes by hand.

So I have two questions:

When you do a query without specifying the order to show the results, it follows the order that it is saved in the database. Is there any way to change THAT order (not the order of your results, the order in the table itself) without manually switching rows?

Also, is there any way to automatically sort the way the Fields show up in the table (the column order)?

I know that I should just be using queries to change values, but (imo) if the table is small enough, I find it easier to find the row and column in a program like Sequel Pro and just change it that way.

Also, if my methods are completely wrong, please tell me

Answer

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

ALTER TABLE name ORDER BY something

It comes with some heavy caveats, and don't rely on them for anything else then perhaps some very small performance gain in sorting by it, of just for your convenience in tools that run arbitrary selects without an order by:

ORDER BY enables you to create the new table with the rows in a specific order. Note that the table does not remain in this order after inserts and deletes. This option is useful primarily when you know that you are mostly to query the rows in a certain order most of the time. By using this option after major changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later.