Медленная оптимизация запросов IN() MySQL

У меня есть следующий SQL-запрос, который отлично работает, но очень медленно обрабатывается (от 3 до 5 секунд). Я создал индексы для столбцов «slug» и «checksum», но, поскольку предложение IN проходит через 5000–10000 строк, этого недостаточно, чтобы сделать его быстрым. Я читал, что есть способ улучшить его с помощью временных таблиц и/или объединений, но я не могу найти способ заставить его работать.

Механизм БД - InnoDB на MySQL. Любая помощь могла бы быть полезна.

SELECT name AS personName, 
       slug AS personSlug, 
       COUNT(slug) AS personCount 
  FROM person
 WHERE checksum IN
         ( SELECT checksum 
             FROM person 
            WHERE slug = 'john-doe' )  
   AND NOT (slug = 'john-doe')
 GROUP BY personName 
 ORDER BY personCount DESC

mysql sql database
person Anthony    schedule 09.06.2014    source источник
comment
Кстати, оператор не-равно x <> y был бы здесь немного более традиционен, чем NOT (x = y)   -  person Michael Berkowski    schedule 09.06.2014

Ответы (3)


arrow_upward
1
arrow_downward

Я не совсем понимаю, что вы пытаетесь сделать, не видя некоторых примеров данных. Но похоже, что вы пытаетесь найти все контрольные суммы, которые соответствуют контрольным суммам, связанным с 'john-doe', но не имеют slug = 'john-doe', поэтому ищите какие-то дубликаты.

Следующее самосоединение должно сделать это за вас.

SELECT
    p.name AS personName,
    p.slug AS personSlug,
    COUNT(p.slug) AS personCount
FROM
    person AS p
INNER JOIN
    person AS p2
ON
    p.checksum = p2.checksum
WHERE
    p2.slug = 'john-doe'
    AND p.slug <> 'john-doe'
GROUP BY personName
ORDER BY personCount DESC
person Mike Brant    schedule 09.06.2014
comment
Большое спасибо. Это сократило время выполнения с 3-5 секунд до 300 мс! - person Anthony; 10.06.2014
comment
@ Энтони Вы пробовали ответ Гордона Линоффа? Обычно EXISTS даже быстрее, потому что это короткое замыкание. - person plalx; 10.06.2014
comment
@plax Я попробовал ответ Гордона, но это не улучшило производительность. - person Anthony; 10.06.2014
comment
@МайкБрант. . . Я предполагаю, что на slug есть индекс, и фильтрация довольно сильная. Это, вероятно, делает эту версию более эффективной. В моей версии это невозможно, потому что фильтрация = выполняется в подзапросе, а не во внешнем запросе. - person Gordon Linoff; 10.06.2014

arrow_upward
1
arrow_downward

Часто изменение его на not exists помогает производительности:

SELECT name AS personName, slug AS personSlug, COUNT(slug) AS personCount
FROM person p
WHERE EXISTS (SELECT 1
              from person p2
              WHERE p2.slug = 'john-doe' and p2.checksum = p.checksum
             ) AND
     NOT (slug = 'john-doe')
GROUP BY personName
ORDER BY personCount DESC;

Для производительности вам нужен индекс person(checksum, slug).

person Gordon Linoff    schedule 09.06.2014
comment
Насколько я понимаю, вы изменили смысл запроса? Вы имели в виду WHERE EXISTS (SELECT 1 FROM person WHERE slug = 'john-doe' AND checksum = p.checksum) AND slug <> 'john-doe'? - person plalx; 10.06.2014
comment
@plaix . . . Да. Вы правы, должно быть exists, а не not exists. - person Gordon Linoff; 10.06.2014
comment
Спасибо за ответ Гордон. К сожалению, это не помогло улучшить производительность (такую ​​же или неожиданно худшую). - person Anthony; 10.06.2014
comment
У вас есть индекс checksum, slug ? - person Gordon Linoff; 10.06.2014
comment
У меня есть индекс контрольной суммы, slug. Я трижды проверил ваше решение, но оно по-прежнему требует столько же или даже больше времени, чем мой первоначальный запрос. - person Anthony; 10.06.2014

arrow_upward
0
arrow_downward

По моему опыту, LEFT JOIN быстрее, чем предложение IN и любой подзапрос.

SELECT p1.name AS personName, p1.slug AS personSlug, COUNT(p1.slug) AS personCount 
FROM person p1
LEFT JOIN person p2 on p1.checksum=p2.checksum and p2.slug = 'john-doe'
WHERE p1.slug != 'john-doe'
AND p2.slug is NOT NULL
GROUP BY personName 
ORDER BY personCount DESC

это примерно означает: «Я хочу получить имя и слаг людей, чей слаг не« john-doe », для которых есть хотя бы одна строка с той же контрольной суммой, что и slug« john-doe »».

Если вы хотите найти записи, для которых нет соответствующей записи «john-doe», вам просто нужно изменить одно предложение:

SELECT p1.name AS personName, p1.slug AS personSlug, COUNT(p1.slug) AS personCount 
FROM person p1
LEFT JOIN person p2 on p1.checksum=p2.checksum and p2.slug = 'john-doe'
WHERE p1.slug != 'john-doe'
AND p2.slug iS NULL
GROUP BY personName 
ORDER BY personCount DESC
person ffflabs    schedule 09.06.2014
comment
Большое спасибо за ответ. Он отлично работает. INNER JOIN (см. Ответ Майка) также работает, и из того, что я смог проверить, производительность аналогична. Но, может быть, LEFT JOIN лучше? - person Anthony; 10.06.2014
comment
Я использовал LEFT JOIN, потому что не понимал, что вам нужно получить. Если вы хотите найти несовпадающие контрольные суммы, то LEFT JOIN позволяет это сделать (отсюда и мой второй запрос). Различия в производительности между INNER JOIN и LEFT JOIN, как правило, незначительны для небольших таблиц и согласованных индексов. Если вы работаете с более чем 100 000 записей, возможно, вы захотите начать бенчмаркинг обоих. - person ffflabs; 10.06.2014