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:
--
To UNSUBSCRIBE, email to debian-bugs-dist-requ...@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmas...@lists.debian.org
