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
Homan,
I can't really say since I've never joined heterogeneous tables together. The best thing you can do is create a test database with the same table structure. Fill it with 10x the number of rows you expect to have, and then execute your expected SQL query statements. Measure the times to see how it can be optimized. Then when you have it optimized for one user, simulate multiple clients from several machines doing the same queries to see how it reacts.
To fill the text data you can use words from the dictionary. There are a few free plain text dictionaries online. See http://www.translatum.gr/dictionaries/download-english.htm. Load the words into a (memory) table and then randomly add words to your test table's text/memo fields. I've used this technique to fill tables with 10 million rows of random words. Each memo field can have dozens of words (random number), and the text fields can have multiple words as well. Now you can do full text search of multiple word occurrences, such as "dog" and "tree" to see how fast it is. Your client computers can pull in 1 or 2 words at random from the dictionary, and then do a fulltext search on it. You can then time the results. This should get you going. :)
Mike
--- 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]
