Bug #39348 Add support for index clustered tables

• Submitted: 9 Sep 2008 21:35
• Modified: 11 Sep 2008 11:24
• Reporter: Dimitriy A
• Status: Not a Bug
• Category: MySQL Server
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any
• Tags: cluster, clustered, INDEX, index clustered table, table

[9 Sep 2008 21:35] Dimitriy A

Description:
Add support for index clustered tables. Clustered tables would re-sequence table rows so that contiguous blocks follow the same order as their primary index. Pretty much add support to provide similar functionality to Oracle cluster tables. Index clustered tables should be added to at least MyISAM and InnoDB.

How to repeat:
N/A

[11 Sep 2008 11:24] Mark Leith

InnoDB already uses a clustered index model, where the primary key is generally the clustered index:

http://dev.mysql.com/doc/refman/5.0/en/innodb-table-and-index.html

'Every InnoDB table has a special index called the clustered index where the data for the rows is stored. If you define a PRIMARY KEY on your table, the index of the primary key is the clustered index.

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. If there is no such index in the table, InnoDB internally generates a clustered index where the rows are ordered by the row ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.'

MyISAM does not have this feature, however you can ALTER TABLE .. ORDER BY index_col; to get the same effect, temporarily (DELETE/UPDATE/INSERT activity will eventually loose the sorting).