0

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).

  • Can you add the DDL for the two tables used, and also the output of 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 (i do not know if 20 is enough to give a correct idea about the contents of these columns.... – Luuk Feb 15 at 9:35
  • Hi @Luuk! For the DDL, what do you need other than the results from SHOW CREATE TABLE pasted above? Is there more to a DDL than that? I've added the results from the query you requested under heading Sample of contents requested by @luuk, as well as sample content from all columns. – Quinn Comendant Feb 15 at 15:57
  • Is initial condition correct? It looks like it should be AND (package_ndc ... OR .. OR .. ) – Nikita Feb 15 at 17:38
  • @Nikita, OR is intentional. The query is part of a search function to match items on any criteria. – Quinn Comendant Feb 15 at 18:45
  • But still its a good idea, when searching for (i.e.) 'codeine' and 'package_ndc starts with 4' , the 'package_ndc starts with 4' will limit the number of records that need to be full-text searched, and therefore speed up the query. The end-user should not have an interest in 'codeine' and 'package_ndc starts with ' soemting else than 4 – Luuk Feb 15 at 18:53
0

OR is hard to optimize.

Plan A The following may decrease the average time spent, but won't decrease the maximum time spent.

  1. SELECT ... MATCH without the ORs.
  2. See if the results are "good enough"; if so, stop here.
  3. Else, run a separate SELECT with the other test. I will involve a full table scan because of the ORs.

Plan B Add another column so that a copy of all the "text" is collected into it. Then put a FULLTEXT index on that column. Meanwhile, drop the existing FT index. Rewrite the query so that there only the one MATCH, no ORs.

product:package?

(`product_tbl`.`id` = `package_tbl`.`id`)

sounds like these tables are 1:1. In general, you should combine table taht are 1:1. That would eliminate the GROUP BY and 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 KEY and unique. So, is it really 1:many?

Assuming it is 1:many, then split the query into 2 SELECTs, then UNION them together. Also, combine the 4 columns being tested into a single column, perhaps in another table, so that a single LIKE 'codeine%' can be used instead of OR. (Sorry, I have not fleshed out the details.)

CHAR/VARCHAR/collation These comments will help performance slightly:

  • Use CHARSET ascii for some of those ids; utf8 is overkill and slightly slower. Then use COLLATE ascii_bin or ascii_general_ci as appropriate.

  • Consider stripping the dashes and using UNHEX() to turn those lengthy VARCHARs into shorter BINARY().

  • Don't use CHAR for variable-length columns; vice versa for VARCHAR.

|improve this answer|||||
  • Hi Rick, thanks for all the suggestions. I'm excited to try all these minor optimizations. I was worried I was doing something clearly wrong in my query, but after letting the DBA community look at my question I guess there are no mistakes in my query, I just have to be more smart how I handle the data. – Quinn Comendant Feb 24 at 17:59
  • @QuinnComendant - "Wrong" is relative. If your query gets the "right" answers, then it is not "wrong". "Too slow" does not equate to "wrong". Good luck. My suggestions vary widely in how much speedup they will provide, and in how easy they will be to implement. I believe (without proof) that none of my suggestions are "wrong"; they "should" give you the same answers. – Rick James Feb 24 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.