Mysql – What MySQL storage engine should I choose

innodbmyisamMySQL

I have a database for my catalog webpage. Some tables are about 3GB with millions of rows.

What engine should I choose for my database, InnoDB or MyISAM?

There will only be about 1,000 new records a week, and about 1,000 deletes, but I will modify some data in one table quite often (price, quantity, etc.). All other tables will be "sealed".

I need indexes to enable fast queries. Which storage engine is best for this scenario?

Best Answer

My recommendations are to start as follows:

  1. Go for the latest version of the MySQL Server that you can get, there have been some pretty impressive work done on 5.6 although the production ready version is 5.5

  2. Go for InnoDB - comes as the default for the higher MySQL versions

  3. Configure InnoDB as follows:

    • file_per_table - creates an data file for each table instead of putting all tables in a single data files
    • innodb_buffer_pool_size - 80% of available RAM (if a dedicated machine) so that as much of your database is in memory

  4. Table column characteristics:

    • use the smallest column size needed for the data
    • use numeric foreign keys
    • index the columns used in the searches, and setup the indexes to cover the most common searches, for example if you search on columns 4, then 6 alot create a multi-column index with column 4 first followed by 6

Remember these are just starting points you can fine tune specifics as you move along, but before you fine tune, measure tune then measure again

Related Question