Mysql – Faster query when joining data from multiple tables

MySQLperformancequery-performance

I’m looking for a way to make a database query faster. The table parts has over 6 million rows. Each part row is associated with the manufacturers table (a couple hundred rows) by ID. When searching for results matching a query in a specific column (i.e.: partNumber or materialDescription) I have efficient queries that return results quickly.

The problem I’m having is when I’m doing an ALL search where the result can be in the parts table’s partNumber column or materialDescription column or the manufacturer’s title (which can only be determined by the manufacturerID from the part’s row which gets the manufacturerCode from the manufacturers table which can then get the manufacturer’s actual name by finding the entry in our CMS' content table field_manufacturerCode column. The results time is well past 20 seconds. Is there any way to make this database query more efficient assuming we can’t add the actual manufacturer’s name to the manufacturers table because the API we are getting the data from doesn’t include the manufacturer’s name?

SELECT p.id, p.manufacturerId, m.code as manufacturerCode, p.materialNumber, p.partNumber, p.materialDescription, c.title as manufacturerTitle
FROM `craft__parts` AS p
JOIN `craft__manufacturers` AS m ON p.manufacturerId = m.id
JOIN `craft_content` AS c ON m.code = c.field_manufacturerCode
WHERE p.partNumber like '%polymer%'
OR p.materialDescription like '%polymer%'
OR c.title like '%polymer%'
ORDER BY manufacturerTitle asc

DDL for all involved tables:

craft__manufacturers

CREATE TABLE `craft__he_manufacturers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` text COLLATE utf8_unicode_ci,
  `totalParts` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `code` (`code`(255)),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

craft__parts

CREATE TABLE `craft__parts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `manufacturerId` int(11) NOT NULL,
  `materialNumber` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `partNumber` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `materialDescription` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `_parts_manufacturerId_partNumber_unq` (`manufacturerId`,`partNumber`),
  UNIQUE KEY `_parts_materialNumber_unq` (`materialNumber`),
  KEY `_parts_materialDescription` (`materialDescription`),
  KEY `_parts_partNumber` (`partNumber`),
  KEY `manufacturerId` (`manufacturerId`),
  CONSTRAINT `_parts_manufacturerId` FOREIGN KEY (`manufacturerId`) REFERENCES `craft__manufacturers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

craft_content

CREATE TABLE `craft_content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `elementId` int(11) NOT NULL,
  `locale` char(12) COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `field_heading` text COLLATE utf8_unicode_ci,
  `field_body` text COLLATE utf8_unicode_ci,
  `field_manufacturerCode` text COLLATE utf8_unicode_ci,
  `dateCreated` datetime NOT NULL,
  `dateUpdated` datetime NOT NULL,
  `uid` char(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `craft_content_elementId_locale_unq_idx` (`elementId`,`locale`),
  KEY `craft_content_title_idx` (`title`),
  KEY `craft_content_locale_fk` (`locale`),
  KEY `title` (`title`),
  KEY `field_manufacturerCode` (`field_manufacturerCode`(255)),
  CONSTRAINT `craft_content_elementId_fk` FOREIGN KEY (`elementId`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE,
  CONSTRAINT `craft_content_locale_fk` FOREIGN KEY (`locale`) REFERENCES `craft_locales` (`locale`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Indexes – cardinalities

craft__manufacturers

+-------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| craft__manufacturers |          0 | PRIMARY  |            1 | id          | A         |         153 |     NULL | NULL   |      | BTREE      |         |               |
| craft__manufacturers |          1 | code     |            1 | code        | A         |         153 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

craft__parts

+-----------------+------------+-------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name                      | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+-------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| craft__parts |          0 | PRIMARY                       |            1 | id                  | A         |     6287165 |     NULL | NULL   |      | BTREE      |         |               |
| craft__parts |          0 | _parts_materialNumber_unq  |            1 | materialNumber      | A         |     6287165 |     NULL | NULL   |      | BTREE      |         |               |
| craft__parts |          1 | _parts_materialDescription |            1 | materialDescription | A         |     3143582 |     NULL | NULL   | YES  | BTREE      |         |               |
| craft__parts |          1 | _parts_partNumber          |            1 | partNumber          | A         |     6287165 |     NULL | NULL   |      | BTREE      |         |               |
| craft__parts |          1 | manufacturerId                |            1 | manufacturerId      | A         |          19 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------+------------+-------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

craft_content

+---------------+------------+----------------------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name                               | Seq_in_index | Column_name            | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------------------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| craft_content |          0 | PRIMARY                                |            1 | id                     | A         |        4813 |     NULL | NULL   |      | BTREE      |         |               |
| craft_content |          0 | craft_content_elementId_locale_unq_idx |            1 | elementId              | A         |        4813 |     NULL | NULL   |      | BTREE      |         |               |
| craft_content |          0 | craft_content_elementId_locale_unq_idx |            2 | locale                 | A         |        4813 |     NULL | NULL   |      | BTREE      |         |               |
| craft_content |          1 | craft_content_title_idx                |            1 | title                  | A         |        4813 |     NULL | NULL   | YES  | BTREE      |         |               |
| craft_content |          1 | craft_content_locale_fk                |            1 | locale                 | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| craft_content |          1 | title                                  |            1 | title                  | A         |        4813 |     NULL | NULL   | YES  | BTREE      |         |               |
| craft_content |          1 | field_manufacturerCode                 |            1 | field_manufacturerCode | A         |         534 |      255 | NULL   | YES  | BTREE      |         |               |
+---------------+------------+----------------------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Best Answer

Leading wildcard in LIKE is the main problem. Another problem is that the WHERE clause is spread across 3 tables.

Consider using a FULLTEXT index; this will make looking for "words" much faster. Alas, you may be stuck with LIKE in the 20-char part number.

Still the structure of the query will need to be changed...

SELECT  p.id, p.manufacturerId, m.code as manufacturerCode, p.materialNumber,
        p.partNumber, p.materialDescription, c.title as manufacturerTitle
    FROM  ( 
              ( SELECT  id
                    FROM  craft__parts
                    WHERE  partNumber like '%polymer%' 
              )
            UNION  DISTINCT 
              ( SELECT  id
                    FROM  craft__parts
                    WHERE  materialDescription like '%polymer%' 
              )
            UNION  DISTINCT 
              ( SELECT  p3.id
                    FROM  `craft__parts` AS p3
                    JOIN  `craft__manufacturers` AS m3 ON p3.manufacturerId = m3.id
                    JOIN  `craft_content` AS c3 ON m3.code = c3.field_manufacturerCode
                    WHERE  title like '%polymer%' 
              )
          ) AS p
    JOIN  `craft__manufacturers` AS m ON p.manufacturerId = m.id
    JOIN  `craft_content` AS c ON m.code = c.field_manufacturerCode
    ORDER BY  c.title asc

The goal there is to

  1. Do each of the awful searches separately.
  2. Combine the results (UNION DISTINCT ... p.id)
  3. Then look up the rest of the fields

Step 1 still involves a full table scan (if using LIKE) or a quick FULLTEXT lookup (much better).

Steps 2 and should be relatively fast, assuming there were not too many matching rows.

The problem with the original query is all the bulky stuff hauled around while doing the JOINs.

Other notes...

A PRIMARY KEY is a KEY; do not redundantly say KEY(id).

Does m.code really need to be bigger than 255 characters? "Prefix" indexes are virtually useless, and may make the JOIN in the third UNION not run very fast.

Related Question