#mysql #sql
#mysql #sql
Вопрос:
Я целую вечность искал, как выполнить подобный запрос, но использовать индекс.
SELECT * FROM aliens_tmp
WHERE creator != 'a'
AND COUNTRY = 'UK'
ORDER BY id DESC LIMIT 0, 10
Какой бы индекс я ни создал, он не получит попадания из-за !=, противоречащего инклюзивному характеру индексов.
Итак, я подумал о том, чтобы сделать минус, где я делаю что-то вроде
SELECT * FROM aliens_tmp
WHERE COUNTRY = 'UK
MINUS
SELECT * FROM aliens_tmp
WHERE CREATOR = 'a'
ORDER BY id DESC LIMIT 0, 10
…но МИНУСА не существует. Итак, я посмотрел и решил, что могу использовать старый трюк, упомянутый на http://www.bitbybit.dk/carsten/blog/?p=71 что вкратце таково
SELECT DISTINCT a.member_id, a.name
FROM a LEFT JOIN b USING (member_id, name)
WHERE b.member_id IS NULL
но это не помогает мне с первоначальной целью сделать МИНУС, сохраняя при этом использование индекса, поскольку любое соединение с моей собственной таблицей по-прежнему должно исключать моего создателя, что приводит к пропуску из-за исключения каких-либо идей?
Комментарии:
1. Какой индекс вы пытаетесь достичь? Это касается создателя или страны или обоих?
2. Сколько разных значений может иметь creator? И сколько строк из общего числа удовлетворяют условию creator = ‘a’?
3. @James. Я бы, вероятно, использовал какой-то составной индекс, основанный на выбранных ключах
4. @piotrm в настоящее время существует более 60 тысяч создателей. В странах около 20
Ответ №1:
Если большая часть вашей таблицы состоит из строк с creator != 'a' , то использование индекса не даст вам никакого преимущества. Однако, если у вас в основном строки с creator='a' , вы можете рассмотреть возможность присвоения creator таких значений (или использования enum), которые при заказе по creator ‘a’ шли бы первыми. Вместо проверки на creator != 'a' вы могли бы затем использовать creator > 'a' условие, которое идеально подходит для использования индексов btree.
ОБНОВЛЕНИЕ после получения дополнительной информации о creator:
Таким образом, ваше creator != 'a' условие имеет чрезвычайно низкую избирательность, и попытка использовать index в creator бессмысленна. Вы ограничиваете свои результаты и упорядочиваете по идентификатору desc, поэтому ключ в country следует использовать для возврата как можно меньшего количества строк, просто проверяя каждую строку, начиная с самого высокого идентификатора, удовлетворяет ли она условиям where, пока не будет достаточно для вашего предела. Если вы видите key: country и количество строк, близких к 10 в вашем explain select ... , это не ускорится.
Ответ №2:
В этом случае индекс вам ничего не даст, ваш исходный запрос с != будет вашим самым быстрым вариантом.
Причина в том, что для выполнения МИНУСА, о котором вы говорите, базе данных потребуется извлечь все строки, затем сверить каждую строку с индексом, чтобы определить, следует ли ее удалять. Это требует, чтобы он проверял каждую строку в таблице.
Ваш исходный запрос также должен будет проверять каждую строку, но вам нужно будет только проверить ее на указанное вами условие.
Это означает, что ваш исходный запрос был бы быстрее, даже если бы была MINUS операция, подобная описанной вами.
Фактически, ваш исходный запрос может быть быстрее, потому что ему нужно только проверить условие и не нужно будет выполнять поиск по индексу для каждой строки.
Ответ №3:
Индекс в creator, вероятно, был бы не очень полезен, даже если бы его можно было использовать. !=a, вероятно, вернет более половины строк в таблице. Сканирование таблицы в этом случае будет более эффективным, чем использование индекса. Индекс по стране МОЖЕТ помочь, предполагая, что стран много, но если половина ваших пользователей находится в Великобритании, у вас там тоже будет такая же проблема.
Вы могли бы рассмотреть возможность разделения ваших данных на две таблицы: «создатели» и «не создатели», если это имеет смысл для вашего приложения.