Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.