strange optimiazer behaviour with JOINed tables.
am 03.02.2003 17:05:22 von gluk>Description:
Optimizer don't use fulltext index with JOINed tables,
(most likely not a bug but strange behaviour)
=09
>How-To-Repeat:
=09
mysql> explain SELECT sp.shop_product_id FROM shop_product sp INNER JOIN=20
product p ON(sp.product_id=3Dp.product_id AND sp.is_enabled AND sp.shop_id=
=3D9)=20
INNER JOIN full_search fs ON(fs.product_id=3Dp.product_id) WHERE=20
match(fs.name,fs.manufacturer,fs.data) against('asdfadsfa');
Return:
+-------+--------+-------------------+---------+---------+-- -------------+-=
=2D-----+-------------+
| table | type | possible_keys | key | key_len | ref |=
=20
rows | Extra |
+-------+--------+-------------------+---------+---------+-- -------------+-=
=2D-----+-------------+
| sp | ref | shop_prod,shop_id | shop_id | 5 | const |=
=20
37422 | Using where |
| p | eq_ref | PRIMARY | PRIMARY | 4 | sp.product_id | =
=20
1 | Using index |
| fs | eq_ref | PRIMARY | PRIMARY | 4 | p.product_id | =
=20
1 | Using where |
+-------+--------+-------------------+---------+---------+-- -------------+-=
=2D-----+-------------+
3 rows in set (0.00 sec)
or:
mysql> explain SELECT sp.shop_product_id FROM shop_product sp INNER JOIN=20
product p ON(sp.product_id=3Dp.product_id AND sp.is_enabled AND sp.shop_id=
=3D9)=20
INNER JOIN full_search fs FORCE INDEX (name_2) ON(fs.product_id=3Dp.product=
_id)=20
WHERE match(fs.name,fs.manufacturer,fs.data) against('asdfadsfa');
+-------+--------+-------------------+-----------+---------+ ---------------=
+--------+-------------+
| table | type | possible_keys | key | key_len | ref =
|=20
rows | Extra |
+-------+--------+-------------------+-----------+---------+ ---------------=
+--------+-------------+
| fs | ALL | NULL | NULL | NULL | NULL =
|=20
233176 | Using where |
| p | eq_ref | PRIMARY | PRIMARY | 4 | fs.product_id =
| =20
1 | Using index |
| sp | ref | shop_prod,shop_id | shop_prod | 5 | p.product_id =
| =20
1 | Using where |
+-------+--------+-------------------+-----------+---------+ ---------------=
+--------+-------------+
3 rows in set (0.01 sec)
while:
mysql> explain SELECT * FROM full_search fs WHERE=20
match(fs.name,fs.manufacturer,fs.data) against ('asfmasdjv');
=09
+-------+----------+---------------+--------+---------+----- -+------+------=
=2D------+
| table | type | possible_keys | key | key_len | ref | rows | Extr=
a =20
|
+-------+----------+---------------+--------+---------+----- -+------+------=
=2D------+
| fs | fulltext | name_2 | name_2 | 0 | | 1 | Usin=
g=20
where |
+-------+----------+---------------+--------+---------+----- -+------+------=
=2D------+
1 row in set (0.00 sec)
Some вdditional information:
mysql> show create table shop_product;
| Table | Create Table |=20
| shop_product |CREATE TABLE `shop_product` (
`shop_product_id` int(11) NOT NULL auto_increment,
`catrelation_id` int(11) default NULL,
`shop_id` int(11) default NULL,
`product_id` int(11) default NULL,
`name` varchar(255) NOT NULL default '',
`vars`=20
set('is_enabled','is_hot','is_new','is_gift','recomend','sus pend','is_singl=
e','suspicious','banner','is_garanty','is_top','is_sale','li mited','notnull=
','is_fast','is_nodiscount','is_soon','not_in_price')=20
NOT NULL default 'notnull',
`is_hot` int(1) NOT NULL default '0',
`is_new` int(1) NOT NULL default '0',
`is_garanty` tinyint(1) NOT NULL default '0',
`price` float(10,4) NOT NULL default '0.0000',
`emx_update` int(8) NOT NULL default '7',
`emx_stake` int(11) NOT NULL default '0',
`emx_stake_cost` int(11) NOT NULL default '0',
`currency_id` int(11) NOT NULL default '1',
`delivery` smallint(6) NOT NULL default '0',
`price_diff` enum('-1','1','0') NOT NULL default '0',
`supplier_id` int(11) NOT NULL default '0',
`set_tax` int(11) default NULL,
`discount_id` int(11) NOT NULL default '0',
`rep_db` tinyint(4) NOT NULL default '1',
`rep_mod` timestamp(14) NOT NULL,
`is_sale` int(1) NOT NULL default '0',
`is_top` int(1) NOT NULL default '0',
`is_enabled` int(1) NOT NULL default '0',
`is_fast` tinyint(1) NOT NULL default '0',
`is_compare` int(11) NOT NULL default '0',
`min_cnt` int(11) default NULL,
`avail_cnt` int(11) NOT NULL default '0',
`old_price` decimal(10,4) default NULL,
`comment` varchar(255) default NULL,
`shop_update` int(11) NOT NULL default '0',
`desc_flag` int(1) NOT NULL default '0',
PRIMARY KEY (`shop_product_id`),
UNIQUE KEY `shop_prod` (`product_id`,`catrelation_id`),
KEY `shop_id` (`shop_id`),
KEY `price` (`price`),
KEY `rep_db` (`rep_db`,`rep_mod`),
KEY `emx_update` (`emx_update`),
KEY `catrelation_id_2` (`catrelation_id`,`is_enabled`)
) TYPE=3DInnoDB |
mysql> show create table product;
| Table | Create Table |
| product |CREATE TABLE `product` (
`product_id` int(11) NOT NULL auto_increment,
`code` varchar(20) default NULL,
`manufacturer_id` int(11) NOT NULL default '0',
`sname` varchar(250) NOT NULL default '',
`name` varchar(250) default NULL,
`measure` varchar(10) default NULL,
`weight` float default NULL,
`recommended_price` float(10,4) NOT NULL default '0.0000',
`mancode` varchar(20) default NULL,
`mb_pid` int(11) default NULL,
`em_pid` int(11) default '0',
`desc_flag` int(1) NOT NULL default '0',
`image_ext` varchar(255) default NULL,
`img_s` varchar(50) NOT NULL default '',
`avg_price` float(10,2) default NULL,
`product_type_id` int(11) NOT NULL default '0',
`bolero_pid` int(11) NOT NULL default '0',
`country_id` int(11) NOT NULL default '0',
`rep_db` tinyint(4) NOT NULL default '1',
`rep_mod` timestamp(14) NOT NULL,
`emx_update` int(8) NOT NULL default '0',
`authorized_delivery` enum('0','1','2') NOT NULL default '0',
`disabled_payment_ids` varchar(200) NOT NULL default '',
`disabled_delivery_type_ids` varchar(200) NOT NULL default '',
`is_comparable` tinyint(1) NOT NULL default '0',
`img_75x225` varchar(50) default NULL,
PRIMARY KEY (`product_id`),
KEY `code` (`code`),
KEY `manufacturer_id` (`manufacturer_id`),
KEY `name` (`name`),
KEY `em_pid` (`em_pid`),
KEY `mb_pid` (`mb_pid`),
KEY `mancode` (`mancode`),
KEY `desc_flag` (`desc_flag`),
KEY `bolero_pid` (`bolero_pid`),
KEY `rep_db` (`rep_db`,`rep_mod`),
KEY `product_type_id` (`product_type_id`),
FULLTEXT KEY `name_2` (`name`)
) TYPE=3DMyISAM |
mysql> show create table full_search;
| Table | Create Table
| full_search | CREATE TABLE `full_search` (
`rep_db` tinyint(4) NOT NULL default '1',
`rep_mod` timestamp(14) NOT NULL,
`product_id` int(11) NOT NULL default '0',
`data` text,
`xtime` datetime default NULL,
`name` text,
`manufacturer` varchar(255) default NULL,
PRIMARY KEY (`product_id`),
KEY `rep_db` (`rep_db`,`rep_mod`),
FULLTEXT KEY `name_2` (`name`,`manufacturer`,`data`)
) TYPE=3DMyISAM |
>Fix:
>Originator: gluk@php4.ru=09
>Organization:
eHouse
>MySQL support: none=20
>Synopsis: strange optimiazer behaviour with JOINed tables
>Severity: non-critical
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.10-gamma (Source distribution)
>C compiler: gcc (GCC) 3.2.1
>C++ compiler: gcc (GCC) 3.2.1
>Environment:
System: Linux db.lamport.msk.ru 2.4.20-rc1aa1 #1 þÃà î=
ÃÃ 14 22:31:09 MSK 2002=20
i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc=20
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.2 2.96-112.7.1)
Compilation info: CC=3D'/usr/local/gcc/bin/gcc' CFLAGS=3D'-O4 -march=3Dpen=
tium3=20
=2Dmcpu=3Dpentium3 -msse -fomit-frame-pointer -pipe' =20
CXX=3D'/usr/local/gcc/bin/gcc' CXXFLAGS=3D'-O4 -march=3Dpentium3 -mcpu=3Dp=
entium3=20
=2Dmsse -fomit-frame-pointer -pipe -felide-constructors -fno-exceptions=20
=2Dfno-rtti -DUSE_MYSYS_NEW' LDFLAGS=3D'' ASFLAGS=3D''
LIBC:=20
lrwxrwxrwx 1 root root 13 Nov 10 19:47 /lib/libc.so.6 ->=20
libc-2.2.4.so
=2Drwxr-xr-x 1 root root 1285884 Oct 10 21:19 /lib/libc-2.2.4.so
=2Drw-r--r-- 1 root root 27338282 Oct 10 20:48 /usr/lib/libc.a
=2Drw-r--r-- 1 root root 178 Oct 10 20:48 /usr/lib/libc.so
lrwxrwxrwx 1 root root 10 May 26 2002 /usr/lib/libc-clien=
t.a=20
=2D> c-client.a
Configure command: ./configure --build=3Di686-pc-linux-gnu --prefix=3D/usr=
=20
=2D-libexecdir=3D/usr/sbin --localstatedir=3D/var/lib/mysql --mandir=3D/usr=
/share/man=20
=2D-infodir=3D/usr/share/info --enable-assembler=20
=2D-with-mysqld-ldflags=3D-all-static --with-mysql-user=3Dmysql --with-inno=
db=20
=2D-with-unix-socket-path=3D/var/lib/mysql/mysql.sock=20
=2D-with-extra-charsets=3Dlatin1,koi8_ru,cp1251 --enable-thread-safe-client=
=20
CC=3D/usr/local/gcc/bin/gcc 'CFLAGS=3D-O4 -march=3Dpentium3 -mcpu=3Dpentium=
3 -msse=20
=2Dfomit-frame-pointer -pipe' 'CXXFLAGS=3D-O4 -march=3Dpentium3 -mcpu=3Dpen=
tium3=20
=2Dmsse -fomit-frame-pointer -pipe -felide-constructors -fno-exceptions=20
=2Dfno-rtti -DUSE_MYSYS_NEW' CXX=3D/usr/local/gcc/bin/gcc=20
build_alias=3Di686-pc-linux-gnu
=20
=2D-=20
Best regards.
Alexander Y. Fomichev
Public PGP key: http://sysadminday.org.ru/gluk.asc
------------------------------------------------------------ ---------
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 bugs-thread13657@lists.mysql.com
To unsubscribe, e-mail