I am not sure exactly if there is a plug in to achieve clustering, but you can achieve this using primary and secondary keys. If you use a prefix for the primary key and then a secondary key that points to the rows information you will achieve the desired affect for clustering in regards to InnoDB storage design.

Let me know if you need further explaining,

D

0

vijji_lakshmiAuthor Commented: 2008-11-04

please give me detailed explanation. i dint understand this point also"If you use a prefix for the primary key and then a secondary key that points to the rows information you will achieve the desired affect for clustering in regards to InnoDB storage design".
please give me any examples to refer.
Rgds,
vijji

0

designitmCommented: 2008-11-04

Hi,

I will try to explain this as best as possible, but it requires some knowledge on indexes.

Suppose the table is populated with primary key values 1 to 10,000, inserted in random order and then optimized with OPTIMIZE TABLE. In other words, the data is arranged optimally on disk, but the rows may be in a random order. The values for col2 are randomly assigned between 1 and 100, so there are lots of duplicates.

Explanation:

InnoDB actually has a clustered storage design and through the use of clever indexing you are able to created a clustered storage affect. This happens in the index design you choose, which is the col2 row of your table. When you use an index to store data, the data clusters itself in order to provide a speedy index for retrieval. So your items will be organized from 1 - 100 where each cluster will have its associative primary key value pointing to the row of data in that table.

When the data is changed and the index changes so lets say from 1 to 20 in col2, the whole row moves at that point and not just the indexes while maintaining the primary key value as a pointer to the row data.

This gives the affect I think you are wanting, which speeds retrieval time, but does add a little overhead for inserts and updates and deletes. The index sizes may grow, but that is okay considering the faster retrieval and organization you will have.

As a tip, use not null field types which is faster for retrieval and better storage.