[debian-mysql] Bug#591271: mysql-server-5.0: optimizer fails with 2-table join, but optimizes away all rows when using nested query

[tabris]

Package: mysql-server-5.0
Version: 5.0.51a-24+lenny4
Severity: important


Tables are InnoDB engine, the schema I can submit in a followup to this
email.

There are two keys on sample:
a)  KEY `sample_timestamp` (`interface`,`timestamp`),
b)  KEY `sample_interface_id` (`interface`,`id`)

I think it chooses the wrong key for query 1, but forcing it to use the
other key doesn't help.

The tables _are_ being updated live (every 5 seconds), so the number of
rows in the EXPLAIN changes as well. Running ANALYZE does not help
(much).

mysql> explain SELECT MAX(sample.id) FROM sample JOIN interface ON
(sample.interface=interface.id) WHERE
interface.name='accounting-total'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: interface
         type: const
possible_keys: PRIMARY,name
          key: name
      key_len: 32
          ref: const
         rows: 1
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: sample
         type: ref
possible_keys: sample_timestamp,sample_interface_id
          key: sample_timestamp
      key_len: 4
          ref: const
         rows: 654966
        Extra: Using index
2 rows in set (0.00 sec)

mysql> explain SELECT MAX(sample.id) FROM sample WHERE interface=(SELECT
interface.id FROM interface WHERE interface.name='accounting-total')\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: interface
         type: const
possible_keys: name
          key: name
      key_len: 32
          ref:
         rows: 1
        Extra: Using index
2 rows in set (1.04 sec)

mysql> SELECT MAX(sample.id) FROM sample JOIN interface ON
(sample.interface=interface.id) WHERE interface.name='accounting-total';
+----------------+
| MAX(sample.id) |
+----------------+
|        6235488 |
+----------------+
1 row in set (0.60 sec)

mysql> SELECT MAX(sample.id) FROM sample WHERE interface=(SELECT
interface.id FROM interface WHERE interface.name='accounting-total');
+----------------+
| MAX(sample.id) |
+----------------+
|        6235488 |
+----------------+
1 row in set (0.00 sec)

Trying this query another way produces worse results than the first,
even if it should be equivalent. And it still chooses the wrong index.

mysql> explain SELECT MAX(sample.id) FROM sample WHERE interface IN
(SELECT interface.id FROM interface WHERE
interface.name='accounting-total')\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: sample
         type: index
possible_keys: NULL
          key: sample_timestamp
      key_len: 12
          ref: NULL
         rows: 6239592
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: interface
         type: const
possible_keys: PRIMARY,name
          key: name
      key_len: 32
          ref: const
         rows: 1
        Extra: Using index
2 rows in set (0.00 sec)


-- System Information:
Debian Release: 5.0.4
  APT prefers stable
  APT policy: (500, 'stable')
Architecture: i386 (i686)

Kernel: Linux 2.6.26-1-686 (SMP w/2 CPU cores)
Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/bash

Versions of packages mysql-server-5.0 depends on:
ii  adduser                3.110             add and remove users and groups
ii  debconf [debconf-2.0]  1.5.24            Debian configuration management sy
ii  libc6                  2.7-18lenny2      GNU C Library: Shared libraries
ii  libdbi-perl            1.605-1           Perl5 database interface by Tim Bu
ii  libgcc1                1:4.3.2-1.1       GCC support library
ii  libmysqlclient15off    5.0.51a-24+lenny4 MySQL database client library
ii  libncurses5            5.7+20081213-1    shared libraries for terminal hand
ii  libreadline5           5.2-3.1           GNU readline and history libraries
ii  libstdc++6             4.3.2-1.1         The GNU Standard C++ Library v3
ii  libwrap0               7.6.q-16          Wietse Venema's TCP wrappers libra
ii  lsb-base               3.2-20            Linux Standard Base 3.2 init scrip
ii  mysql-client-5.0       5.0.51a-24+lenny4 MySQL database client binaries
ii  mysql-common           5.0.51a-24+lenny4 MySQL database common files
ii  passwd                 1:4.1.1-6+lenny1  change and administer password and
ii  perl                   5.10.0-19lenny2   Larry Wall's Practical Extraction 
ii  psmisc                 22.6-1            Utilities that use the proc filesy
ii  zlib1g                 1:1.2.3.3.dfsg-12 compression library - runtime

Versions of packages mysql-server-5.0 recommends:
ii  bsd-mailx [mailx]  8.1.2-0.20071201cvs-3 A simple mail user agent
ii  libhtml-template-p 2.9-1                 HTML::Template : A module for usin
ii  mailx              1:20071201-3          Transitional package for mailx ren

Versions of packages mysql-server-5.0 suggests:
pn  tinyca                        <none>     (no description available)

-- debconf information:
  mysql-server-5.0/really_downgrade: false
  mysql-server-5.0/need_sarge_compat: false
  mysql-server-5.0/start_on_boot: true
  mysql-server/error_setting_password:
  mysql-server-5.0/nis_warning:
  mysql-server-5.0/postrm_remove_databases: false
  mysql-server-5.0/need_sarge_compat_done: true
  mysql-server/password_mismatch: