Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What's the faster scheme for insert data to TokuDB or InnoDB Engine? Over 200 Insert per second.

That's okay to answer of one engine.

TABLE: `game`
`gameId` bigint 20 (PK)
`gameMapId` int 4
`gameType` varchar 20
`createDate` datetime (index)

TABLE: `game`
`idx` bigint 20 (autoincrement PK)
`gameId` bigint 20 (UK)
`gameMapId` int 4
`gameType` varchar 20
`createDate` datetime (index)
share|improve this question
2  
Have you tested them? –  ypercube May 17 '13 at 9:44
1  
Why would you add just another unique key to an already existing one? Is gameId subject to changes? –  dezso May 17 '13 at 10:42
    
@ypercube Yes, I did test, but I can't know effect because other service are running in same system. –  kargnas May 17 '13 at 11:44
    
@dezso gameID is unique value. will never be changed. –  kargnas May 17 '13 at 11:46
add comment

2 Answers

Percona has already done some benchmarks with TokuDB vs InnoDB

See my post from 10 days ago : TokuDB not much faster than MySQL

Better answers are there as well.

share|improve this answer
    
That's just one query test. Not enough to judge. –  kargnas May 29 '13 at 1:36
add comment

The following is valid for both engines (InnoDB and TokuDB).

The first schema will contain two indexes: a clustered primary key index (gameId) and a secondary index (createDate).

The second schema will create three indexes: a clustered primary key index (idx), a unique secondary index (gameId), and a secondary index (createDate).

Therefore, each insert will require more index maintenance for your second proposed table schema. It will also be larger on disk given the extra index.

Another important factor to keep in mind is that performing searches by gameId will require a single lookup in the first schema, but two lookups in the second schema (one on the gameId index to get PK values, and another on the PK).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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