Update: so, as mentioned in the comments thread, it seems the ORDER BY might be the most direct cause of slow execution. So, the question should be: how can this query use ORDER BY score and execute quickly?
I've been unable to optimize this MySQL query satisfactorily. It takes about 1.2 seconds to execute, which is surprising to me because it is a simple join with not a huge quantity of records. ? If I remove the GROUP BY line or the conditions matching on columns in the second table of the join (the ones with OR … LIKE …), performance is improved sigificantly, which tell me there is a problem with the way I'm JOINing the tables, or its indexes. See experiments with EXPLAIN SELECT results below.
I appreciate any suggestions for making this run faster.
In the query below, codeine is a stand-in for any user-entered search terms. They may enter the name of a phamaeutical drug, an ingredient, or an NDC number.
SELECT
`product_ndc`,
CONCAT(`brand_name`, ' (', `generic_name`, ')') AS `name`,
`dosage_form`,
`dea_schedule`,
`labeler_name`,
`ingredients`,
MATCH (`brand_name`, `generic_name`, `labeler_name`, `ingredients`) AGAINST ('codeine' IN BOOLEAN MODE) AS `score`
FROM product_tbl
LEFT JOIN package_tbl ON (`product_tbl`.`id` = `package_tbl`.`id`)
WHERE MATCH (`brand_name`, `generic_name`, `labeler_name`, `ingredients`) AGAINST ('codeine' IN BOOLEAN MODE)
OR `package_ndc` LIKE 'codeine%'
OR `package_ndc_11dig` LIKE 'codeine%'
OR `fuzzed_package_ndc` LIKE 'codeine%'
OR `fuzzed_package_ndc_11dig` LIKE 'codeine%'
GROUP BY `product_tbl`.`id`
ORDER BY `score` DESC
LIMIT 25;
Here's the EXPLAIN output from this query:
+----+-------------+-------------+------------+-------+------------------------------------------------------------------------------------------------------+---------+---------+--------------------+--------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+------------------------------------------------------------------------------------------------------+---------+---------+--------------------+--------+----------+---------------------------------+
| 1 | SIMPLE | product_tbl | NULL | index | PRIMARY,product_ndc,brand_name,generic_name,dosage_form,dea_schedule,labeler_name,ingredients,ft_all | PRIMARY | 3 | NULL | 102739 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | package_tbl | NULL | ref | id | id | 4 | fwr.product_tbl.id | 1 | 100.00 | Using where |
+----+-------------+-------------+------------+-------+------------------------------------------------------------------------------------------------------+---------+---------+--------------------+--------+----------+---------------------------------+
SHOW CREATE TABLE product_tbl (contains 111,502 records)
CREATE TABLE `product_tbl` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`product_id` varchar(48) NOT NULL,
`product_ndc` varchar(10) NOT NULL,
`spl_id` varchar(36) NOT NULL,
`rxcui` varchar(8) NOT NULL,
`brand_name` varchar(255) NOT NULL,
`generic_name` varchar(520) NOT NULL,
`dosage_form` varchar(255) NOT NULL,
`dea_schedule` varchar(3) NOT NULL,
`labeler_name` varchar(255) NOT NULL,
`is_original_packager` tinyint(1) NOT NULL,
`finished` tinyint(1) NOT NULL,
`ingredients` text NOT NULL,
PRIMARY KEY (`id`),
KEY `product_ndc` (`product_ndc`),
KEY `brand_name` (`brand_name`),
KEY `generic_name` (`generic_name`),
KEY `dosage_form` (`dosage_form`),
KEY `dea_schedule` (`dea_schedule`),
KEY `labeler_name` (`labeler_name`),
FULLTEXT KEY `ingredients` (`ingredients`),
FULLTEXT KEY `ft_all` (`brand_name`,`generic_name`,`labeler_name`,`ingredients`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SHOW CREATE TABLE package_tbl (contains 205,042 records)
CREATE TABLE `package_tbl` (
`id` mediumint(8) unsigned DEFAULT NULL,
`package_ndc` char(12) NOT NULL,
`package_ndc_11dig` char(13) NOT NULL,
`fuzzed_package_ndc` varchar(10) NOT NULL,
`fuzzed_package_ndc_11dig` varchar(11) NOT NULL,
`description` varchar(255) NOT NULL,
KEY `package_ndc` (`package_ndc`),
KEY `package_ndc_11dig` (`package_ndc_11dig`),
KEY `fuzzed_package_ndc` (`fuzzed_package_ndc`),
KEY `fuzzed_package_ndc_11dig` (`fuzzed_package_ndc_11dig`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Sample of database contents (SELECT * of both joined tables)
+-------+------------------------------------------------+-------------+--------------------------------------+-------+------------------------------------------------------------+------------------------------------------------------------+-------------+--------------+--------------------------------------------+----------------------+----------+-------------------------------------------------------------------------------------------------+-------+--------------+-------------------+--------------------+--------------------------+--------------------------------------------------------------------------+--------------------+
| id | product_id | product_ndc | spl_id | rxcui | brand_name | generic_name | dosage_form | dea_schedule | labeler_name | is_original_packager | finished | ingredients | id | package_ndc | package_ndc_11dig | fuzzed_package_ndc | fuzzed_package_ndc_11dig | description | score |
+-------+------------------------------------------------+-------------+--------------------------------------+-------+------------------------------------------------------------+------------------------------------------------------------+-------------+--------------+--------------------------------------------+----------------------+----------+-------------------------------------------------------------------------------------------------+-------+--------------+-------------------+--------------------+--------------------------+--------------------------------------------------------------------------+--------------------+
| 1875 | 71930-054_79377617-686b-4640-b2b8-e1358cf82358 | 71930-054 | 79377617-686b-4640-b2b8-e1358cf82358 | | ACETAMINOPHEN AND CODEINE PHOSPHATE | acetaminophen and codeine phosphate | TABLET | 3 | Eywa Pharma Inc | 1 | 1 | ACETAMINOPHEN (300 mg/1); CODEINE PHOSPHATE (15 mg/1) | 1875 | 71930-054-12 | 71930-0054-12 | 7193005412 | 71930005412 | 100 TABLET in 1 BOTTLE (71930-054-12) | 21.973543167114258 |
| 13240 | 0527-1727_7265ba33-74cc-4607-a29c-2dcc9c59c774 | 0527-1727 | 7265ba33-74cc-4607-a29c-2dcc9c59c774 | | Codeine Sulfate | Codeine Sulfate | TABLET | 2 | Lannett Company, Inc. | -1 | 1 | CODEINE SULFATE (15 mg/1) | 13240 | 0527-1727-91 | 00527-1727-91 | 527172791 | 527172791 | 4 BLISTER PACK in 1 CARTON (0527-1727-91) > 25 TABLET in 1 BLISTER PACK | 21.973543167114258 |
| 13277 | 0591-2641_256c7767-84e0-4f6c-a682-154caaec6775 | 0591-2641 | 256c7767-84e0-4f6c-a682-154caaec6775 | | Butalbital, Acetaminophen, Caffeine, and Codeine Phosphate | Butalbital, Acetaminophen, Caffeine, and Codeine Phosphate | CAPSULE | 3 | Actavis Pharma, Inc. | 1 | 1 | BUTALBITAL (50 mg/1); ACETAMINOPHEN (300 mg/1); CAFFEINE (40 mg/1); CODEINE PHOSPHATE (30 mg/1) | 13277 | 0591-2641-01 | 00591-2641-01 | 591264101 | 591264101 | 100 CAPSULE in 1 BOTTLE, PLASTIC (0591-2641-01) | 21.973543167114258 |
| 14149 | 0603-2553_c5b97ed1-27c5-4ca5-9675-7b72e5c8fa0b | 0603-2553 | c5b97ed1-27c5-4ca5-9675-7b72e5c8fa0b | | Butalbital, Acetaminophen, Caffeine, and Codeine Phosphate | Butalbital, Acetaminophen, Caffeine, and Codeine Phosphate | CAPSULE | 3 | Par Pharmaceutical | 1 | 1 | BUTALBITAL (50 mg/1); ACETAMINOPHEN (325 mg/1); CAFFEINE (40 mg/1); CODEINE PHOSPHATE (30 mg/1) | 14149 | 0603-2553-21 | 00603-2553-21 | 603255321 | 603255321 | 100 CAPSULE in 1 BOTTLE, PLASTIC (0603-2553-21) | 21.973543167114258 |
| 14169 | 0603-2338_3809e549-9586-4a30-b20b-a01c08d9869d | 0603-2338 | 3809e549-9586-4a30-b20b-a01c08d9869d | | Acetaminophen And Codeine | acetaminophen and codeine phosphate | TABLET | 3 | Par Pharmaceutical | 1 | 1 | CODEINE PHOSPHATE (30 mg/1); ACETAMINOPHEN (300 mg/1) | 14169 | 0603-2338-32 | 00603-2338-32 | 603233832 | 603233832 | 1000 TABLET in 1 BOTTLE, PLASTIC (0603-2338-32) | 21.973543167114258 |
| 14238 | 0603-1585_8fd47fe8-c377-4255-b64a-739a5f91ca74 | 0603-1585 | 8fd47fe8-c377-4255-b64a-739a5f91ca74 | | Promethazine with Codeine | Promethazine Hydrochloride and Codeine Phosphate | SOLUTION | 5 | Par Pharmaceutical | -1 | 1 | PROMETHAZINE HYDROCHLORIDE (6.25 mg/5mL); CODEINE PHOSPHATE (10 mg/5mL) | 14238 | 0603-1585-58 | 00603-1585-58 | 603158558 | 603158558 | 473 mL in 1 BOTTLE (0603-1585-58) | 21.973543167114258 |
+-------+------------------------------------------------+-------------+--------------------------------------+-------+------------------------------------------------------------+------------------------------------------------------------+-------------+--------------+--------------------------------------------+----------------------+----------+-------------------------------------------------------------------------------------------------+-------+--------------+-------------------+--------------------+--------------------------+--------------------------------------------------------------------------+--------------------+
Sample of database contents requested by @luuk
Results from SELECT generic_name, labeler_name, ingredients FROM product_tbl WHERE MATCH (brand_name, generic_name, labeler_name, ingredients) AGAINST ('codeine' IN BOOLEAN MODE) LIMIT 20:
+------------------------------------------------------------+------------------------------------+-------------------------------------------------------------------------------------------------+
| generic_name | labeler_name | ingredients |
+------------------------------------------------------------+------------------------------------+-------------------------------------------------------------------------------------------------+
| promethazine hydrochloride and codeine phosphate | Apotheca Inc. | PROMETHAZINE HYDROCHLORIDE (6.25 mg/5mL); CODEINE PHOSPHATE (10 mg/5mL) |
| ACETAMINOPHEN AND CODEINE PHOSPHATE | Direct_Rx | ACETAMINOPHEN (300 mg/1); CODEINE PHOSPHATE (30 mg/1) |
| ACETAMINOPHEN AND CODEINE PHOSPHATE | RedPharm Drug | ACETAMINOPHEN (300 mg/1); CODEINE PHOSPHATE (30 mg/1) |
| Codeine Phosphate and APAP | Northwind Pharmaceuticals, LLC | ACETAMINOPHEN (300 mg/1); CODEINE PHOSPHATE (30 mg/1) |
| promethazine hydrochloride and codeine phosphate | Morton Grove Pharmaceuticals, Inc. | PROMETHAZINE HYDROCHLORIDE (6.25 mg/5mL); CODEINE PHOSPHATE (10 mg/5mL) |
| Acetaminophen and Codeine Phosphate | Elite Laboratories, Inc. | ACETAMINOPHEN (300 mg/1); CODEINE PHOSPHATE (60 mg/1) |
| Codeine phosphate and Guaifenesin | Proficient Rx LP | CODEINE PHOSPHATE (10 mg/5mL); GUAIFENESIN (100 mg/5mL) |
+------------------------------------------------------------+------------------------------------+-------------------------------------------------------------------------------------------------+
Removing the OR … LIKE … conditions
If I remove the conditions matching on columns in the second table of the join (the lines containing OR … LIKE …), query time is 0.02 seconds. This is just an experiment to show that the issue is related to comparing values in the joined table. I really do need to compare on that table, so this isn't a viable option.
EXPLAIN outout from query without OR … LIKE … conditions:
+----+-------------+-------------+------------+----------+------------------------------------------------------------------------------------------------------+--------+---------+--------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+----------+------------------------------------------------------------------------------------------------------+--------+---------+--------------------+------+----------+----------------------------------------------+
| 1 | SIMPLE | product_tbl | NULL | fulltext | PRIMARY,product_ndc,brand_name,generic_name,dosage_form,dea_schedule,labeler_name,ingredients,ft_all | ft_all | 0 | const | 1 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | package_tbl | NULL | ref | id | id | 4 | fwr.product_tbl.id | 1 | 100.00 | Using index |
+----+-------------+-------------+------------+----------+------------------------------------------------------------------------------------------------------+--------+---------+--------------------+------+----------+----------------------------------------------+
Removing the GROUP BY … clause
If I remove the GROUP BY product_tbl.id line, query time is 0.0015 seconds. That's great, but then I have duplicated rows for the data I need.
EXPLAIN outout from a query without GROUP BY:
+----+-------------+-------------+------------+----------+---------------+--------+---------+--------------------+--------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+----------+---------------+--------+---------+--------------------+--------+----------+------------------+
| 1 | SIMPLE | product_tbl | NULL | fulltext | NULL | ft_all | 3099 | NULL | 102739 | 100.00 | Ft_hints: sorted |
| 1 | SIMPLE | package_tbl | NULL | ref | id | id | 4 | fwr.product_tbl.id | 1 | 100.00 | Using where |
+----+-------------+-------------+------------+----------+---------------+--------+---------+--------------------+--------+----------+------------------+
Using a subquery
I've tried using a subquery to select the matching records in package_tbl. It's slightly faster, but still slow – about 0.6 seconds:
SELECT
`product_ndc`,
CONCAT(`brand_name`, ' (', `generic_name`, ')') AS `name`,
`dosage_form`,
`dea_schedule`,
`labeler_name`,
`ingredients`,
MATCH (`brand_name`, `generic_name`, `labeler_name`, `ingredients`) AGAINST ('codeine' IN BOOLEAN MODE) AS `score`
FROM product_tbl
WHERE MATCH (`brand_name`, `generic_name`, `labeler_name`, `ingredients`) AGAINST ('codeine' IN BOOLEAN MODE)
OR `product_tbl`.`id` IN (
SELECT id FROM package_tbl
WHERE `package_ndc` LIKE '4%'
OR `package_ndc_11dig` LIKE '4%'
OR `fuzzed_package_ndc` LIKE '4%'
OR `fuzzed_package_ndc_11dig` LIKE '4%'
)
GROUP BY `product_tbl`.`id`
ORDER BY `score` DESC
LIMIT 25;
EXPLAIN outout:
+----+-------------+-------------+------------+------+------------------------------------------------------------------------------------------------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+------------------------------------------------------------------------------------------------------+------+---------+------+--------+----------+-----------------------------+
| 1 | PRIMARY | product_tbl | NULL | ALL | PRIMARY,product_ndc,brand_name,generic_name,dosage_form,dea_schedule,labeler_name,ingredients,ft_all | NULL | NULL | NULL | 102739 | 100.00 | Using where; Using filesort |
| 2 | SUBQUERY | package_tbl | NULL | ALL | package_ndc,package_ndc_11dig,fuzzed_package_ndc,fuzzed_package_ndc_11dig,id | NULL | NULL | NULL | 192238 | 37.57 | Using where |
+----+-------------+-------------+------------+------+------------------------------------------------------------------------------------------------------+------+---------+------+--------+----------+-----------------------------+
After adding PRIMARY KEY to package_tbl
PRIMARY KEY id (id, package_ndc, package_ndc_11dig, fuzzed_package_ndc, fuzzed_package_ndc_11dig).
Performance improved slightly, from 1.2 s to 1.0 s.
EXPLAIN outout after adding primary key:
+----+-------------+-------------+------------+-------+------------------------------------------------------------------------------------------+---------+---------+--------------------+--------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+------------------------------------------------------------------------------------------+---------+---------+--------------------+--------+----------+---------------------------------+
| 1 | SIMPLE | product_tbl | NULL | index | PRIMARY,product_ndc,brand_name,generic_name,dosage_form,dea_schedule,labeler_name,ft_all | PRIMARY | 3 | NULL | 101201 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | package_tbl | NULL | ref | PRIMARY | PRIMARY | 3 | fwr.product_tbl.id | 1 | 100.00 | Using where; Using index |
+----+-------------+-------------+------------+-------+------------------------------------------------------------------------------------------+---------+---------+--------------------+--------+----------+---------------------------------+
SHOW INDEX FROM product_tbl adding primary key:
+-------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| product_tbl | 0 | PRIMARY | 1 | id | A | 101199 | NULL | NULL | | BTREE | | |
| product_tbl | 1 | product_ndc | 1 | product_ndc | A | 95416 | NULL | NULL | | BTREE | | |
| product_tbl | 1 | brand_name | 1 | brand_name | A | 38657 | NULL | NULL | | BTREE | | |
| product_tbl | 1 | generic_name | 1 | generic_name | A | 12743 | NULL | NULL | | BTREE | | |
| product_tbl | 1 | dosage_form | 1 | dosage_form | A | 93 | NULL | NULL | | BTREE | | |
| product_tbl | 1 | dea_schedule | 1 | dea_schedule | A | 4 | NULL | NULL | | BTREE | | |
| product_tbl | 1 | labeler_name | 1 | labeler_name | A | 6471 | NULL | NULL | | BTREE | | |
| product_tbl | 1 | ft_all | 1 | brand_name | NULL | 101201 | NULL | NULL | | FULLTEXT | | |
| product_tbl | 1 | ft_all | 2 | generic_name | NULL | 101201 | NULL | NULL | | FULLTEXT | | |
| product_tbl | 1 | ft_all | 3 | labeler_name | NULL | 101201 | NULL | NULL | | FULLTEXT | | |
| product_tbl | 1 | ft_all | 4 | ingredients | NULL | 101201 | NULL | NULL | | FULLTEXT | | |
+-------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
SHOW INDEX FROM package_tbl adding primary key:
+-------------+------------+-------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| package_tbl | 0 | PRIMARY | 1 | id | A | 103229 | NULL | NULL | | BTREE | | |
| package_tbl | 0 | PRIMARY | 2 | package_ndc | A | 188684 | NULL | NULL | | BTREE | | |
| package_tbl | 0 | PRIMARY | 3 | package_ndc_11dig | A | 192642 | NULL | NULL | | BTREE | | |
| package_tbl | 0 | PRIMARY | 4 | fuzzed_package_ndc | A | 192642 | NULL | NULL | | BTREE | | |
| package_tbl | 0 | PRIMARY | 5 | fuzzed_package_ndc_11dig | A | 192640 | NULL | NULL | | BTREE | | |
| package_tbl | 1 | package_ndc | 1 | package_ndc | A | 167869 | NULL | NULL | | BTREE | | |
+-------------+------------+-------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Removing the ORDER BY solves the problem too
Query executes in 0.01 seconds!
EXPLAIN output with removed ORDER BY (notice the lack of Using temporary; Using filesort):
+----+-------------+-------------+------------+-------+------------------------------------------------------------------------------------------+---------+---------+--------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+------------------------------------------------------------------------------------------+---------+---------+--------------------+------+----------+--------------------------+
| 1 | SIMPLE | product_tbl | NULL | index | PRIMARY,product_ndc,brand_name,generic_name,dosage_form,dea_schedule,labeler_name,ft_all | PRIMARY | 3 | NULL | 13 | 100.00 | NULL |
| 1 | SIMPLE | package_tbl | NULL | ref | PRIMARY | PRIMARY | 3 | fwr.product_tbl.id | 1 | 100.00 | Using where; Using index |
+----+-------------+-------------+------------+-------+------------------------------------------------------------------------------------------+---------+---------+--------------------+------+----------+--------------------------+
If I use ORDER BY product_tbl.id, performance is improved but not as much (0.1 seconds). The EXPLAIN output using the primary key as the order by is identical to the above (also no temporary, no filesort).
Best Answer
ORis hard to optimize.Plan A The following may decrease the average time spent, but won't decrease the maximum time spent.
SELECT ... MATCHwithout theORs.SELECTwith the other test. I will involve a full table scan because of theORs.Plan B Add another column so that a copy of all the "text" is collected into it. Then put a
FULLTEXTindex on that column. Meanwhile, drop the existing FT index. Rewrite the query so that there only the oneMATCH, noORs.product:package?
sounds like these tables are 1:1. In general, you should combine table taht are 1:1. That would eliminate the
GROUP BYand perhaps some other issues.But it smells like one product may have 0, 1, or more packages?? If so, please don't use
id; this implies (to readers)PRIMARY KEYand unique. So, is it really 1:many?Assuming it is 1:many, then split the query into 2
SELECTs, thenUNIONthem together. Also, combine the 4 columns being tested into a single column, perhaps in another table, so that a singleLIKE 'codeine%'can be used instead ofOR. (Sorry, I have not fleshed out the details.)CHAR/VARCHAR/collation These comments will help performance slightly:
Use
CHARSET asciifor some of those ids;utf8is overkill and slightly slower. Then useCOLLATE ascii_binorascii_general_cias appropriate.Consider stripping the dashes and using
UNHEX()to turn those lengthyVARCHARsinto shorterBINARY().Don't use
CHARfor variable-length columns; vice versa forVARCHAR.