Important notes about InnoDB

Executing a simple statement like SELECT count(*) FROM [table name] without having indexes in place would be very slow as it does a full table scan to retrieve data. If you want to use count query frequently in the InnoDB table, it is suggested you create triggers on insert and delete operations, after which you can increase or decrease counters when records are inserted or deleted, which would help you achieve better performance.

InnoDB is a multiversion concurrency control (MVCC) storage engine that keeps information of old versions of changed rows to support the transaction and rollback features, which comes in very handy in case of data integrity or failure.

To optimize InnoDB table performance, the following are a few settings that we can use in my.cnf settings. However, it would vary based on your environment and databases.

• innodb_open_files=300: This defines the maximum number of open files which it can keep open while working with the innodb_file_per_table mode.
• innodb_buffer_pool_size = 128M: This specifies the pool size in memory which can be used to cache the indexes and table data. This is one of the important aspects to tune the InnoDB table. We can increase this value based on RAM size on the server.
• innodb_thread_concurrency = 8: This setting is used for a number of concurrent threads to be used to process the request that is derived based on the number of CPUs available.