Optimizer don't use fulltext index with JOINed tables, (most likely not a bug but strange behaviour)

mysql> explain SELECT sp.shop_product_id FROM shop_product sp INNER JOIN product p ON(sp.product_id=p.product_id AND sp.is_enabled AND sp.shop_id=9) INNER JOIN full_search fs ON(fs.product_id=p.product_id) WHERE match(fs.name,fs.manufacturer,fs.data) against('asdfadsfa'); Return: +-------+--------+-------------------+---------+---------+---------------+-------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+-------------------+---------+---------+---------------+-------+-------------+ | sp | ref | shop_prod,shop_id | shop_id | 5 | const | 37422 | Using where | | p | eq_ref | PRIMARY | PRIMARY | 4 | sp.product_id | 1 | Using index | | fs | eq_ref | PRIMARY | PRIMARY | 4 | p.product_id | 1 | Using where | +-------+--------+-------------------+---------+---------+---------------+-------+-------------+ 3 rows in set (0.00 sec)

or: mysql> explain SELECT sp.shop_product_id FROM shop_product sp INNER JOIN product p ON(sp.product_id=p.product_id AND sp.is_enabled AND sp.shop_id=9) INNER JOIN full_search fs FORCE INDEX (name_2) ON(fs.product_id=p.product_id) WHERE match(fs.name,fs.manufacturer,fs.data) against('asdfadsfa'); +-------+--------+-------------------+-----------+---------+---------------+--------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+-------------------+-----------+---------+---------------+--------+-------------+ | fs | ALL | NULL | NULL | NULL | NULL | 233176 | Using where | | p | eq_ref | PRIMARY | PRIMARY | 4 | fs.product_id | 1 | Using index | | sp | ref | shop_prod,shop_id | shop_prod | 5 | p.product_id | 1 | Using where | +-------+--------+-------------------+-----------+---------+---------------+--------+-------------+ 3 rows in set (0.01 sec)

while: mysql> explain SELECT * FROM full_search fs WHERE match(fs.name,fs.manufacturer,fs.data) against ('asfmasdjv');

+-------+----------+---------------+--------+---------+------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+----------+---------------+--------+---------+------+------+-------------+ | fs | fulltext | name_2 | name_2 | 0 | | 1 | Using where | +-------+----------+---------------+--------+---------+------+------+-------------+ 1 row in set (0.00 sec)

Some вdditional information: mysql> show create table shop_product; | Table | Create Table | | 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` set('is_enabled','is_hot','is_new','is_gift','recomend','suspend','is_single','suspicious','banner','is_garanty','is_top','is_sale','limited','notnull','is_fast','is_nodiscount','is_soon','not_in_price') 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=InnoDB |

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=MyISAM |

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=MyISAM |

eHouse

<machine, os, target, libraries (multiple lines)> System: Linux db.lamport.msk.ru 2.4.20-rc1aa1 #1 þÔ× îÏÑ 14 22:31:09 MSK 2002 i686 unknown Architecture: i686

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /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='/usr/local/gcc/bin/gcc' CFLAGS='-O4 -march=pentium3 -mcpu=pentium3 -msse -fomit-frame-pointer -pipe' CXX='/usr/local/gcc/bin/gcc' CXXFLAGS='-O4 -march=pentium3 -mcpu=pentium3 -msse -fomit-frame-pointer -pipe -felide-constructors -fno-exceptions -fno-rtti -DUSE_MYSYS_NEW' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Nov 10 19:47 /lib/libc.so.6 -> libc-2.2.4.so -rwxr-xr-x 1 root root 1285884 Oct 10 21:19 /lib/libc-2.2.4.so -rw-r--r-- 1 root root 27338282 Oct 10 20:48 /usr/lib/libc.a -rw-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-client.a -> c-client.a Configure command: ./configure --build=i686-pc-linux-gnu --prefix=/usr --libexecdir=/usr/sbin --localstatedir=/var/lib/mysql --mandir=/usr/share/man --infodir=/usr/share/info --enable-assembler --with-mysqld-ldflags=-all-static --with-mysql-user=mysql --with-innodb --with-unix-socket-path=/var/lib/mysql/mysql.sock --with-extra-charsets=latin1,koi8_ru,cp1251 --enable-thread-safe-client CC=/usr/local/gcc/bin/gcc 'CFLAGS=-O4 -march=pentium3 -mcpu=pentium3 -msse -fomit-frame-pointer -pipe' 'CXXFLAGS=-O4 -march=pentium3 -mcpu=pentium3 -msse -fomit-frame-pointer -pipe -felide-constructors -fno-exceptions -fno-rtti -DUSE_MYSYS_NEW' CXX=/usr/local/gcc/bin/gcc build_alias=i686-pc-linux-gnu