Thanks Mike for the information. Yes, Emmett mentioned the same thing in a private message, and it seems that MyISAM is exactly what I'm looking for: a heavily-indexed large table that will be also indexed for full-text search and built off-line -- no updates whatsoever.
However, I will be joining this table with other tables that need to be updated frequently, and that would require InnoDB for concurrency. And my concern is: How efficiently MySQL handles joins between MyISAM and InnoDB tables? In other words, does the overhead of joining between a MyISAM table and an InnoDB table outweigh any read performance advantage I get by using MyISAM for the read-mostly table? Meaning, would it be more efficient if I just made all the tables InnoDB to improve join performance, or there's no particular overhead in heterogeneous engine queries? I appreciate your feedback, Homam --- mos <[EMAIL PROTECTED]> wrote: > At 06:37 PM 12/20/2004, you wrote: > >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] > > __________________________________ Do you Yahoo!? Send a seasonal email greeting and help others. Do good. http://celebrity.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
