MySQL Forums
Forum List  »  Full-Text Search

Join Query Taking Too Long
Posted by: Aaron B
Date: April 05, 2011 08:16AM

Hello,

My query is taking a long time (10+ seconds) and I want to find a way to speed it up.

SELECT p.productid FROM product p
JOIN mcc_product_import mpi ON (mpi.mfgpartno = p.mfgpartno)
JOIN productkeywords pk ON (pk.productid = p.productid)
WHERE pk.keywords LIKE '%wrt54%'

+----+-------------+-------+-------+---------------------------+---------------------------+---------+-----------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------------+---------------------------+---------+-----------------------+--------+-------------+
| 1 | SIMPLE | mpi | index | product_mfgpartno | product_mfgpartno | 72 | NULL | 480032 | Using index |
| 1 | SIMPLE | p | ref | PRIMARY,product_mfgpartno | product_mfgpartno | 72 | etilize.mpi.mfgpartno | 1 | |
| 1 | SIMPLE | pk | ref | productkeywords_productID | productkeywords_productID | 4 | etilize.p.productid | 2 | Using where |
+----+-------------+-------+-------+---------------------------+---------------------------+---------+-----------------------+--------+-------------+


mysql> describe mcc_product_import;
+----------------+--------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------------------+-----------------------------+
| mfgpartno | varchar(70) | NO | MUL | NULL | |
| manufacturer | varchar(60) | NO | MUL | NULL | |
| manufacturerid | int(11) | NO | MUL | NULL | |
| categoryid | int(11) | NO | | 0 | |
| vendorpartno | varchar(25) | NO | PRI | NULL | |
| vendor | varchar(25) | NO | PRI | NULL | |
| description2 | varchar(100) | NO | | NULL | |
| description3 | varchar(100) | NO | | NULL | |
| keywords | mediumtext | NO | MUL | NULL | |
| price | float(10,2) | NO | MUL | NULL | |
| regular_price | float(10,2) | NO | | NULL | |
| msrp | float(10,2) | NO | | NULL | |
| stock | varchar(5) | NO | | NULL | |
| isactive | tinyint(1) | NO | | 0 | |
| special | varchar(1) | NO | | NULL | |
| refurbished | varchar(1) | NO | | NULL | |
| custom | tinyint(1) | NO | PRI | NULL | |
| creationdate | timestamp | NO | | 0000-00-00 00:00:00 | |
| modifieddate | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| lastupdated | timestamp | NO | | 0000-00-00 00:00:00 | |
+----------------+--------------+------+-----+---------------------+-----------------------------+
mysql> SHOW KEYS FROM mcc_product_import;
+--------------------+------------+---------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------+------------+---------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| mcc_product_import | 0 | vendorpartno_vendor | 1 | vendorpartno | A | 480032 | NULL | NULL | | BTREE | |
| mcc_product_import | 0 | vendorpartno_vendor | 2 | vendor | A | 480032 | NULL | NULL | | BTREE | |
| mcc_product_import | 0 | vendorpartno_vendor | 3 | custom | A | 480032 | NULL | NULL | | BTREE | |
| mcc_product_import | 1 | price | 1 | price | A | 120008 | NULL | NULL | | BTREE | |
| mcc_product_import | 1 | manufacturer | 1 | manufacturer | A | 1124 | NULL | NULL | | BTREE | |
| mcc_product_import | 1 | keywords | 1 | keywords | A | 480032 | 255 | NULL | | BTREE | |
| mcc_product_import | 1 | manufacturerid | 1 | manufacturerid | A | 406 | NULL | NULL | | BTREE | |
| mcc_product_import | 1 | product_mfgpartno | 1 | mfgpartno | A | 480032 | NULL | NULL | | BTREE | |
+--------------------+------------+---------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+



mysql> describe product;
+----------------+-------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------------------+-----------------------------+
| productid | int(11) | NO | PRI | 0 | |
| manufacturerid | int(11) | NO | MUL | 0 | |
| isactive | tinyint(1) | NO | | 1 | |
| mfgpartno | varchar(70) | NO | MUL | | |
| categoryid | int(11) | NO | MUL | 0 | |
| isaccessory | tinyint(1) | NO | MUL | 0 | |
| equivalency | double | NO | | 0 | |
| creationdate | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| modifieddate | timestamp | NO | | 0000-00-00 00:00:00 | |
| lastupdated | timestamp | NO | | 0000-00-00 00:00:00 | |
+----------------+-------------+------+-----+---------------------+-----------------------------+
mysql> SHOW KEYS FROM product;
+---------+------------+------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| product | 0 | PRIMARY | 1 | productid | A | 2062167 | NULL | NULL | | BTREE | |
| product | 1 | product_isAccessory | 1 | isaccessory | A | 1 | NULL | NULL | | BTREE | |
| product | 1 | product_manufacturerID | 1 | manufacturerid | A | 4740 | NULL | NULL | | BTREE | |
| product | 1 | product_categoryID | 1 | categoryid | A | 772 | NULL | NULL | | BTREE | |
| product | 1 | product_mfgpartno | 1 | mfgpartno | A | 2062167 | NULL | NULL | | BTREE | |
+---------+------------+------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+

mysql> describe productkeywords;
+-----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| productid | int(11) | NO | MUL | 0 | |
| keywords | mediumtext | NO | MUL | NULL | |
| localeid | int(11) | NO | MUL | 0 | |
+-----------+------------+------+-----+---------+-------+
mysql> SHOW KEYS FROM productkeywords;
+-----------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| productkeywords | 1 | productkeywords_productID | 1 | productid | A | 1728271 | NULL | NULL | | BTREE | |
| productkeywords | 1 | productkeywords_localeID | 1 | localeid | A | 3 | NULL | NULL | | BTREE | |
| productkeywords | 1 | productkeywords_keywords | 1 | keywords | A | 3456543 | 255 | NULL | | BTREE | |
| productkeywords | 1 | productkeywords_keywords_ft | 1 | keywords | NULL | 3456543 | NULL | NULL | | FULLTEXT | |
+-----------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Join Query Taking Too Long
3480
April 05, 2011 08:16AM
1681
April 09, 2011 12:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.