From: mos (mos99fastmail.fm)
Date: Tue Dec 21 2004 - 16:30:28 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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 <mos99fastmail.fm> 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/mysql?unsub=homam_sayahoo.com
> >
> >
>
>
>
>
>__________________________________
>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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]