joins and nested where clauses.
>Description:
The query analyzer seems to hang while trying to parse/optimize this
particular query if there is data in the table. Trying to understand why
mysql locked up running a complex query, We have been surprised to
find that using EXPLAIN could hang it as well, even with freshly rebuilt
and analyzed indexes. If there is no data, EXPLAIN fails saying "Impossible
WHERE notices after reading const tables", as there is nothing there. I can
provide sample data on request.
>How-To-Repeat:
A- Create a database with the following tables (not all are required):
-- MySQL dump 8.21
--
-- Host: localhost Database: tkidb06
---------------------------------------------------------
-- Server version 3.23.49-log
--
-- Table structure for table 'ascii_term'
--
CREATE TABLE ascii_term (
ascii varchar(64) NOT NULL default '',
utf8 varchar(64) NOT NULL default '',
PRIMARY KEY (ascii,utf8)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'date_f'
--
CREATE TABLE date_f (
id int(10) unsigned NOT NULL auto_increment,
element varchar(64) NOT NULL default '',
vq varchar(8) NOT NULL default '',
lang char(2) NOT NULL default '',
dialect char(2) NOT NULL default '',
value date NOT NULL default '0000-00-00',
UNIQUE KEY id (id),
PRIMARY KEY (value,element,lang,vq)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'date_l'
--
CREATE TABLE date_l (
feature int(10) unsigned NOT NULL default '0',
res int(10) unsigned NOT NULL default '0',
PRIMARY KEY (feature,res),
KEY res (res)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'id_f'
--
CREATE TABLE id_f (
id int(10) unsigned NOT NULL auto_increment,
element varchar(64) NOT NULL default '',
vq varchar(8) NOT NULL default '',
lang char(2) NOT NULL default '',
dialect char(2) NOT NULL default '',
value varchar(128) NOT NULL default '',
UNIQUE KEY id (id),
PRIMARY KEY (value,element,lang,vq)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'id_l'
--
CREATE TABLE id_l (
feature int(10) unsigned NOT NULL default '0',
res int(10) unsigned NOT NULL default '0',
PRIMARY KEY (feature,res),
KEY res (res)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'kete'
--
CREATE TABLE kete (
kete_id int(11) unsigned NOT NULL auto_increment,
kete_name varchar(32) NOT NULL default '',
kete_description text,
date_created date NOT NULL default '0000-00-00',
date_modified date NOT NULL default '0000-00-00',
PRIMARY KEY (kete_id)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'kete_res'
--
CREATE TABLE kete_res (
id int(11) unsigned NOT NULL auto_increment,
kete_id int(11) unsigned NOT NULL default '0',
md_id int(11) unsigned NOT NULL default '0',
type varchar(32) NOT NULL default 'none',
identifier varchar(254) NOT NULL default '',
title varchar(254) default NULL,
description text,
sequence int(11) default NULL,
date_created date NOT NULL default '0000-00-00',
date_modified date NOT NULL default '0000-00-00',
PRIMARY KEY (id),
KEY kete (kete_id)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'md_user_priv'
--
CREATE TABLE md_user_priv (
user_id varchar(255) NOT NULL default '',
create_md char(1) NOT NULL default 'n',
edit_md char(1) NOT NULL default 'n',
delete_md char(1) NOT NULL default 'n',
manage_md char(1) NOT NULL default 'n',
signoff_md char(1) NOT NULL default 'n',
view_md char(1) NOT NULL default 'n',
supervisor char(1) NOT NULL default 'n',
se_re_md char(1) NOT NULL default 'n',
import_md char(1) NOT NULL default 'n',
copy_stag char(1) NOT NULL default 'n',
copy_prod char(1) NOT NULL default 'n',
develop char(1) NOT NULL default 'n',
qa_md char(1) NOT NULL default 'n',
PRIMARY KEY (user_id)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'res_list'
--
CREATE TABLE res_list (
id int(10) unsigned NOT NULL auto_increment,
created timestamp(14) NOT NULL,
PRIMARY KEY (id),
KEY created (created)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'res_list_l'
--
CREATE TABLE res_list_l (
res_list int(10) unsigned NOT NULL default '0',
res int(10) unsigned NOT NULL default '0',
PRIMARY KEY (res_list,res),
KEY res (res)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'resource'
--
CREATE TABLE resource (
id int(10) unsigned NOT NULL auto_increment,
identifier varchar(64) NOT NULL default '',
PRIMARY KEY (id),
KEY identifier (identifier)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'session'
--
CREATE TABLE session (
session_id varchar(32) NOT NULL default '0',
user_id varchar(255) NOT NULL default '0',
date_created date NOT NULL default '0000-00-00',
date_modified date NOT NULL default '0000-00-00',
PRIMARY KEY (session_id)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'stem_f'
--
CREATE TABLE stem_f (
id int(10) unsigned NOT NULL auto_increment,
element varchar(64) NOT NULL default '',
vq varchar(8) NOT NULL default '',
lang char(2) NOT NULL default '',
dialect char(2) NOT NULL default '',
value varchar(16) NOT NULL default '',
PRIMARY KEY (value,element,lang,vq),
UNIQUE KEY id (id)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'stem_l'
--
CREATE TABLE stem_l (
feature int(10) unsigned NOT NULL default '0',
res int(10) unsigned NOT NULL default '0',
PRIMARY KEY (feature,res),
KEY res (res),
KEY feature (feature)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'term_f'
--
CREATE TABLE term_f (
id int(10) unsigned NOT NULL auto_increment,
element varchar(64) NOT NULL default '',
vq varchar(8) NOT NULL default '',
lang char(2) NOT NULL default '',
dialect char(2) NOT NULL default '',
value varchar(64) NOT NULL default '',
PRIMARY KEY (value,element,lang,vq),
UNIQUE KEY id (id)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'term_l'
--
CREATE TABLE term_l (
feature int(10) unsigned NOT NULL default '0',
res int(10) unsigned NOT NULL default '0',
PRIMARY KEY (feature,res),
KEY res (res),
KEY feature (feature)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'text_f'
--
CREATE TABLE text_f (
id int(10) unsigned NOT NULL auto_increment,
element varchar(64) NOT NULL default '',
vq varchar(8) NOT NULL default '',
lang char(2) NOT NULL default '',
dialect char(2) NOT NULL default '',
value text NOT NULL,
PRIMARY KEY (id),
KEY element (element,lang,vq)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'text_l'
--
CREATE TABLE text_l (
feature int(10) unsigned NOT NULL default '0',
res int(10) unsigned NOT NULL default '0',
PRIMARY KEY (feature,res),
KEY res (res)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'tki_user'
--
CREATE TABLE tki_user (
user_id varchar(32) NOT NULL default '',
email varchar(48) default '',
first_name varchar(32) default '',
last_name varchar(32) default '',
org_name varchar(32) default '',
date_created date NOT NULL default '0000-00-00',
date_modified date NOT NULL default '0000-00-00',
password varchar(255) default NULL,
public char(1) default 'Y',
PRIMARY KEY (user_id)
) TYPE=ISAM PACK_KEYS=1;
--
-- Table structure for table 'user_kete'
--
CREATE TABLE user_kete (
id int(11) unsigned NOT NULL auto_increment,
user_id varchar(32) NOT NULL default '',
kete_id int(11) unsigned NOT NULL default '0',
kete_name varchar(32) NOT NULL default '',
permission char(1) NOT NULL default 'm',
date_created date NOT NULL default '0000-00-00',
date_modified date NOT NULL default '0000-00-00',
PRIMARY KEY (id)
) TYPE=ISAM PACK_KEYS=1;
B- EXPLAIN this query
SELECT DISTINCT 4278257786, resource.id
FROM term_f as term_0_0, term_l as terml_0_0, resource, stem_f as stem_1_0,
stem_f as stem_1_1, stem_f as stem_1_2, stem_f as stem_1_3, stem_f as
stem_1_4, stem_f as stem_1_5, stem_f as stem_1_6, stem_l as steml_1_0,
stem_l as steml_1_1, stem_l as steml_1_2, stem_l as steml_1_3, stem_l as
steml_1_4, stem_l as steml_1_5, stem_l as steml_1_6
WHERE
(
(
(
term_0_0.value = 'English'
and term_0_0.element in ('DC.Subject.Classification')
and terml_0_0.feature = term_0_0.id
and terml_0_0.res = resource.id
)
)
AND
(
(
stem_1_0.value = 'exemplar'
and stem_1_0.element in
('DC.Description','DC.Title','DC.Subject.Keyword','DC.Subject.Classification','TKI.SupplementaryTerm')
and steml_1_0.feature = stem_1_0.id
and steml_1_0.res = resource.id
)
AND
(
stem_1_1.value = 'languag'
and stem_1_1.element in
('DC.Description','DC.Title','DC.Subject.Keyword','DC.Subject.Classification','TKI.SupplementaryTerm')
and steml_1_1.feature = stem_1_1.id
and steml_1_1.res = resource.id
)
AND
(
stem_1_2.value = 'written'
and stem_1_2.element in
('DC.Description','DC.Title','DC.Subject.Keyword','DC.Subject.Classification','TKI.SupplementaryTerm')
and steml_1_2.feature = stem_1_2.id
and steml_1_2.res = resource.id
)
AND
(
stem_1_3.value = 'english'
and stem_1_3.element in
('DC.Description','DC.Title','DC.Subject.Keyword','DC.Subject.Classification','TKI.SupplementaryTerm')
and steml_1_3.feature = stem_1_3.id
and steml_1_3.res = resource.id
)
AND
(
stem_1_4.value = 'nation'
and stem_1_4.element in
('DC.Description','DC.Title','DC.Subject.Keyword','DC.Subject.Classification','TKI.SupplementaryTerm')
and steml_1_4.feature = stem_1_4.id
and steml_1_4.res = resource.id
)
AND
(
stem_1_5.value = 'trial'
and stem_1_5.element in
('DC.Description','DC.Title','DC.Subject.Keyword','DC.Subject.Classification','TKI.SupplementaryTerm')
and steml_1_5.feature = stem_1_5.id
and steml_1_5.res = resource.id
)
AND
(
stem_1_6.value = 'level'
and stem_1_6.element in
('DC.Description','DC.Title','DC.Subject.Keyword','DC.Subject.Classification','TKI.SupplementaryTerm')
and steml_1_6.feature = stem_1_6.id
and steml_1_6.res = resource.id
)
)
)
ORDER BY resource.id
Experimenting, I have found that removing the two last WHERE clauses, the
ones referring to stem_1_5 and stem_1_6, ANALYZE works again. Hmmmm.
>Fix:
I wish I knew!
>Submitter-Id: [EMAIL PROTECTED]
>Originator: martin langhoff
>Organization:
CWA New Media
>MySQL support: none
>Synopsis: Query analizer hangs on complex query.
>Severity: serious
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.49 (Source distribution)
>Environment:
System: Linux uniform 2.2.20 #1 Sat Apr 20 11:45:28 EST 2002 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/i386-linux/2.95.4/specs
gcc version 2.95.4 20011002 (Debian prerelease)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Oct 22 15:30 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x 1 root root 1153784 Sep 18 21:40 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2390970 Sep 18 21:41 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Sep 18 21:41 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr --exec-prefix=/usr
--libexecdir=/usr/sbin --datadir=/usr/share --sysconfdir=/etc/mysql
--localstatedir=/var/lib/mysql --includedir=/usr/include --infodir=/usr/share/info
--mandir=/usr/share/man --enable-shared --with-libwrap --enable-assembler
--with-berkeley-db --with-innodb --enable-static --enable-shared --enable-local-infile
--with-raid --enable-thread-safe-client --without-readline
--with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql
--without-bench --with-client-ldflags=-lstdc++ --with-extra-charsets=all
---------------------------------------------------------------------
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
