List:General Discussion« Previous MessageNext Message »
From:tony Date:August 7 1999 1:01pm
Subject:Optimiser bug in 3.22.20a?
View as plain text  
>Description: The optimiser doesn't seem to join these 2 tables properly


I have two tables:

  CREATE TABLE t_items (
    uniqueid bigint(20) unsigned zerofill DEFAULT '00000000000000000000' NOT NULL
    itemid varchar(13) DEFAULT '' NOT NULL,
    PRIMARY KEY (uniqueid),
    KEY itemid (itemid)

  CREATE TABLE t_xref (
    itemid varchar(13) DEFAULT '0' NOT NULL,
    realid varchar(13) DEFAULT '0' NOT NULL,
    PRIMARY KEY (itemid),
    KEY (realid)

Items that have an entry in t_items, may or may not have a corresponding entry in t_xref.

t_items has approx 87,500 entries, and t_xref has approx 13,000

Now, I want to get a list of uniqueids from t_items for a particular realid.

As t_xref is keyed on realid, and matches exactly a key from t_items, I would have thought
this would be very fast.


    SELECT i.uniqueid
      FROM t_xref x, t_items i
     WHERE i.itemid = x.itemid
       AND x.realid = 7000000019429

takes over 2 seconds to run.

An explain gives:

| 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 |

If, however I force a straight join

    SELECT i.uniqueid
      FROM t_xref x STRAIGHT_JOIN t_items i
     WHERE i.itemid = x.itemid
       AND x.realid = 7000000019429

the query only takes about 0.05 seconds.


I'd like to think that the fix is to upgrade, but I'll wait and see first ....

>Submitter-Id:	<submitter ID>
>Originator:	Tony Bowden
>Organization:  TMtm
>MySQL support: email support 
>Synopsis:	Optimiser doesn't join tables properly
>Severity:	non-critical 
>Priority:	low 
>Category:	mysql
>Class:		sw-bug 
>Release:	mysql-3.22.20a (TCX binary)
>Server: /usr/local/mysql/bin/mysqladmin  Ver 7.8 Distrib 3.22.20a, for pc-linux-gnu on
> i686
TCX Datakonsult AB, by Monty

Server version		3.22.20a
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/tmp/mysql.sock
Uptime:			2 days 14 hours 26 min 19 sec

Threads: 23  Questions: 13153544  Slow queries: 1248  Opens: 313370  Flush tables: 1  Open
tables: 64
System: Linux pyxis 2.2.9 #2 SMP Wed Aug 4 22:31:29 BST 1999 i686 unknown
Architecture: i686

Some paths:  /usr/local/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-linux/
gcc version
Compilation info: CC='gcc'  CFLAGS='-O6 -mpentium -fomit-frame-pointer'  CXX='gcc' 
CXXFLAGS='-O6 -mpentium -fomit-frame-pointer -felide-constructors'  LDFLAGS='-static'
Configure command: ./configure  --prefix=/usr/local/mysql '--with-comment=TCX binary'
--enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static
Perl: This is perl, version 5.004_04 built for i386-linux
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