I've created "MY TABLE" based on Innodb with composite primary key because I am storing many trees inside this table. The PK is for example (a,b,c,d).

1- Does Innodb store rows with same (a,b,c) near together?

2- Does every (a,b,c) add one 'page' (at least 4 kb) and so increase my table size?

3- Does this query almost checks all table?
Select * from mytable where a='1' and b='2' So it would be faster if I user 'MyIsam' and use secondary index (a,b) ?

Many Thanks

PS: I edited and removed that question. (yeah,that was really funny :) )

  • It appears as if you have created duplicate accounts. If that is the case please merge your accounts. That will allow you to edit your question without going through the review process, and give you other privileges like the ability to comment on answers to this question. – Erik Jan 21 '16 at 19:14
up vote 1 down vote accepted
  1. (For InnoDB) All the rows with the same a will be next to each other; all rows with same a and b, will be adjacent, etc. So, I guess "yes" is the answer to #1.

  2. No. An InnoDB "block" is 16KB. If the entire table fits in one block, then everything will be in that one block. Note: The PRIMARY KEY is "clustered" with the table. So talking about the layout of the PK and the layout of the table are one and the same.

  3. (Q3 has been changed; this edit addresses the new Q3.) For WHERE a='1' AND b='2', whether MyISAM or InnoDB, any index beginning with (a,b) or (b,a) will be optimal. Note "beginning with"; since the PRIMARY KEY already begins with (a,b), adding another index not be useful. See my cookbook on creating indexes.

MyISAM and InnoDB differ (for this discussion) in the following ways:

  • MyISAM does not "cluster" the PK with the data. The PK is a separate BTree and the data is (mostly) appended to without regard to PK order.

  • MyISAM index blocks are 1KB.

Did you have other questions about the difference between MyISAM and InnoDB?

In order to determine the optimal indexes, first determine what the queries will look like. WHERE c = 124 begs for INDEX(c), or at least an index that starts with c. Rearranging the PK to PRIMARY KEY(c,a,b,d) would suffice, but might hurt other queries.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.