Блог

Почему этот запрос (и ряд подобных вариантов) не использует индекс для ASIN в таблице ‘tags’? Он настаивает на проверке всей таблицы, даже если A содержит всего несколько строк. Поскольку таблица ‘tags’ в рабочей среде содержит почти миллион записей, это довольно сильно убивает запрос.

 SELECT C.tag, count(C.tag) AS total
FROM
(
    SELECT B.*
    FROM
    (
        SELECT ASIN FROM requests WHERE user_id=9
    ) A
    INNER JOIN tags B USING(ASIN)
) C
GROUP BY C.tag ORDER BY total DESC 
 

ОБЪЯСНЕНИЕ показывает, что индекс не используется (выполняется в тестовой базе данных, поэтому строк в «тегах» мало, но все равно выполняется полное сканирование таблицы):

 |  1 | PRIMARY     | <derived2>         | system | NULL          | NULL        | NULL    | NULL |    0 | const row not found            |
|  2 | DERIVED     | <derived3>         | ALL    | NULL          | NULL        | NULL    | NULL |   28 |                                |
|  2 | DERIVED     | B                  | ALL    | NULL          | NULL        | NULL    | NULL | 2593 | Using where; Using join buffer |
|  3 | DERIVED     | borrowing_requests | ref    | idx_user_id   | idx_user_id | 5       |      |   27 | Using where        
 

Индексы:

 | book_tags |          1 | asin           |            1 | ASIN        | A         |         432 |     NULL | NULL   |      | BTREE      |         |
| book_tags |          1 | idx_tag        |            1 | tag         | A         |        1296 |     NULL | NULL   |      | BTREE      |         |
| book_tags |          1 | idx_updated_on |            1 | updated_on  | A         |         518 |     NULL | NULL   |      | BTREE
 

Запрос был переписан из INNER JOIN, у которого была та же проблема:

 SELECT tag, count(tag) AS total 
FROM tags 
INNER JOIN requests ON requests.ASIN=tags.ASIN 
WHERE user_id=9 
GROUP BY tag 
ORDER BY total DESC
 

ОБЪЯСНИТЕ:

 |  1 | SIMPLE      | tags          | ALL  | NULL                 | NULL     | NULL    | NULL | 2593 | Using temporary; Using filesort |
|  1 | SIMPLE      | requests | ref  | idx_ASIN,idx_user_id | idx_ASIN | 33      | func |    3 | Using where 
 

Я понимаю, что это реальная базовая точка, которую я упускаю, но около 4 часов работы над ней меня никуда не привели. Любые советы приветствуются.

Редактировать:

Я вижу, что первый запрос, использующий подзапросы, не будет использовать индексы благодаря некоторым ответам, но это использовалось, поскольку оно выполнялось в два раза быстрее, чем нижний запрос, только с ВНУТРЕННИМ СОЕДИНЕНИЕМ.

В качестве примера, в запросах содержится 70 тыс. строк (все с индексированным ASIN) и 700 тыс. строк в тегах, с 95 тыс. различных ASIN в тегах, каждый из которых содержит менее 10 различных записей тегов.

Если у пользователя есть 10 запросов, я хочу, чтобы были перечислены и подсчитаны только теги из этих 10 ASIN. На мой взгляд, для этого следует использовать tags.idx_ASIN и искать не более 100 строк (10 ASIN, каждая с максимум 10 тегами) из таблицы тегов.

Я что-то упускаю…Я просто не вижу, что.

Редактировать:

запросы СОЗДАЮТ ТАБЛИЦУ:

 CREATE TABLE IF NOT EXISTS `requests` (
  `bid` int(40) NOT NULL AUTO_INCREMENT,
  `user_id` int(20) DEFAULT NULL,
  `ASIN` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` enum('active','inactive','pending','deleted','completed') COLLATE     utf8_unicode_ci NOT NULL,
  `added_on` datetime NOT NULL,
  `status_changed_on` datetime NOT NULL,
  `last_emailed` datetime DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`bid`),
  KEY `idx_ASIN` (`ASIN`),
  KEY `idx_status` (`status`),
  KEY `idx_added_on` (`added_on`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status_changed_on` (`status_changed_on`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=149380 ;
 

теги СОЗДАЮТ ТАБЛИЦУ

 CREATE TABLE IF NOT EXISTS `tags` (
  `ASIN` varchar(10) NOT NULL,
  `tag` varchar(50) NOT NULL,
  `updated_on` datetime NOT NULL,
  KEY `idx_tag` (`tag`),
  KEY `idx_updated_on` (`updated_on`),
  KEY `idx_asin` (`ASIN`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 

В тегах нет первичного ключа. Обычно у меня нет таблиц без первичных ключей, но в этом я не видел необходимости. Может ли это быть проблемой?

АГА! Разные кодировки и параметры сортировки. Я исправлю это и попробую еще раз!

Позже:

Вот и все. Запрос снизился с 10 секунд до 0,006 секунды. Спасибо всем за то, что заставили меня взглянуть на это по-другому.

MySQL не индексирует подзапросы. Если вы хотите, чтобы индексы повышали производительность ваших запросов, перепишите их, чтобы не использовать подзапросы.

Попробуйте изменить порядок таблиц в исходном запросе:

 SELECT tag, count(tag) AS total 
FROM requests 
INNER JOIN tags ON requests.ASIN=tags.ASIN 
WHERE user_id=9 
GROUP BY tag 
ORDER BY total DESC
 

АГА! Разные кодировки и параметры сортировки. Я исправлю это и попробую еще раз!

Позже:

Вот и все. Запрос снизился с 10 секунд до 0,006 секунды. Спасибо всем за то, что заставили меня взглянуть на это по-другому.