1

I made a local table with my PHPMyAdmin (XAMPP). I can do all sorts of things with it, but I cannot make a permanent order alteration.

For example, under the tab Operations, there is the function Alter Table Order By. But that it won't do. I can select a field, select Ascending or Descending, click Go, and the system will even report back that the query has been executed successfully. But when I subsequently browse the table, nothing has changed. No matter which field I select.

The alternative method doesn't work either. The system will let me hustle the table all I want, as long as it goes via the command

SELECT * FROM table_name ORDER BY field_name .

But when I enter

ALTER TABLE table_name ORDER BY field_name

the table doesn't change one iota, while I do get the message that the query has been executed successfully.

How is that possible?

Frank Conijn - Support Ukraine
  • 3,050
  • 1
  • 22
  • 29

1 Answers1

2

If you have an InnoDB table with a primary key or non-null unique index key, this will have no effect, as InnoDB will ALWAYS order by those keys.

Here is text directly from MySQL manual ALTER TABLE page (http://dev.mysql.com/doc/refman/5.5/en/alter-table.html):

ORDER BY does not make sense for InnoDB tables that contain a user-defined clustered index (PRIMARY KEY or NOT NULL UNIQUE index). InnoDB always orders table rows according to such an index if one is present.

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
  • OK, thanks, especially for the speedy reply. I must say that I find it ridiculous that PHPMyAdmin says that the query has been executed successfully while in practice it has not been executed at all, but I did find the remedy with the info you gave me: remove the Primary Key field. And for others who will read this: InnoDB is the default storage engine for MySQL as of MySQL 5.5. See http://en.wikipedia.org/wiki/InnoDB. Cheers, Mike! – Frank Conijn - Support Ukraine May 14 '13 at 18:03
  • @FrankConijn The query was executed, it just doesn't have any effect. This is not a problem with PHPMyAdmin, as it just reports what MySQL tells it (that the query was executed). You can run the command in MySQL directly and you will see the same result. I guess I don't understand why you would be that hung up on the order of some non-primary key, non-unique key column anyway. Is this just for your display convenience in PHPMyAdmin? If so, this is not a sound reason for removing a primary key field, as it is trivial to ORDER BY the field when querying the database. – Mike Brant May 14 '13 at 18:15
  • It is not just for display convenience. I need to do a Spearman's rho calculation between two variables, of which one is the respondents' last name (which contains an added number if more have the same last name, as in Johnson 02). For that, I have to rank the last names alphabetically and give them a rank number. That first step never worked (and cost me several hours...) until you explained why. The easiest way to give the last names their proper rank number is to remove the primary key, order the table by last name and re-insert a primary key. – Frank Conijn - Support Ukraine May 15 '13 at 01:14
  • @FrankConijn They are alternate ways to generate a numbered sequence in MySQL. For example, check out the method proposed in the second answer here: http://stackoverflow.com/questions/304461/generate-an-integer-sequence-in-mysql – Mike Brant May 15 '13 at 17:36
  • I had already found this method in another SO thread: `SET @rownum=0; UPDATE table_name SET rank_field=(@rownum:=@rownum+1);` (ordering to be done and rank_field to be created first), but I'm a bit confused: does the method you linked to order by, create a rank_field and give a rank number all at the same time? If not, what exactly does it do and what does it not do? – Frank Conijn - Support Ukraine May 16 '13 at 09:54
  • @FrankConijn That is just a sample usage from that SO question. For your case you would obviously need to add an ORDER BY clause so as to provide the ordering by which the updates are to be made. S something like `UPDATE table_name SET rank_field=(@rownum:=@rownum+1) ORDER BY field_name`. This would update those rows in the order specified by the ORDER BY field, incrementing the value of `@rownum` with each row updated. – Mike Brant May 16 '13 at 15:26
  • That code line makes sense again. Thanks once more, Mike! I really appreciate your help. – Frank Conijn - Support Ukraine May 17 '13 at 03:14