I'm new to MySQL and I was wondering which storage engine is the best choice for heavily-indexed, read-mostly data.
From skimming over the documentation, it seems that MyISAM is a better choice since it doesn't have the transactional overhead. Yet I'm worried that it's becoming depricated and won't be supported in future versions.
I need the highest read performance possible, with many indexes and joins. It has to be able to cache query results in memory to service a large number of concurrent requests per second.
Which way to go? What's the pros and cons of each engine for my particular situation?
I appreciate your help.
-- Homam
Homam,
MyISAM will be faster than InnoDb for reading. In my tests, MyISAM was about 10x faster than an untuned InnoDb installation for simple Select statements. InnoDb is capable of faster speeds but requires a lot of tuning to get the peak performance from it. InnoDb is great for updates but for reading I prefer MyISAM hands down.
MyISAM is the most popular table type for MySQL and will be around for years to come. It also supports FullText searching which InnoDb does not (perhaps in the future it will).
Of course the bottleneck for MyISAM is its table locking. If you get more than 20 concurrent updates per second for a table, there may be delays in getting a lock on the table (you will need to do your own testing). In this case you either have to switch to InnoDb, get a faster server, or batch the updates or inserts. Keep in mind every time the table is updated, even if only 1 record is changed, the query cache is discarded (true for MyISAM and InnoDb). So you really don't want to be continuously updating the table that a lot of people are reading from. It is better to update the table every 5-10 minutes.
One more thing. If you are just inserting rows into a MyISAM table, locks are not required if the table has been optimized (holes created from deleted rows have been removed). So if you optimize the table and then do not delete any rows from the table, and people only insert rows to the table, locking should not be a problem. You will of course need to do your own testing to confirm this. Hope this helps.
Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
