FrankC wrote:the table doesn't change one iota, while I do get the message that the query has been executed successfully.
What's up with that??
The problem is not on MySQL, the problem is on yourself. What did you expect, what should happen after executing the "ALTER" command?
If you have a table, and want to see the contents of that table, you have to run a "SELECT" command. If you do not supply any "ORDER" clause, the table order (shown by this SELECT) is the order of the primary key.
If you apply an ORDER clause "SELECT * ... ORDER BY field_name", this will result in a table, which is ordered by field_name.
In any case, the ORDER of the table is NOT affected by an "ALTER" clause, it is only effected by the SELECT ... ORDER statement.
So, what does it mean to run a "ALTER ... ORDER by field_name"? As stated above, this does not have any effect on subsequent SELECTs. So this seems to be a stupid statement. But - it is not. The problem ist not the ORDER itself, the problem is execution time. Per default, all tables are (internally) ordered by the primary key. Therefore, if you run a "SELECT .... ORDER BY prim_key", this SELECT is executed very very fast, because the whole table is already ordered by the Primary Key and there is no extra work to be done for ordering this table.
But if you run "SELECT ... ORDER by field_name" instead, MySQL has to re-order all data what might take a certain amount of time. And this is the idea of using "ALTER": in that case, the table is physically NOT ordered by the primary key, but by the field_name. And if you run a SELECT .... ORDER by field_name after that, MySQL does not need to re-order the table, as it is already ordered in that way.
BUT: the result of ANY SELECT is ALWAYS the same (this is what makes you believing, that ALTER is not working). But the execution time can vary extremeley, dependig on the size of the table and depending on the default physically ORDER.
Therefore, you cannot see any difference before/after running this ALTER statement. But if you run benchmarks and have large amount of data, you will see big differences in execution time.