使用 where 语句优化 MySQL 查询

Question

我目前正在处理包含 >100 万条记录的大数据库，我正在使用下面的查询来根据我的标准填写记录，但即使我索引了主键，它也太慢了。

SQL:

SELECT DISTINCT title, productID, catID, description, productUrl, 
    regularPrice, salePrice, imageUrl, merID, created_at, 
    updated_at, name, link, source 
FROM products p, advertisers a 
WHERE (title LIKE '%headphone%' OR description LIKE '%headphone%') 
AND catID=22 
AND regularPrice IS NOT NULL 
AND p.merID = a.advertID

谁能帮我优化查询？

谢谢，

编辑:

表结构:

CREATE TABLE `products` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `productID` varchar(50) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
  `catID` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `title` text COLLATE utf8_unicode_ci,
  `description` longtext COLLATE utf8_unicode_ci,
  `productUrl` text COLLATE utf8_unicode_ci NOT NULL,
  `regularPrice` double(8,2) DEFAULT NULL,
  `salePrice` double(8,2) NOT NULL,
  `imageUrl` text COLLATE utf8_unicode_ci NOT NULL,
  `merID` varchar(50) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `advertisers` (
  `id` int(10) UNSIGNED NOT NULL PRIMARY KEY,
  `advertID` varchar(50) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  `link` text COLLATE utf8_unicode_ci NOT NULL,
  `logo` text COLLATE utf8_unicode_ci NOT NULL,
  `source` varchar(10) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

解释:

mysql> EXPLAIN EXTENDED SELECT DISTINCT title, productID, catID, description, productUrl, regularPrice, salePrice, imageUrl, merID, created_at, updated_at, name, link, source FROM products p, advertisers a WHERE (title LIKE '%headphone%' OR description LIKE '%headphone%') AND catID=22 AND regularPrice IS NOT NULL AND p.merID = a.advertID;
+----+-------------+-------+------------+--------+---------------------+----------+---------+-------------------+--------+----------+------------------------------+
| id | select_type | table | partitions | type   | possible_keys       | key      | key_len | ref               | rows   | filtered | Extra                        |
+----+-------------+-------+------------+--------+---------------------+----------+---------+-------------------+--------+----------+------------------------------+
|  1 | SIMPLE      | p     | NULL       | ALL    | merID               | NULL     | NULL    | NULL              | 682242 |     1.89 | Using where; Using temporary |
|  1 | SIMPLE      | a     | NULL       | eq_ref | advertID,advertID_2 | advertID | 152     | datafeeds.p.merID |      1 |   100.00 | NULL                         |
+----+-------------+-------+------------+--------+---------------------+----------+---------+-------------------+--------+----------+------------------------------+
2 rows in set, 2 warnings (0.00 sec)

Answer 1

检查产品的连接键 merID 和广告商的 advertID 中的索引

 SELECT DISTINCT 
    title
  , productID
  , catID
  , description
  , productUrl
  , regularPrice
  , salePrice
  , imageUrl
  , merID
  , created_at
  , updated_at
  , name
  , link
  , source 
FROM products p
INNER JOIN  advertisers a  ON p.merID = a.advertID
WHERE (title LIKE '%headphone%' OR description LIKE '%headphone%') 
AND catID=22 
AND regularPrice IS NOT NULL 

可能对(可能)更具选择性的列列上的复合索引有用

 (catID, merID)

由于使用 like 和 si not null，regularPrice、title 和 description 在复合索引中不应该有用