Как увеличить скорость SELECT, где нужно получить все строки, кроме одной?

Question

[maksam07]

Добрый день. Имеется запрос на вордпрессе следующего характера:

SELECT ID, user_login
FROM users
WHERE ID != '81306'
ORDER BY user_login ASC

Запрос написан плагином и лезть в него я не хочу. Но делаю небольшие оптимизации по БД, на сколько хватает знаний, но вот тут я застопорился. Индексы на id и user_login прописаны по отдельности. Время выполнения запроса не сильно критичное, но тем не менее - 0,1625
Можно как-то ускорить или придется жить с таким плагином?

Comments

[Ипатьев]

А скорость запроса без where разве не такая же?
и сколько, собственно, записей в таблице?

[Akina]

Нужен композитный индекс по (user_login, id). Любой другой индекс либо будет проигнорирован сервером, либо только замедлит выполнение запроса.

Время выполнения запроса не сильно критичное, но тем не менее - 0,1625

А какое время передачи набора на клиента? небось больше, чем выполнения запроса... тогда на оптимизацию запроса следует вообще воскласть.

[Ипатьев]

Akina, а вроде же праймари незримо присутствует во всех остальных индексах? Это здесь не сработает?

[Akina]

Роман Юрьевич Ипатьев, а вот тут очень забавная (и мне непонятная) вещь - несмотря на присутствие выражения первичного ключа в любом индексе, значения полей оттуда не экстрагируются, выражение используется исключительно для получения местоположения записи в кластерном индексе по записи в сканируемом индексе.

То есть когда документация говорит

In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

то на самом деле имеется в виду "InnoDB uses this primary key value to search for the row in the clustered index only." Когда-то давно, ещё в версии, вроде бы, 5.6, я даже не поверил и проводил специальный проверочный эксперимент, который подтвердил этот бред...

[ThunderCat]

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

Answer 1

[Егор Казанцев]

Индекс по user_login сделай - ИМХО сортировка по полю тормозит

Comments

[maksam07]

Указывал же, что прописан индекс :) Поэтому и застопорился, потому что не знаю куда дальше копать. Составной индекс может нужен? Вот в составном не уверен, не так хорошо знаю о нем

[Егор Казанцев]

EXPAIN что говорит?

[maksam07]

Егор Казанцев, на ответ ThunderCat дал больше информации

Answer 2

[ThunderCat]

для начала explain, понять что и как, потом смотреть в сторону составных индексов. Еще слегка удивляет отсутствие limit, в таком ключе у вас выбирается все содержимое таблицы, что не может не сказаться на скорости выборки. Так же длина ключа по (очевидно) текстовому полю (укажите тип кстати, надеюсь там ограниченный варчар, а ни лонгтекст или блоб...) должна хорошо покрывать возможную длину логина.

Comments

[maksam07]

Результат explain

"limit" - к сожалению, так написан плагин. Там выпадающий список со всеми пользователями, что максимально не оптимизировано, учитывая большую нагрузку на браузер при отрисовке списка. Надеюсь в будущем это исправят, но на текущий момент хочется узнать, можно ли как-то такой вариант ускорить

ID bigint(20) unsigned Auto Increment
user_login varchar(60)

По длине индексов ограничений нет
Если вы про то, зачем кавычки для числа - не знаю, не я эти запросы писал
В таблице всего чуть больше 80к строк

[Ипатьев]

странно, а почему тогда пишет что строк 39к

ну попробуйте зафорсить индекс, force_index()

но вообще конечно всех этих страдальцев с "плагинами от вордпресс" запереть бы в какую-нибудь резервацию, пусть там сами это нюхают.

[Ипатьев]

и, главное - да, непонятно, при чем здесь ускорение в сотые доли секунды, если основная нагрузка все равно при передаче этой кучи дерьма в браузер и отрисовка

[maksam07]

Роман Юрьевич Ипатьев,

странно, а почему тогда пишет что строк 39к

наверное на таком количестве нашло единственный ид, который нужно исключить и дальше уже ничего не искало

и, главное - да, непонятно, при чем здесь ускорение в сотые доли секунды, если основная нагрузка все равно при передаче этой кучи дерьма в браузер и отрисовка

не думаю, что исходя из этого не нужно оптимизировать БД

[Ипатьев]

Я бы скорее предположил что идея про 80к записей исходит из айди 81306
ведь айди в базе - это же ведь номер по порядку, правда?

не думаю, что исходя из этого не нужно оптимизировать БД

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

[maksam07]

Роман Юрьевич Ипатьев,

Я бы скорее предположил что идея про 80к записей исходит из айди 81306

но строки могут удаляться и буквально после первого удаление последний ид уже не будет равен количеству строк. Удалением особо не балуемся, а с какого-то времени так вообще перестали это делать, поэтому количество почти равное последнему айдишнику

отличная логика.

я уже писал, что все косяки в этом плане написаны авторами плагина. Плагин сменить нет возможности, это крупный обширный проект. Только ждать пока они сами все эти косяки исправят. Или при каждом обновлении плагина мне каждый раз вставлять туда свой код

[Ипатьев]

Или при каждом обновлении плагина мне каждый раз вставлять туда свой код

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

В общем решение проблемы с запросом в общем виде это поменять идиотский бигинт на инт, сделать составной индекс и заставить БД применять его насильно, если сама не хочет.
Но на исходную проблему с плагином это вообще никак не повлияет.

[BorLaze]

и, главное - да, непонятно, при чем здесь ускорение в сотые доли секунды, если основная нагрузка все равно при передаче этой кучи дерьма в браузер и отрисовка

не думаю, что исходя из этого не нужно оптимизировать БД

а стоило бы подумать

когда запрос выполняется 100к раз в цикле - тогда да, даже миллисекунда оптимизации даст эффект

но если от запроса до отрисовки страницы в браузере проходит 5 секунд, и из них сам запрос занимает 0.16 сек... ну, представь сам - запрос отрабатывает вообще мгновенно, за 0 времени.
сильно заметно будет, отобразилась страница через 5 сек или через 4.84?

[ThunderCat]

Максим Компаниец, Во первых надо написать автору плагина что он тупой чудак,

Во вторых - если у вас база не предназначена для переписи населения земли, заменить бигинт на ансигн инт, думаю 4+ миллиарда записей у вас накопятся очень не скоро.

В третьих - посмотреть насколько длинные логины у вас в базе предусмотрены, и задать варчар поменьше, так индекс будет работать быстрее.

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

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