MySQL - как использовать индекс в WHERE x IN (<подзапрос>)
Я использую этот запрос, чтобы получить всех сотрудников {клиентов с именами, начинающимися со строчной буквы "a"}:
SELECT * FROM employees
WHERE client_id IN (SELECT id FROM clients WHERE name LIKE 'a%')
Столбец employees.client_id представляет собой int с INDEX client_id (index_id). Подзапрос должен ИМХО возвращать список идентификаторов, который затем используется в предложении WHERE.
Когда я EXPLAIN запрос, первичный запрос не использует индексы (type:ALL). Но когда я EXPLAIN
список взят из подзапроса (например, SELECT ... WHERE client_id IN (121,184,501)), EXPLAIN переключается на type:range, и этот запрос выполняется быстрее на 50%.
Как я могу заставить запрос использовать индекс для данных, возвращаемых подзапросом, или есть более эффективный способ получения этих данных? (Получение списка идентификаторов на сервер приложений, присоединение к нему и отправка второго запроса здесь еще дороже).
Заранее спасибо.
Ответы 6
SELECT employees.*
FROM employees, clients
WHERE employees.client_id = clients.id
AND clients.name LIKE 'a%';
Должно быть быстрее, так как оптимизатор может выбрать наиболее эффективный план. Написав его по-своему с подзапросом, вы заставляете его выполнять шаги в определенном порядке, а не позволяете ему выбирать оптимальный порядок соединения.
Как правило, следует избегать подзапросов, поскольку они, как правило, будут менее производительными, чем запрос соединения (хотя есть определенные обстоятельства, при которых они неизбежны)
Я видел случаи, когда оптимизатор запросов ошибался, а подзапрос для возврата идентификаторов загружался быстрее. Но это был действительно конкретный случай. См .: benlumley.co.uk/2008/06/25/mysql-query-optimiser-left-lackin g, если вас интересуют подробности.
Вы пробовали сделать это с JOIN, а не с подзапросом?
SELECT employees.* FROM employees, clients WHERE employees.client_id = clients.id AND clients.name LIKE 'a%';
Стоит отметить, что соединения работают лучше, чем подзапросы, не для всех существующих СУБД. Хотя это точно подходит для MySQL.
select * from X as _x where
exists(select * from Y as _y where _y.someField = _x.someField)
Должен помочь вам;)
SELECT e.*
FROM employees e
WHERE EXISTS (
SELECT 1
FROM clients c
WHERE c.id = e.client_id
AND c.name LIKE 'a%'
)
Вы можете переписать запрос, используя СУЩЕСТВУЕТ. В MySQL это определенно дает улучшение производительности. Для получения дополнительной помощи по оптимизации вы можете обратиться к: MySQL-в-запрос-оптимизация
Я не думаю, что использование каких-либо подзапросов является улучшением MySQL.
Для конкретного объяснения почему
SELECT * FROM employees WHERE client_id IN (SELECT id FROM clients WHERE name LIKE 'a%')
медленнее, чем
SELECT * FROM employees WHERE client_id IN (1,2,3,4)
Ознакомьтесь с этой частью руководства MySQL, особенно с третьей точкой: http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html. Также этот отчет об ошибке.
Также можно использовать синтаксис INNER JOIN.