Вопрос по mysql, indexing – Использование индекса в MySQL JOIN с условием ИЛИ

9

Я запускаю запрос, который выглядит следующим образом

SELECT parent.field, child.field
FROM parent
JOIN child ON (child.id = parent.id 
    OR child.id = parent.otherid)

Это, однако, очень медленно (около 100 тыс. Записей и присоединение к другим таблицам в реальной версии), но несмотря на то, что пробовал индексы на

parent.id (PRIMARY),  
parent.otherid,  
child.id (PRIMARY), 
and a composite index of parent.id and parent.otherid

Я не могу заставить MySQL использовать любой из этих индексов при создании этого соединения.

Я читал, что MySQL может использовать только один индекс на соединение, но нигде не может найти, может ли он использовать составной индекс, когда JOIN содержит условие ИЛИ.

Кто-нибудь здесь знает, возможно ли сделать этот запрос ссылкой на индекс? Если так, то как?

МОЕ РЕШЕНИЕ

(ТАК не позволю мне ответить на мой вопрос ниже)

Куча настроек и придумали довольно приличное решение, которое сохраняет способность присоединяться и объединять другие таблицы.

SELECT parent.field, child.field
FROM parent
JOIN (
    SELECT parent.id as parentid, 
    # Prevents the need to union
    IF(NOT ISNULL(parent.otherid) AND parent.otherid <> parent.id, 
       parent.otherid, 
       parent.id) as getdataforid
    FROM parent
    WHERE (condition)
) as foundrecords
    ON foundrecords.parentid = parent.id
JOIN child ON child.id = parent.getdataforid

Для скорости требуется условие внутри подзапроса, чтобы уменьшить количество записей, помещаемых во временную таблицу, но у меня есть тонны дополнительных объединений во внешнем запросе, некоторые присоединяются к дочернему, а некоторые - к родительскому (с некоторыми агрегатами), поэтому этот работал лучше всего для меня.

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

Спасибо Джирке за предложение UNION ALL, именно это побудило меня попасть сюда :)

Ты бежалexplain select ... на запрос? juergen d
да, объяснение - это то, что позволяет мне знать, что он не использует никаких индексов для этого соединения. Bob Davies

Ваш Ответ

2   ответа
0
EXPLAIN 
SELECT parent.fld, child.fld 
  FROM parent JOIN child ON child.id = parent.id  
 UNION ALL 
SELECT parent.fld, child.fld
  FROM parent JOIN child ON child.id = parent.otherid
   AND parent.otherid <> parent.id

id  select_type   TABLE       TYPE    possible_keys  KEY      key_len  ref                  ROWS  Extra
1   PRIMARY       parent      ALL     PRIMARY                                               9999
1   PRIMARY       child       eq_ref  PRIMARY        PRIMARY  4        test.parent.id       1
2   UNION         parent      ALL     parent_ix1                                            9999  USING WHERE
2   UNION         child       eq_ref  PRIMARY        PRIMARY  4        test.parent.otherid  1
    UNION RESULT  <union1,2>  ALL

с таблицами, использующими движок InnoDB:

id  select_type   table       type    possible_keys  key         key_len  ref            rows  Extra
1   PRIMARY       child       ALL     PRIMARY                                            9903
1   PRIMARY       parent      eq_ref  PRIMARY        PRIMARY     4        test.child.id  1
2   UNION         child       ALL     PRIMARY                                            9903
2   UNION         parent      ref     parent_ix1     parent_ix1  5        test.child.id  1     Using where
    UNION RESULT  <union1,2>  ALL   
7

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

Затем следующее дает тот же результат, используя отдельные индексы, по одному индексу на столбец.

SELECT parent.field, child.field
FROM parent
JOIN child ON child.id = parent.id 

UNION ALL

SELECT parent.field, child.field
FROM parent
JOIN child ON child.id = parent.otherid
@BobDavies - можно. Вставить это как подзапрос.
Это превосходное решение в упрощенном случае (спасибо), но оно предотвращает агрегацию данных в дочерних (используя GROUP_CONCAT (DISTINCT child.field)). Играя с идеей, чтобы посмотреть, смогу ли я применить ее для ускорения подобных запросов, но без агрегирования. Спасибо Bob Davies
+1. что бы я ни предложил, разбить его на два отдельных запроса и объединить результаты, хотя этот запрос МОЖЕТ вернуть больше строк, чем исходный запрос. (Рассмотрим строку в parent, где id = otherid). Чтобы гарантировать те же результаты, добавьте предикатAND parent.otherid <> parent.id на этот второй запрос. (NULL не являются проблемой в этом тесте, так как parent.id не равен NULL в силу того, что он является ПЕРВИЧНЫМ КЛЮЧОМ.)

Похожие вопросы