I'm working with Enron Email Dataset in MySQL database. After I executed data import from the dump file into MyISAM-based DB, I got the set of tables without any connections between them via foreign keys.

I read that MyISAM is more suitable for the case if the tables are more static with lots of select and less update and delete operations. In my case, all queries will be read-only with multiple joins inside of each query. Considering the fact that MyISAM doesn't support foreign keys at all, will this affect the performance of my queries and if yes, how can I optimize them?

Should I convert the DB into InnoDB-format or, it's better to stay with MyISAM-format?


Foreign keys are not required in order to execute a JOIN. Foreign keys are restrictions that are there for security reasons (so that you cannot change the value of a column, or delete/insert a row ending up with an inconsistent state between tables). It is true that in most cases foreign key are used as JOIN matching conditions.

The reasons why InnoDB is recommended is because its support of data integrity thanks to these foreign keys and database transactions. It can also support better concurrency as writes do not need to lock the whole table in order to proceed, only the rows modified. That is why it is recommended for write-loads. If your tables are going to be read-only, transactions, integrity and concurrency are not so important (because you are not going to modify the data), that is why in those cases the simpler MyISAM format can work fine.

InnoDB also has its own buffering management, which can be fine-tuned better than with MyISAM, where its cache works only for indexes, leaving data caching management to the filesystem cache.

There is one case in which foreign key creation can improve your performance: on creation, InnoDB creates a secondary index on the column if it didn't have one beforehand. This functionality is as easy to overcome as creating one on any column that may be referenced on the JOIN.

In summary, InnoDB has many advantages, but in a read-only workload MyISAM can work properly. If you want the data security, however, you will have to implement that on a software layer, easily leading to errors, that is why foreign keys are always recommended.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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