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
vijji_lakshmiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java App Servers

From novice to tech pro — start learning today.