Fastest MySQL storage engine for storing data without concurrency
I'm using a mysql base to store results from various test over a large amount of data (hundred of millions recordings).
I'm the only user of the base so there won't be any problem of concurrency. I also want to use simple mathematical functions such as 'avg', 'std' etc
What is the best engine in your opinion for such a task?
I'm using InnoDB right now and it seems to me a bit heavy.
Regards
Guillaume
Answer
Using an InnoDB table comes with an overhead of transactional support, rollbacks etc. If you don't need this support for transactions then you should really go with an MyISam table as it doesn't have any transactional support and can be faster for lookups etc. They have been doing a lot of work on InnoDB to bring it up to speed but there is always that overhead to contend with. I would suggest some further reading on the topic before switching.
With a table as large as your suggesting a memory table may cause you issues with performance and storage. For these sorts of tables I would recommend an MyISam structure or an InnoDB table with innodb_flush_log_at_trx_commit set to 0 (this switches to a mode where transactions are not commited to disk before control is returned to the caller).