Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
-
None
Description
Our KB (https://mariadb.com/kb/en/altering-tables-in-mariadb/) says:
"Finally, with tables that contain data, occasionally it's desirable to resort the data within the table. Although the ORDER BY clause in a SELECT statement can do this on the fly as needed, sometimes developers want to do this somewhat permanently to the data within the table based on a particular column or columns. It can be done by entering the following:
ALTER TABLE client_addresses
ORDER BY city, name;
Notice that we're sorting by the city first and then by the client's name. Now when the developer enters a SELECT statement without an ORDER BY clause, the results are already ordered by the default of city and then name, at least until more data is added to the table"
This is actually NOT true for InnoDB tables with primary key defined on other columns that that ORDER BY list. MySQL manual (https://dev.mysql.com/doc/refman/5.7/en/alter-table.html) is more correct:
"Row Order for MyISAM Tables
ORDER BY enables you to create the new table with the rows in a specific order. This option is useful primarily when you know that you 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.
...
ORDER BY does not make sense for InnoDB tables because InnoDB always orders table rows according to the clustered index. "
Our KB must be chasnged/corrected to NOT produce wrong expectations that in a general case one can store the data in the table in a specific order that one can rely on in SELECTs, without ORDER By added.