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
add comment

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.