2

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      |         |               |
+---------------+------------+----------------------------------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  • the KEY on code text COLLATE utf8_unicode_ci namely KEY code (code(255)) and other such indices on text fields aren't likely doing you any good. None of the joins are performed using the indices, but rather the full text version. As those keys are unclustered (for one: they're not the primary key) you are probably just adding to the cost every time you INSERT without benefiting you on a SELECT. This is the basis of the benefit of the FULLTEXT index that @RickJames indicates - it will actually be used for Joins – mpag Nov 3 '15 at 21:37
2

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.

  • That's a really good answer. I would upvote it twice if I could. A lot of the advice here is sound for any rdbms. – Nicolas de Fontenay Nov 3 '15 at 16:56
-1

Break the query into pieces first. Do the joins and save it as a VIEW and see how long that takes. Then query the VIEW with ONE of the WHERE clauses at a time and see which one takes the most time. You may have to add an INDEX on the one that seems to be the slowest.

  • 1
    VIEWs are syntactic sugar; they provide no performance benefit. – Rick James Nov 3 '15 at 16:28
  • with the possible exception of Indexed / Persistent VIEWs, but only if the indexes are "better" than those in the source table(s) – mpag Nov 3 '15 at 21:23

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.