Hello.
Sorry about the delay, but I was side-tracked by a server crash at the
end of last week.
The EXPLAIN for the 3.23 MySQL is at least as good as for 3.22, (in
fact, it is better). As one fact, 996*1*1264=1258944 rows are less
than 986*1*1470=1449420 rows, but at least in the same magnitude and
therefore, at least in theory, the query shouldn't differ much
regarding speed.
I just reread the thread and saw that you compiled the newer MySQL
yourself. That could make part of the speed difference, because TcX'
precompiled binaries are using are well tuned (and use pgcc, which is
optimized for Pentiums).
Could you try a binary from www.mysql.com at least for one of the slow
machines and see if it makes a difference?
To be true, I am running out of ideas. On the other hand, the test
environments have too much differences to easily guess which
difference causes the performance hit. :-/
Were all your test cases on different machines, or did some use the
same hardware, just with upgraded MySQL? Just to be sure: the machines
were not used in another way during the tests, were they?
Bye,
Benjamin.
On Wed, Mar 21, 2001 at 01:10:54PM +0200, viljo@stripped wrote:
> > Okay, that means, memory is no problem. And disks shouldn't be a
> > problem either, because the table fits into memory. (Sorry, don't
> > remember the test case anymore. Did it contain INSERTs/UPDATEs?)
>
> No, only one SELECT with 3 joins. Just in case i send this query again:
>
> SELECT f.foto_id, f.imgname, f.path FROM foto f, indeks k1, indeks k2
> WHERE f.foto_id = k1.foto_id AND k1.word = 'mati' AND f.foto_id =
> k2.foto_id AND k2.word = 'kose' GROUP BY f.foto_id;
>
> > This implies, they are not the same for different versions of MySQL?
> > Then, as I said, this is the probably cause for the speed difference.
> >
> > Could you post the output of both, please?
>
> Ok, here we go (it didn't fit on one line, sorry):
>
> 3.22.32 (old and fast) EXPLAIN:
> +-------+--------+---------------+---------+---------+------------+------+-------+
> | table | type | possible_keys | key | key_len | ref | rows |
> Extra |
> +-------+--------+---------------+---------+---------+------------+------+-------+
> | k1 | ref | idx2 | idx2 | 100 | mati | 986 |
> |
> | f | eq_ref | PRIMARY | PRIMARY | 4 | k1.foto_id | 1 |
> |
> | k2 | range | idx2 | idx2 | NULL | NULL | 1470 |
> |
> +-------+--------+---------------+---------+---------+------------+------+-------+
>
> 3.23.32 (new and slow) EXPLAIN:
>
>
> +-------+--------+---------------+---------+---------+------------+------+-----------------------------+
> | table | type | possible_keys | key | key_len | ref | rows |
> Extra |
>
> +-------+--------+---------------+---------+---------+------------+------+-----------------------------+
> | k1 | ref | idx2 | idx2 | 101 | const | 996 |
> where used; Using temporary |
> | f | eq_ref | PRIMARY | PRIMARY | 4 | k1.foto_id | 1 |
> |
> | k2 | ref | idx2 | idx2 | 101 | const | 1264 |
> where used |
>
> +-------+--------+---------------+---------+---------+------------+------+-----------------------------+
>
> Newer 3.23.xx have same EXPLAIN
>
> Rgds,
> Viljo
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <mysql-thread68974@stripped>
> To unsubscribe, e-mail <mysql-unsubscribe-philemon=spin.de@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
