I do the following:
time ./bench.pl >/dev/null
Using oracle, I get 4 seconds
Using MySQL, I get 6 minutes 22 seconds
This is running the command from the command line at least 10 times in a row -
just to be sure the system can cache as much as possible.
As near as I can tell, MySQL will use the index if the WHERE clause contains
"LIKE 'foo%'" but not if it contains "LIKE '%foo%'". In the first case, the
first 'foo' is found and then it scans the index forward until it no longer
gets a match wtih 'foo%'. In the latter case, it needs to scan from the first
values in the index through tthe entire index and select those that match.
If one of the mysql developers added the code for the latter case, we could
actually see the performance gain, if any.
Right now, all we are doing is guessing what the gain would be.
On Mon, 18 Oct 1999, Scott Hess wrote:
> Mike Schwartz <mykes@stripped> wrote:
> > I created an index that contained the most common of the 50 fields
> queries acted
> > upon. This index fits entirely in memory (250K x 1K/index would fit in
> 256M of
> > RAM!). I would hope that MySQL would be smart enough to use available
> memory
> > to cache entire indexes that easily fit in the machine's RAM.
> <snip>
> > For what it's worth, the .ISD file for this database is 791M, the ISM
> file is
> > 135M, and the .frm file is 9K. That 135M should easily fit in RAM on my
> 256M
> > machine. The production servers have 512M... Even if it didn't fit,
> scanning
> > the 135M file on disk would be (logically) almost 8x faster.
>
> Have you compared the time for the query for the first run on a cold system
> versus the second run? MYSQL relies heavily on various caches, and the
> second time you run a query can be a lot faster. MYSQL should just be
> scanning the ISM file as you describe (though it's possible that something
> about the query precludes it - for instance, using VARCHAR versus BINARY
> VARCHAR can sometimes have effects like this under 3.22.*).
>
> > I am not using the context option of oracle. The initora table was set
> up to
> > assure the indexes fit in RAM all the time.
>
> You might also try using isamchk (or myisamchk, of course) to clean up the
> table after you've created it. It's possible that Oracle manages to end up
> with a more efficient index structure than MYSQL after a random set of
> inserts.
>
> Later,
> scott
>
>
>
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread16276@stripped
>
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.
--
Mike Schwartz
mykes@stripped
http://home.webmaster.com
