程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 【mysql】一個關於order by排序的問題,mysqlorderby排序

【mysql】一個關於order by排序的問題,mysqlorderby排序

編輯:MySQL綜合教程

【mysql】一個關於order by排序的問題,mysqlorderby排序


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 DELETEINSERT 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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved