List:General Discussion« Previous MessageNext Message »
From:tony Date:August 8 1999 1:35pm
Subject:Re: Optimiser bug in 3.22.20a?
View as plain text  
Michael Widenius <monty@stripped> writes:
>+-------+--------+----------------+---------+---------+----------+-------+------------+
>| table | type   | possible_keys  | key     | key_len | ref      | rows  | Extra     
> |
>+-------+--------+----------------+---------+---------+----------+-------+------------+
>| i     | ALL    | itemid         | NULL    |    NULL | NULL     | 87519 |           
> |
>| x     | eq_ref | PRIMARY,realid | PRIMARY |      13 | i.itemid |     1 | where used
> |
>+-------+--------+----------------+---------+---------+----------+-------+------------+

>tony> If, however I force a straight join

>What is the explain for the later query?

+-------+------+----------------+--------+---------+----------+-------+------------+
| table | type | possible_keys  | key    | key_len | ref      | rows  | Extra      |
+-------+------+----------------+--------+---------+----------+-------+------------+
| x     | ALL  | PRIMARY,realid | NULL   |    NULL | NULL     | 13482 | where used |
| i     | ref  | itemid         | itemid |      13 | x.itemid |    10 |            |
+-------+------+----------------+--------+---------+----------+-------+------------+

>Try running 'isamchk -a' on your tables.  Do 'mysqladmin flush-tables' 
>and try again!

Doesn't make a lot of difference ... speeds slightly but not really noticeably.

>SELECT i.uniqueid
>  FROM t_xref x, t_items i
>  WHERE i.itemid = x.itemid
> AND x.realid = '7000000019429'
>(Why are the columns VARCHAR and not bigint ?)

However, this takes 0.00 seconds!

An explain shows:

+-------+------+----------------+--------+---------+---------------+------+-------+
| table | type | possible_keys  | key    | key_len | ref           | rows | Extra |
+-------+------+----------------+--------+---------+---------------+------+-------+
| x     | ref  | PRIMARY,realid | realid |      13 | 7000000019428 |    1 |       |
| i     | ref  | itemid         | itemid |      13 | x.itemid      |    3 |       |
+-------+------+----------------+--------+---------+---------------+------+-------+

which is very interesting...

There are a variety of reasons why the ids were VARCHAR rather than BIGINT, not
all of which I can remember - some to do with the database we inherited, some
to do with problems in Perl dealing with integers that big ...

The IDs are all 13 digit numbers, starting 7x000000 ... where x differentiates the 
type of item. Does this mean that MySQL isn't very good at dealing with this if
it's referred to in numeric context, but is fine when treated as a string?

I guess I'm just too used to Perl's blindness in this regard :)

Tony
-- 
-----------------------------------------------------------------------------
 Tony Bowden | Belfast, NI | tb@stripped | www.tmtm.com | www.blackstar.co.uk
   all history is too small for even me; for me and you,exceedingly too small
-----------------------------------------------------------------------------


Thread
Optimiser bug in 3.22.20a?tony7 Aug
  • Optimiser bug in 3.22.20a?Michael Widenius8 Aug
    • Re: Optimiser bug in 3.22.20a?tony8 Aug
      • Re: Optimiser bug in 3.22.20a?Benjamin Pflugmann8 Aug
        • Re: Optimiser bug in 3.22.20a?Michael Widenius8 Aug