On 2001-03-21, Viljo Marrandi <[EMAIL PROTECTED]> wrote:
> 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;
[snip; heavy snippage on the following tables to clean them up]
> 3.22.32 (old and fast) EXPLAIN:
> +-------+--------+---------------+---------+---------+------------+-----
> | table | type | possible_keys | key | key_len | ref | rows
> +-------+--------+---------------+---------+---------+------------+-----
> | 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
> +-------+--------+---------------+---------+---------+------------+-----
> | 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
Hm. A couple of things occur to me looking at the above:
-the tables in the 3.23.32 example may not be defined the same way. In
particular, key_len changes from 100 to 101 bytes for the indeks table
lookup. Perhaps a char/varchar is defined NOT NULL on the 3.22 box and
not on the 3.23 ?
-a temp table is/will be used in 3.23, and not in 3.22. Significance...?
-3.22 thinks that there is no index it can use for the k2 join of indeks.
Therefore it plans to table-scan through 1470 records. OTOH, 3.23 plans
to use a key on the k2 join. But, perhaps (gasping) that causes instead
of a single scan through the indeks table, 1,264 seeks from the index
file to the data file to the index file... etc? Still, your data set is
surely small enough to fit all in disk cache. But perhaps there's also
a thousandfold increase in the work done by mysqld at some point during
this query? (Grasping again.)
In terms of query-optimization, it seems (logically, not necessarily the
same as SQLese :) that you really want to join indeks with itself to find
indeks.foto_id's which have a word='foo' and a word='bar', and then join
that result set with f. Perhaps there's a better way to optimize the query
/ indexes to reflect that?
--
Hank Leininger <[EMAIL PROTECTED]>
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
