I don't think a count query is ever going to be representative of Toku's strengths. Inserting data in 1 shot is also a problem for a test on a dataset with indices that will be updated constantly. Regardless of whether it fits in memory, you're not aging the index so the BTREE will be nice and neat. Wait a few weeks and the BTREE will get more fragmented. A fractal tree is not going to get fragmented. So that's 1 flaw in the test. You're also not SELECTing anything. Once you do, you might want more than 1 clustered index, which InnoDB will not do. The way secondary indices work on InnoDB is what I hate most about it.
In InnoDB you're going to take a hit the second you use a non-primary index because it's essentially dereferencing (a quasi-self-join) a pointer in a random position with every row an index refers to. It uses a rather large 6 byte key to do this. I'm pretty sure that does horrible things to hardware cache since you're bouncing all over the place.
Depending on what you're SELECTing, the advantages of a clustered index could be huge since it would obviate that quasi-join.
But what do I know? I get away with using MyISAM + concurrent inserts on many things =)
@symcbean I believe that was the original intent of Toku (SSD), but it eventually led to grander aspirations. I vaguely remember a press release about it.
I also expect Toku to be competitive for reads except in read-heavy low-contention environments where MVCC is mostly just more complexity. Also, if the queries are very ad-hoc referring to various columns without much of a pattern, multiple clustered indexes will do less for you. Do not underestimate the utility of multiple clustered indexes when you have say ~5 types of queries that refer to ~5 different tuples each requiring a bunch of stuff to be SELECTed. Esp. without SSD, it's huge. Think about the principle of locality. InnoDB secondary indexes aren't friendly to spinning disks. I guess you can hack it with a covering index so you don't have to do that ugly quasi-join to get the other columns, but that won't scale and you're indexing lots of stuff for nothing (said: lots of GBs), esp. without compression. You're only referring to a prefix of the index. So unless it's just like 1-2 extra columns, it's a horrible abuse of space. In a clustered index, the row is just a payload on the leaves.
I'm still not sure why InnoDB can't support more than 1 clustering index regardless. It would be useful even in MyISAM in moderation. People abuse covering indexes to mimic clustered indexes all the time, and it would be nice.
@Zardosht Even if it does fit in memory, doesn't Toku keep the trees more balanced than BTREEs over time, offering better performance even in that case?
Also, why can't Toku do a partial clustering index. What if you don't need the whole row?