Mysql – Innodb composite primary key with 4 columns vs MyIsam

innodbmyisamMySQL

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

Best Answer

(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.
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.
(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.

Related Solutions

Mysql – Insert-heavy InnoDB table won’t use all the CPU

You have to crank up innodb_io_capacity as well.

The default is 200. Raise it to 5000 for starters. I would go to 20000.

You may also want to make sure ib_logfile0 and ib_logfile1 are sufficiently large. The default value for innodb_log_file_size is 5M. I would raise that to 1G for starters.

A larger InnoDB Buffer Pool would also help, perhaps 4G.

To recap, use these additional settings:

[mysqld]
innodb_io_capacity=5000
innodb_buffer_pool_size=4G
innodb_log_file_size=1G

After adding these settings to my.cnf, to resize ib_logfile0/ib_logfile1 do the following

service mysql stop
rm -f /var/log/mysql/ib_logfile[01]
service mysql start

The files ib_logfile0 and ib_logfile1 are recreated. Don't worry, I have done this many times.

You may have to do something out of the ordinary for InnoDB

Try the following:

Full Table Lock on the InnoDB table
Perform the Bulk Load
Release the Lock

Mysql – way to prevent queries from waiting for table level lock

The MyISAM Storage Engine is furiously notorious for performing full table locks for any DML (INSERTs, UPDATEs, DELETEs). InnoDB would definitely solve that issue in the long term.

I wrote about pros and cons of using MyISAM vs InnoDB

InnoDB vs MyISAM with many indexes (Jul 05, 2012)
Which is faster, InnoDB or MyISAM? (May 03, 2012)
Any gotchas at all with converting from MyISAM to InnoDB? (Jan 09, 2012)
Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks? (Oct 07, 2011)
Benefits of table level locking (Sep 14, 2011)
What are the main differences between InnoDB and MyISAM? (Apr 14, 2011)

With regard to your current question, here is a possible scenario:

article and article_comments are both MyISAM tables
article_comments has one or more indexes with status as a column
Index page updates for article_comments are cached in the MyISAM Key Buffer (sized by key_buffer_size), causing old index pages out of the MyISAM Key Buffer
You have SELECT queries that perform JOINs between article and article_comments

In my suggested scenario, SELECTs against the article table can be held up from allowing writes because of having to wait for article_comments to be free from any DML (in this case, an UPDATE)

Best Answer

Related Solutions

Mysql – Insert-heavy InnoDB table won’t use all the CPU

Mysql – way to prevent queries from waiting for table level lock

Related Question