Join Query Taking Too Long
Posted by: Aaron B
Date: April 05, 2011 08:16AM
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 | |
+-----------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
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 | |
+-----------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Subject
Views
Written By
Posted
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.