>Description:
Index analysis and optimization is choosing wrong index when a
table involved in a JOIN is compound. MySQL should still be using
the first part of the GECK_STATE PRIMARY key (geck_id) since it is the column being
used to JOIN on the GECK table.
>How-To-Repeat:
You should already have edecosta.mathworks.com.gz on your ftp site. If not, contact me
and I will up-load again.
mysql> show index from GECK_STATE;
+------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
|Collation | Cardinality | Sub_part | Packed | Comment |
+------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+---------+
| GECK_STATE | 0 | PRIMARY | 1 | geck_id | A
| | NULL | NULL | NULL | |
| GECK_STATE | 0 | PRIMARY | 2 | requiredby | A
| | 105779 | NULL | NULL | |
| GECK_STATE | 1 | team | 1 | team | A
| | 4 | NULL | NULL | |
| GECK_STATE | 1 | requiredby | 1 | requiredby | A
| | 36 | NULL | NULL | |
| GECK_STATE | 1 | owner | 1 | owner | A
| | 384 | NULL | NULL | |
| GECK_STATE | 1 | status_id | 1 | status_id | A
| | 19 | NULL | NULL | |
| GECK_STATE | 1 | verifiedin | 1 | verifiedin | A
| | 5 | NULL | NULL | |
| GECK_STATE | 1 | codedon | 1 | codedon | A
| | 2250 | NULL | NULL | |
| GECK_STATE | 1 | closedon | 1 | closedon | A
| | 2299 | NULL | NULL | |
| GECK_STATE | 1 | codedby | 1 | codedby | A
| | 418 | NULL | NULL | |
| GECK_STATE | 1 | closedby | 1 | closedby | A
| | 491 | NULL | NULL | |
| GECK_STATE | 1 | priority | 1 | priority | A
| | 4 | NULL | NULL | |
| GECK_STATE | 1 | plannedfor | 1 | plannedfor | A
| | 33 | NULL | NULL | |
| GECK_STATE | 1 | date_plannedfor | 1 | date_plannedfor | A
| | 1 | NULL | NULL | |
| GECK_STATE | 1 | date_requiredby | 1 | date_requiredby | A
| | 1 | NULL | NULL | |
+------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+---------+
EXPLAIN SELECT GECK.summary AS Summary, STATUS.status AS Status, GECK.type AS Type,
U1.user AS Owner, GECK.geck_id AS Record
FROM GECK_STATE, USER U1, COMPONENT, STATUS, GECK
WHERE (GECK_STATE.owner = U1.user_id
AND GECK.component_id = COMPONENT.component_id
AND GECK_STATE.status_id = STATUS.status_id
AND GECK.geck_id = GECK_STATE.geck_id )
AND ((COMPONENT.component = 'Web Gecko' AND STATUS.status IN ('Unreviewed', 'Failed
to verify', 'Need more info', 'Under consideration',
'Under investigation', 'Waiting for vendor', 'To do', 'To prequalify', 'To
submit', 'In BaT', 'To verify', 'Add testpoint')) )
ORDER BY STATUS.rank DESC, GECK.type ASC, U1.user ASC, GECK.geck_id ASC;
+------------+--------+-------------------------+-----------+---------+--------------------+------+---------------------------------------------+
| table | type | possible_keys | key | key_len | ref
| | rows | Extra |
+------------+--------+-------------------------+-----------+---------+--------------------+------+---------------------------------------------+
| COMPONENT | ref | PRIMARY,component | component | 32 | const
| | 1 | where used; Using temporary; Using filesort |
| STATUS | ALL | PRIMARY,status | NULL | NULL | NULL
| | 18 | where used |
| GECK_STATE | ref | PRIMARY,owner,status_id | status_id | 1 |
|STATUS.status_id | 5567 | |
| U1 | eq_ref | PRIMARY | PRIMARY | 2 |
|GECK_STATE.owner | 1 | where used |
| GECK | eq_ref | PRIMARY,component_id | PRIMARY | 4 |
|GECK_STATE.geck_id | 1 | where used |
+------------+--------+-------------------------+-----------+---------+--------------------+------+---------------------------------------------+
mysql> show index from GECK_STATE;
+------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
|Collation | Cardinality | Sub_part | Packed | Comment |
+------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+---------+
| GECK_STATE | 0 | PRIMARY | 1 | geck_id | A
| | 105864 | NULL | NULL | |
| GECK_STATE | 1 | team | 1 | team | A
| | 5 | NULL | NULL | |
| GECK_STATE | 1 | requiredby | 1 | requiredby | A
| | 35 | NULL | NULL | |
| GECK_STATE | 1 | owner | 1 | owner | A
| | 387 | NULL | NULL | |
| GECK_STATE | 1 | status_id | 1 | status_id | A
| | 18 | NULL | NULL | |
| GECK_STATE | 1 | verifiedin | 1 | verifiedin | A
| | 6 | NULL | NULL | |
| GECK_STATE | 1 | codedon | 1 | codedon | A
| | 2252 | NULL | NULL | |
| GECK_STATE | 1 | closedon | 1 | closedon | A
| | 2301 | NULL | NULL | |
| GECK_STATE | 1 | codedby | 1 | codedby | A
| | 418 | NULL | NULL | |
| GECK_STATE | 1 | closedby | 1 | closedby | A
| | 492 | NULL | NULL | |
| GECK_STATE | 1 | priority | 1 | priority | A
| | 5 | NULL | NULL | |
| GECK_STATE | 1 | plannedfor | 1 | plannedfor | A
| | 33 | NULL | NULL | |
| GECK_STATE | 1 | date_plannedfor | 1 | date_plannedfor | A
| | 1 | NULL | NULL | |
| GECK_STATE | 1 | date_requiredby | 1 | date_requiredby | A
| | 1 | NULL | NULL | |
+------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+---------+
EXPLAIN SELECT GECK.summary AS Summary, STATUS.status AS Status, GECK.type AS Type,
U1.user AS Owner, GECK.geck_id AS Record
FROM GECK_STATE, USER U1, COMPONENT, STATUS, GECK
WHERE (GECK_STATE.owner = U1.user_id
AND GECK.component_id = COMPONENT.component_id
AND GECK_STATE.status_id = STATUS.status_id
AND GECK.geck_id = GECK_STATE.geck_id )
AND ((COMPONENT.component = 'Web Gecko' AND STATUS.status IN ('Unreviewed', 'Failed
to verify', 'Need more info', 'Under consideration',
'Under investigation', 'Waiting for vendor', 'To do', 'To prequalify', 'To
submit', 'In BaT', 'To verify', 'Add testpoint')) )
ORDER BY STATUS.rank DESC, GECK.type ASC, U1.user ASC, GECK.geck_id ASC;
+------------+--------+-------------------------+--------------+---------+------------------------+------+---------------------------------------------+
| table | type | possible_keys | key | key_len | ref
| | rows | Extra |
+------------+--------+-------------------------+--------------+---------+------------------------+------+---------------------------------------------+
| COMPONENT | ref | PRIMARY,component | component | 32 | const
| | 7 | where used; Using temporary; Using filesort |
| GECK | ref | PRIMARY,component_id | component_id | 2 |
|COMPONENT.component_id | 436 | |
| GECK_STATE | eq_ref | PRIMARY,owner,status_id | PRIMARY | 4 |
|GECK.geck_id | 1 | |
| STATUS | eq_ref | PRIMARY,status | PRIMARY | 1 |
|GECK_STATE.status_id | 1 | where used |
| U1 | eq_ref | PRIMARY | PRIMARY | 2 |
|GECK_STATE.owner | 1 | where used |
+------------+--------+-------------------------+--------------+---------+------------------------+------+---------------------------------------------+
>Fix:
Temporary work-around is not to use a compound index and/or create additional,
separate indexes. I suppose throwing the "safe" switch on mysqld would also work.
>Submitter-Id: michael6218
>Originator:
>Organization:
The MathWorks, Inc., Natick, MA 01760
>MySQL support: extended email support
>Synopsis: Incorrect index selection when potential index is compound
>Severity: serious
>Priority: medium
>Category: mysql
>Class: doc-bug
>Release: mysql-3.23.40 (Source distribution)
>Environment:
System: SunOS komodo 5.7 Generic_106541-15 sun4u sparc SUNW,Ultra-Enterprise
Architecture: sun4
Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gmake
/usr/local/bin/gcc /usr/ucb/cc
GCC: Reading specs from
/hub/SunOS/5.6/sun4u/apps/gcc-2.95.2/lib/gcc-lib/sparc-sun-solaris2.6/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
-rw-r--r-- 1 bin bin 1710308 Feb 21 19:16 /lib/libc.a
lrwxrwxrwx 1 root root 11 Feb 21 18:12 /lib/libc.so -> ./libc.so.1
-rwxr-xr-x 1 bin bin 1124692 Feb 21 19:16 /lib/libc.so.1
-rw-r--r-- 1 bin bin 1710308 Feb 21 19:16 /usr/lib/libc.a
lrwxrwxrwx 1 root root 11 Feb 21 18:12 /usr/lib/libc.so -> ./libc.so.1
-rwxr-xr-x 1 bin bin 1124692 Feb 21 19:16 /usr/lib/libc.so.1
Configure command: ./configure --prefix=/export/local/mysql-3.23.40
Perl: This is perl, version 5.004_04 built for sol2-solaris
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
