I am using a MyISAM database to store user data because I am using full text searching. I have looked in to options like Sphinx but cannot install this on my shared hosting plan which uses MySQL 5.5.36.
I have been researching the problems of MyISAM and have come across the full table locking.
By using MyISAM, will this mean that, say if multiple users are on my website and two users are searching and two users are trying to INSERT into the same table that they will be unable to do so?
Is anyone able to explain to me how this works?
Thanks
Best Answer
One of the biggest problems with MyISAM is the low write concurrency. In the most general case, a single write (update, delete or insert) to a single MyISAM table will block all other writes and reads. Multiple reads, however, can happen concurrently.
You can force MyISAM to perform concurrent inserts at the end (not for updates and deletes), but it normally requires a defragmented table. Read more about concurrent inserts for MyISAM on the manual. Multiple writes can always happen to different tables.
InnoDB doesn't have those problems, and also has better memory management, corruption resilience, support for foreign keys and much more features. With InnodB you can write to the same table in concurrency as long as you do not write to the same exact row (disabled to avoid race conditions). It can provide also reads and writes at the same time, even on the same rows thanks to multi-versioning.
If MySQL FULLTEXT is good enough for you, I would recommend you to search for a hosting that provides MySQL 5.6, where InnoDB Fulltext is available. Otherwise, you will have to search for an alternative application, like the mentioned sphinx.