Thanks Mike. I think testing ultimately determines how efficient heterogeneous engine joins are. I just wanted to know if someone had issues with them in a heavy-load environment.
--- mos <[EMAIL PROTECTED]> wrote: > At 04:00 PM 12/21/2004, Homam S.A. wrote: > >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] > > __________________________________ Do you Yahoo!? Yahoo! Mail - now with 250MB free storage. Learn more. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
