【mysql】一个关于order by排序的问题

I have a table

CREATE TABLE `tableMain` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `value1` varchar(45) NOT NULL,
   'value2' varchar(50) NOT NULL,
   'value3' int NOT NULL,
   'value4' timestamp NOT NULL,
   'value5' int NOT NULL
PRIMARY KEY (`id`)
)

So I create that table and I want it to be always ordered by value2, if there is two a like it should sort by value3 and after that value4.

So I try to do it by that

ALTER TABLE tableMain
ORDER BY value2 ASC, value3 ASC, value4 ASC

And when I run that code I get an error:

Error Code: 1105. ORDER BY ignored as there is a user-defined clustered   index in the table 'tableMain'

 

If you want rows in a particular sequence, specify an appropriate ORDER BY clause in your query.

The idea that rows need to be "ordered" in a table flies in the face of relational database theory.

A relation is a set of tuples; altering the "order" of tuples within a relation does not alter the relation.

Translating the theory into practice, with the InnoDB storage engine, it doesn't make sense to specify ORDER BY as a table attribute, since an InnoDB table will always be ordered by its cluster index.

In the case of the MyISAM storage engine, specifying ORDER BY may improve performance of some queries. The ALTER TABLE ... ORDER BY statement only reorganizes the table one time. The "order" of the rows may not be preserved when subsequent DELETE, INSERT and UPDATEstatements are run.

To reiterate: if you need rows returned in a particular order, you can not depend on the "order" that rows are physically stored in a table. It's imperative that you include an ORDER BY on the query.

To really improve performance with large tables, adding appropriate indexes is the way to go.

---

As to why your classmates get the statement to run, and your statement returns an error... the most likely explanation is that their tables are using the MyISAM storage engine, while your table is using the InnoDB storage engine.

(Whatever your assignment is, changing the storage engine of your table can not be the right answer... MyISAM storage engine is an appropriate choice for some use cases; but InnoDB is the most appropriate choice for traditional "relational database" uses cases.)

---

If your requirement is that your InnoDB table to "always be ordered by" a set of columns (for whatever reason), then have the cluster index include those columns as the leading columns. You can do that by declaring those columns as the leading columns of the PRIMARY KEY of your table. You can create a UNIQUE INDEX on the id column.

CREATE TABLE `tableMain` 
( `id` INT(11) NOT NULL AUTO_INCREMENT
, `value1` VARCHAR(45) NOT NULL
, `value2` VARCHAR(50) NOT NULL
, `value3` INT NOT NULL
, `value4` TIMESTAMP NOT NULL
, `value5` INT NOT NULL
, PRIMARY KEY (`value2`,`value3`,`value4`,`id`)
, UNIQUE KEY `tableMain_UX1` (`id`)
)

In reality, we'd never do this... because any secondary indexes are going to include the PRIMARY KEY values as the "pointer" back to the cluster index, and that's going to be an incredible waste of resources. In practice, we'd leave id as the PRIMARY KEY of the table, and create a secondary index on the other columns...

CREATE TABLE `tableMain` 
( `id` INT(11) NOT NULL AUTO_INCREMENT
, `value1` VARCHAR(45) NOT NULL
, `value2` VARCHAR(50) NOT NULL
, `value3` INT NOT NULL
, `value4` TIMESTAMP NOT NULL
, `value5` INT NOT NULL
, PRIMARY KEY (`id`)
, KEY `tableMain_IX1` (`value2`,`value3`,`value4`)
)

 

source：http://stackoverflow.com/questions/29781052/order-by-ignored-as-there-is-a-user-defined-clustered-index-in-the-table/29781290#29781290