[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

can we acheive clustering with InnoDB

Hi,
  can we acheive clustering with InnoDB by using some added plugins like that.
or there is no way to do that??
please reply ASAP.
Regds,
vijji
0
vijji_lakshmi
Asked:
vijji_lakshmi
  • 2
1 Solution
 
designitmCommented:
Hi,

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:
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:
Hi,

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

Here is a sample table:

CREATE TABLE layout_test (
   col1 int NOT NULL,
   col2 int NOT NULL,
   PRIMARY KEY(col1),
   KEY(col2)
);

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.

I hope this helps :)

D




0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now