can we acheive clustering with InnoDB

Posted on 2008-11-03
Last Modified: 2013-12-01
  can we acheive clustering with InnoDB by using some added plugins like that.
or there is no way to do that??
please reply ASAP.
Question by:vijji_lakshmi

    Expert Comment


    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,


    Author Comment

    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.

    Accepted Solution


    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),

    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.


    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 :)



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    Upgrading Tomcat – There are a couple of methods to upgrade Tomcat is to use The Apache Installer is to download and unzip and run the services.bat remove|install Tomcat6 Because of the App that we are working with, we can only use Tomcat 6.…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now