Your message dated Mon, 19 Mar 2012 18:23:14 +0000
with message-id <[email protected]>
and subject line okay got it
has caused the Debian Bug report #591271,
regarding mysql-server-5.0: optimizer fails with 2-table join, but optimizes
away all rows when using nested query
to be marked as done.
This means that you claim that the problem has been dealt with.
If this is not the case it is now your responsibility to reopen the
Bug report if necessary, and/or fix the problem forthwith.
(NB: If you are a system administrator and have no idea what this
message is talking about, this may indicate a serious mail system
misconfiguration somewhere. Please contact [email protected]
immediately.)
--
591271: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=591271
Debian Bug Tracking System
Contact [email protected] with problems
--- Begin Message ---
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:
--- End Message ---
--- Begin Message ---
Okay I have read through the bug report properly and now I am much
clearer - not least about the roles of the different contributors.
Essentially this bug report says the optimizer will always make mistakes
even if it continuously improves. The solution lies in standard Db
administration good practice. As such I am closing this.
--- End Message ---
