Почему самосоединение выполняется быстрее, чем или?

Я пытаюсь отфильтровать таблицу отношений, чтобы получить подмножество таблицы, в которой выполняются два условия (т. Е. Я хочу, чтобы все идентификаторы записей, у которых color_ids, были 1 или 2). Это мощная таблица, поэтому я пытаюсь максимально ее оптимизировать.

Мне было интересно, может ли кто-нибудь объяснить мой вывод в этом случае:

Почему

SELECT DISTINCT a.id 
  FROM RelationshipTable as a 
  JOIN RelationshipTable as b ON b.id = a.id 
 WHERE a.color_id = 1 
   AND b.color_id = 2;

быстрее, чем

SELECT DISTINCT id 
  FROM RelationshipTable 
 WHERE color_id = 1 
    OR color_id = 2;

в MySql 4.1?


mysql sql query-optimization
person matthewdunnam    schedule 20.10.2010    source источник
comment
Вы EXPLAIN выполнили два запроса?   -  person Ian Henry    schedule 20.10.2010
comment
Являются ли color_id и size_id одним и тем же? Первый дважды запрашивает color_id, а второй запрашивает color_id и size_id.   -  person Joel Etherton    schedule 20.10.2010
comment
Есть ли опечатка между ними, потому что это похоже на сравнение яблок с апельсинами...   -  person OMG Ponies    schedule 20.10.2010
comment
Да, запросы не совпадают, один ссылается на size_id = 2, а другой ссылается на color_id = 2.   -  person Ken Richards    schedule 20.10.2010
comment
Является ли AND b.color_id = 2 по сравнению с OR size_id = 2 опечаткой? Если нет, то это может быть причиной.   -  person TToni    schedule 20.10.2010
comment
@Matt: я думаю, что лучший способ ускорить этот запрос — добавить индекс в RelationshipTable.color_id. Если у color_id не так много уникальных значений, вы можете создать индекс (color_id, id). Делитесь и наслаждайтесь.   -  person Bob Jarvis - Reinstate Monica    schedule 20.10.2010
comment
@Matt: Вы заявляете, например: я хочу, чтобы все идентификаторы записей, у которых color_id были равны 1 или 2. Первый запрос не достигает этого, если только это не совпадение. Первые запросы возвращают запись только в том случае, если 1) color_id равен 1 (или 2) и 2) если существует другая запись с тем же идентификатором, а color_id равен 2 (или 1).   -  person mbeckish    schedule 20.10.2010
comment
@mbeckish - разве это не одно и то же? Конечным набором будут все идентификаторы, у которых color_id равен 1 или 2, повторяя повторяющиеся идентификаторы.   -  person matthewdunnam    schedule 20.10.2010
comment
@Matt - если у вас есть только 1 запись с заданным идентификатором, она никогда не появится, независимо от color_id. Если у вас есть 2 записи с одинаковым идентификатором, и они обе имеют color_id = 1, ни одна запись не будет в вашем наборе результатов. и т.д.   -  person mbeckish    schedule 20.10.2010

Ответы (2)


arrow_upward
2
arrow_downward

Эти два запроса не являются одним и тем же и не должны давать один и тот же набор результатов. В первом запросе вам нужны все записи, которые соответствуют обоим условиям, у вас есть запись с color_id = 1 и запись с color_id 2 для одного и того же идентификатора. Во втором запросе вы получите все записи, которые имеют оба идентификатора цвета, и все записи, которые имеют только один или другой идентификатор. Конечно, поскольку вы просите вернуть другое поле, вы можете этого не увидеть. И второй запрос в любом случае несколько глуп, так как его можно выразить так:

select 1 as color id 
union all
select 2

И вообще никогда не бить по столу. Это сделало бы его супер быстрым.

person HLGEM    schedule 20.10.2010

arrow_upward
2
arrow_downward

Первый запрос невозможен и никогда не вернет набор результатов. По сути, это говорит: «Дайте мне все записи в таблице, где color_id равен 1 И color_id равен 2», чего никогда не может быть.

Если вы хотите спросить разницу между

SELECT DISTINCT a.id 
  FROM RelationshipTable as a 
  JOIN RelationshipTable as b ON b.id = a.id 
 WHERE a.color_id = 1 
   OR b.color_id = 2;

против

SELECT DISTINCT color_id 
  FROM RelationshipTable 
 WHERE color_id = 1 
    OR color_id = 2;

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

person methodin    schedule 20.10.2010
comment
Это неправильно. Я забыл упомянуть, что id НЕ является уникальным ключом. Поэтому я собираюсь получить все итерации комбинаций повторяющихся идентификаторов, создав таблицу, в которой возможно неидентичное значение color_id. - person matthewdunnam; 20.10.2010