mysql: slow query when matching columns of the second table in JOIN with GROUP BY

I could not optimize this MySQL query successfully. It takes about 1.2 seconds to run on my development computer. If I delete the GROUP BY line or conditions that match in the columns of the second table of the union (those that have OR … LIKE …), performance improves significantly (EXPLAIN SELECT results below).

I appreciate any suggestions to make this work faster.

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 '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;

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

EXPLAIN with my original query (pasted at the top of this question)

+----+-------------+-------------+------------+-------+------------------------------------------------------------------------------------------------------+---------+---------+--------------------+--------+----------+---------------------------------+
| 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                     |
+----+-------------+-------------+------------+-------+------------------------------------------------------------------------------------------------------+---------+---------+--------------------+--------+----------+---------------------------------+

The elimination of the OR … LIKE … terms

If I delete the conditions that match in the columns of the second table of the union (the lines that contain OR … LIKE …), the query time is 0.02 seconds. This is just an experiment to show that the problem is related to the comparison of values ​​in the joined table. I really need to compare in that table, so this is not a viable option.

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)
GROUP BY `product_tbl`.`id`
ORDER BY `score` DESC
LIMIT 25;
+----+-------------+-------------+------------+----------+------------------------------------------------------------------------------------------------------+--------+---------+--------------------+------+----------+----------------------------------------------+
| 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                                  |
+----+-------------+-------------+------------+----------+------------------------------------------------------------------------------------------------------+--------+---------+--------------------+------+----------+----------------------------------------------+

The elimination of the GROUP BY … clause

If I delete the GROUP BY product_tbl.id line, The query time is 0.0015 seconds. That's great, but then I doubled rows for the data I need.

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 '4%'
OR `package_ndc_11dig` LIKE '4%'
OR `fuzzed_package_ndc` LIKE '4%'
OR `fuzzed_package_ndc_11dig` LIKE '4%'
ORDER BY `score` DESC
LIMIT 25;
+----+-------------+-------------+------------+----------+---------------+--------+---------+--------------------+--------+----------+------------------+
| 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 tried using a subquery to select the matching records in package_tbl. It's a little faster, but it's 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;
+----+-------------+-------------+------------+------+------------------------------------------------------------------------------------------------------+------+---------+------+--------+----------+-----------------------------+
| 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                 |
+----+-------------+-------------+------------+------+------------------------------------------------------------------------------------------------------+------+---------+------+--------+----------+-----------------------------+