Goal
joining the two tables: tbl_quickSearchParam (fulltext search results) against tbl_product ( active products : prod_status = 1, group by prod_image_name)
create table tbl_product (
`prod_id` mediumint unsigned not null primary key auto_increment,
`prod_image_name` varchar(30) not null,
`prod_status` tinyint(1) not null,
KEY `status_image` (`prod_status`,`prod_image_name`),
KEY `image` (`prod_image_name`)
)engine=innodb;
create table tbl_quickSearchParams (
`transID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`prod_id` mediumint(9) NOT NULL,
`parameters` text COLLATE utf8_unicode_ci NOT NULL,
`date_added` datetime NOT NULL,
PRIMARY KEY (`transID`),
UNIQUE KEY `prod_id` (`prod_id`),
FULLTEXT KEY `parameters` (`parameters`)
) engine = myisam;
first query
--------------------------------------------------------------------------------
explain select prod_id from (select t1.prod_id from tbl_quickSearchParams t1 inner join tbl_product using(prod_id) where match(parameters) against('wild tribe')) as t1 inner join tbl_product t2 using(prod_id)
where t2.prod_status = 1group by prod_image_name;
+----+-------------+-------------+----------+--------------------+------------+---------+------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+----------+--------------------+------------+---------+------------------------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2490 | Using temporary; Using filesort |
| 1 | PRIMARY | t2 | eq_ref | PRIMARY | PRIMARY | 3 | t1.prod_id | 1 | Using where |
| 2 | DERIVED | t1 | fulltext | prod_id,parameters | parameters | 0 | | 1 | Using where |
| 2 | DERIVED | tbl_product | eq_ref | PRIMARY | PRIMARY | 3 | salesdev_bv.t1.prod_id | 1 | Using where; Using index |
+----+-------------+-------------+----------+--------------------+------------+---------+------------------------+------+---------------------------------+
its kinda got optimization issue ( file sort )
second query
--------------------------------------------------------------------------------
explain select t1.prod_id from (select prod_id from tbl_product where prod_status=1 group by prod_image_name) t1 inner join tbl_quickSearchParams t2 using(prod_id) where match(parameters) against('wild tribe');
+----+-------------+-------------+----------+--------------------+--------------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+----------+--------------------+--------------+---------+------+-------+--------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 8121 | |
| 1 | PRIMARY | t2 | fulltext | prod_id,parameters | parameters | 0 | | 1 | Using where |
| 2 | DERIVED | tbl_product | ref | status_image | status_image | 1 | | 14106 | Using where; Using index |
+----+-------------+-------------+----------+--------------------+--------------+---------+------+-------+--------------------------+
does this mean 14106 * 8121 rows found?
third query
--------------------------------------------------------------------------------
explain select t1.prod_id from tbl_product t1 inner join tbl_quickSearchParams t2 using(prod_id) where match(parameters) against('wild tribe') and t1.prod_status=1 group by t1.prod_image_name;
+----+-------------+-------+----------+----------------------+------------+---------+------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+----------+----------------------+------------+---------+------------------------+------+----------------------------------------------+
| 1 | SIMPLE | t2 | fulltext | prod_id,parameters | parameters | 0 | | 1 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY,status_image | PRIMARY | 3 | salesdev_bv.t2.prod_id | 1 | Using where |
+----+-------------+-------+----------+----------------------+------------+---------+------------------------+------+----------------------------------------------+
it got some performance issue ( temporary + filesort)
Questions:
- which of these query is better?
- is there way to improve the goal query?
