Почему запрос к MySQL с Front-End в разы медленнее прямого запроса?

Question

[Пашенька]

Есть таблица на овер 500k записей.
Почему запрос сделанный в phpMyAdmin выполняется за доли секунды, а отправленный с фронтэнда средствами JQuery Autocomplete UI, возвращается через несколько секунд?

$title = '%' . $_GET['term'] . '%';
$cpt1 = 'type1';
$cpt2 = 'typ2';

   
$sql = $wpdb->prepare("
		(SELECT ID, post_title, post_type FROM wc_posts WHERE (post_title LIKE %s) AND (post_type = %s) LIMIT 5)
		UNION
		(SELECT ID, post_title, post_type FROM wc_posts WHERE (post_title LIKE %s) AND (post_type = %s) LIMIT 5)
		ORDER BY post_type DESC, post_title", $title, $cpt1, $title, $cpt2);
   
$data = $wpdb->get_results( $sql );

Answer 1

[Дмитрий Энтелис]

1. Не очень понятно если честно что Вы подразумеваете под "возвращается через несколько секунд", в коде не понятно как Вы это меряли.
Мне кажется что вероятней всего дело не в SQL:
При "отправке с phpmyadmin", phpmyadmin выводит чистое время работы SQL запроса.
При "отправке с фронтенда" - Вы видите итоговое время всего HTTP запроса, включая: dns поиск, установление соединения до вашего хоста, запуск вашего php фреймворка и его работу, получение данных и их отдача. Промониторьте все эти значения.

На всякий случай если дело все таки в SQL:

2. У Вас могут быть включены всяко-разные кеши, соответственно при повторном вводе запроса через phpmyadmin он отдается быстрее. Можно попробовать отключить кеши и промониторить ситуацию заново.

3. Покажите explain запроса

Comments

[Пашенька]

Дмитрий Энтелис
1. Чисто визуально "мерял", т.к. запрос аяксовый. Время от момента окончания ввода запроса в инпут до момента вывода соответствий на экран.
Я понимаю из чего складывается итоговое время запроса при "отправке с фронтенда")

Дело всё-таки оказывается именно в SQL: те, доли секунды про которые я говорил, отнимают коротенькие запросы(например в 2 символа). Т.е. быстро находятся все мои 10 соответствий где-то относительно в начале таблицы т.к. LIMIT достигнут.
Запросы же подлиннее, на которые нет 10 соответствий в таблице, очевидно, проходят по всей таблице и в том же phpmyadmin занимают 6-7 секунд!
Так что, вот она проблема. Как оптимальнее перестроить запрос? Или что вообще делать?

Прилагаю EXPLAIN

*************** row 1 ***************
id: 1
select_type: PRIMARY
table: showme_posts
type: ref
possible_keys: type_status_date
key: type_status_date
key_len: 62
ref: const
rows: 62700
Extra: Using index condition; Using where

*************** row 2 ***************
id: 2
select_type: UNION
table: showme_posts
type: ref
possible_keys: type_status_date
key: type_status_date
key_len: 62
ref: const
rows: 234474
Extra: Using index condition; Using where

*************** row 3 ***************
id: NULL
select_type: UNION RESULT
table: <union1,2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Using temporary; Using filesort

[Дмитрий Энтелис]

like-a-boss:
1. Добавьте ключ (post_title, post_type)
2. Попробуйте делать 2 запроса без union, склеивать данные в php

[Игорь Воротнёв]

like-a-boss: А не пробовали через WP_Query, модифицируя через хуки? Условия запроса вроде бы достаточно простые, не вижу необходимости в кастомном запросе. Не гарантирую, что это сразу решит проблему, но в теории - вполне может. Попробовать не мешает.

[Пашенька]

Дмитрий Энтелис
Можно подробнее про первый пункт?
По отдельности запросы дают время меньшее примерно на секунду, что тоже не быстро :(

[Пашенька]

Игорь Воротнёв нет, не пробовал. Всёравно же нужно будет sql запрос писать, иначе как?

[Дмитрий Энтелис]

like-a-boss: какие есть индексы в этой таблице?

[Игорь Воротнёв]

like-a-boss: нет, не нужно.
https://codex.wordpress.org/Class_Reference/WP_Query
https://codex.wordpress.org/Custom_Queries
https://codex.wordpress.org/Function_Reference/set...
https://codex.wordpress.org/Plugin_API/Action_Refe...
https://developer.wordpress.org/reference/hooks/query/
https://developer.wordpress.org/reference/hooks/po...
https://developer.wordpress.org/reference/hooks/po...
https://developer.wordpress.org/reference/hooks/po...
https://developer.wordpress.org/reference/hooks/po...
https://developer.wordpress.org/reference/hooks/po...
ну и так далее. фильтров и экшнов для модификации и контроля стандартного объекта WP_Query - масса. Но, опять же, для начала посмотрите сам класс WP_Query, думаю из кода будет понятно, как у них реализованы запросы, кеширование и т.д. Возможно, использование стандартного объекта поможет. Возможно нет. Одно могу сказать точно - запрос у вас достаточно простой, размер БД крохотный. Таких тормозов не должно быть и близко.
Попробуйте повесить тот же запрос не на ajax, а на страницу в виде обычного запроса, посмотрите как он справляется. Да и подебажить с помощью того же Query Monitor плагина будет легче, чем аякс. Возможно, проблема вообще в самом Autocomplete и его придется сменить на Select2 или другую альтернативу. В общем, ставьте Query Monitor и начинайте дебажить. Где-то точно есть узкое место. Если запрос в PHPMyAdmin выполняется быстро, он должен точно так же быстро выполняться и из-под WP, оверхед будет уже на других запросах, работа самого WP и тд, но это не тот оверхед, который у вас получается.

[Пашенька]

Дмитрий Энтелис
ID - уникальный
post_type - первичный уникальный, впрочем вот:

[Пашенька]

Игорь Воротнёв я бы не сказал, что таблица в 500 000 записей - это крохотная таблица)
Дело всё-таки именно в sql части. На самом деле те запросы, которые якобы быстро выполнялись, кешировались :) В phpMyAdmin, выполнение вышеприведённого мной запроса(или хотя бы его половины), затягивается на секунды

[Дмитрий Энтелис]

like-a-boss: не увидел списка индексов на скриншоте.
покажите show create table пожалуйста

[Пашенька]

Дмитрий Энтелис ньюби, уж простите)
Вот

CREATE TABLE `showme_posts` (
 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
 `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_content` longtext NOT NULL,
 `post_title` text NOT NULL,
 `post_excerpt` text NOT NULL,
 `post_status` varchar(20) NOT NULL DEFAULT 'publish',
 `comment_status` varchar(20) NOT NULL DEFAULT 'open',
 `ping_status` varchar(20) NOT NULL DEFAULT 'open',
 `post_password` varchar(20) NOT NULL DEFAULT '',
 `post_name` varchar(200) NOT NULL DEFAULT '',
 `to_ping` text NOT NULL,
 `pinged` text NOT NULL,
 `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_content_filtered` longtext NOT NULL,
 `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
 `guid` varchar(255) NOT NULL DEFAULT '',
 `menu_order` int(11) NOT NULL DEFAULT '0',
 `post_type` varchar(20) NOT NULL DEFAULT 'post',
 `post_mime_type` varchar(100) NOT NULL DEFAULT '',
 `comment_count` bigint(20) NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID`),
 KEY `post_name` (`post_name`),
 KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
 KEY `post_parent` (`post_parent`),
 KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=522366 DEFAULT CHARSET=utf8

[Дмитрий Энтелис]

like-a-boss: смотрите, вы делаете фильтрацию по полю post_title но при этом индекса по этому полю нет, да еще и тип поля text вдобавок, да еще и запрос вида "%abc%".

Возможные варианты решения:
а) добавить full text index, использовать MATCH вместо LIKE
б) заменить тип поля на varchar, добавить составной индекс post_title,post_type , LIKE переделать на "abc%"

[Пашенька]

Дмитрий Энтелис я выберу первый вариант, мне нужно искать именно %abc%
Теперь вопрос как это сделать)

CREATE FULLTEXT INDEX post_type_post_title ON showme_posts (post_type, post_title)

верно ли?

[Пашенька]

Дмитрий Энтелис говорит, что таблица не поддерживает fulltext индексы

[Пашенька]

Дмитрий Энтелис Я тут немного поэкспериментировал: поскольку для моей InnoDB на локалке не добавился FULLTEXT Index(попробовал на хостинге - там получилось(и, кстати, чисто визуально аякс поиск заработал быстрее)), я эту таблицу перевёл в MyISAM и сразу получил в разы быстрейший поиск. Затем добавил FULLTEXT Индекс, а после переписал запрос на MATCH. Результаты запросов:

-------------------------- запрос 3 ---------------------------------
InnoDB | MyISAM | MyISAM_FULLTEXT | MyISAM_FT_MATCH
6.0870 | 1.4575 |     1.4636      |      1.3553 

-------------------------- запрос 2 ---------------------------------
InnoDB | MyISAM | MyISAM_FULLTEXT | MyISAM_FT_MATCH
6.0486 | 1.5553 |     1.5511      |      1.3559

-------------------------- запрос 1 ---------------------------------
InnoDB | MyISAM | MyISAM_FULLTEXT | MyISAM_FT_MATCH
3.4546 | 0.1335 |     0.1317      |      0.0005

Получилось почти хорошо:) В связи с этим ряд вопросов: Каковы последствия перехода на MyISAM? Может Все таблицы сделать MyISAM? Почему разработчики Wordpress выбрали InnoDB?

И ещё: насколько адекватно я переписал свой запрос? Ищёт он, то ищет, но у меня сомнения насчёт оптимизации/правильности

SELECT ID, post_title, post_type FROM showme_posts WHERE 
(MATCH (post_type,post_title) AGAINST ('%my_search_query%')) AND (post_type = 'type_1')
OR 
(MATCH (post_type,post_title) AGAINST ('%my_search_query%')) AND (post_type = 'type_2')
LIMIT 5

[Дмитрий Энтелис]

like-a-boss: myisam в целом быстрее работает, но при вставках строк блокируется вся таблица. В innodb блокируется только строка + есть транзакции, итд. Сейчас засыпаю уже, по остальным вопросам завтра отвечу)

[Пашенька]

Дмитрий Энтелис Жду ответа, как соловей лета:)
Всё таки запрос у меня бредовый и ни черта не рабочий) Как минимум проценты ничего не решают и лимит распространяется на весь запрос, а не на его части.
Я так понял, что при полнотекстовом поиске возможен поиск только полных слов, как быть мне в случае, когда мне нужно находить все вхождения в слова?

[Пашенька]

Игорь Воротнёв спасибо за подсказанный плагин. Я вобщем-то знаю о существовании таких плагинов и пользовал даже парочку из них, но этот прям вобще хорош, не зря у него чистая пятёра в репозитории.
С его помощью эксперементально выяснил что запрос через get_posts() В РАЗЫ быстрее запроса через new WP_Query() с одними и теми же аргументами, хотя по идее разницы в их производительности быть не должно. Пойду дальше переписывать запросы)

[Игорь Воротнёв]

like-a-boss: этот плагин - это must-have) По get_posts vs WP_Query - вы имеете в виду все время, включая выполнение всех остальных операций, связанных с этими запросами, или чисто выполнение SQL? Функция get_posts это обертка, она парсит параметры и делает new WP_Query. Возможно, разница по времени как раз из-за количества аргументов, и, соответственно, сложности запроса.
500к - уже не крохотная бд, тут я немного недосмотрел. У вас это один сервер, или БД отдельно? Для таких объемов не мешало бы выделить отдельный БД сервер и оптимайзить его исключительно под базу. Да и смена движка с MySQL на Percona, а еще лучше - MariaDB, вполне может ускорить. Тяжелые запросы будут всегда, смотрите еще какие из них можно кешировать (механизмы transients, wp_cache_set) и выполнять эти сложные выборки только раз в Х минут / часов.

[Пашенька]

Игорь Воротнёв Я имею ввиду сам SQL запрос, возникающий в результате вызова get_posts / new WP_Query, в частности это замечено при выводе нескольких последних записей: с get_post это занимает примерно 0.000Xs, а с new WP_Query 0.5s (!) Эта разница обусловлена подсчётом количества полученных строк, во втором случае
Одно плохо: get_posts() не дружит с пагинацией :( А это уже следствие того, что он не знает сколько строк получил
500k - это таблица posts, в таблице relationships примерно миллион связей.
Да это вообще шаред хостинг за 2 бакса) Просто пока в данном случае нет необходимости в выделенном сервере. Но хочется чтобы всё работало по возможности быстро. Примечательно, что на шареде и моём Xeon'e с денвером(правда вроде не конфигурировал его даже) производительность по части сабжа одинаковая

[Игорь Воротнёв]

like-a-boss: да, это больное место запросов WP - SQL_CALC_FOUND_ROWS. Тут немного об этом, и погуглить можете - мне когда-то даже попадались решения. Функция get_posts как раз не использует это, из-за этого нет и пагинации. Эта функция для того и делалась - она идеально подходит, когда надо просто получить Х постов по нескольким параметрам. А для основных запросов (основа контента страницы) используется полноценный объект WP_Query, который корректно устанавливает все глобальные переменные, считает количество строк и тд. Пагинация - не единственный нюанс, там еще есть виджеты и прочие плюшки, которые могут быть завязаны на контекст основного запроса и глобальные переменные. Также с UNION / UNION DISTINCT кое-где есть проблемы, когда-то тут обсуждалось. В общем, с большими базами и сложными запросами всегда приходится маяться. Вам еще хорошо, что вы работаете с таблицей wp_posts, если залезете в сложные запросы на wp_postmeta, в которой значения ключей без индексов - вот там полная печаль с перебором всех строк. И там миллионы строк - обычное дело на более-менее большом сайте. Я, кстати, на всех крупных сайтах где нужен нормальный поиск или фильтрация по куче аргументов (в том числе postmeta) решил проблему на корню другим способом - поставил Elastic Search и все делаю через его индекс. Работает мгновенно, ищет очень хорошо - такой себе персональный гугл. Но тут уже без VPS не обойтись, на shared его не поставишь)

[Пашенька]

Игорь Воротнёв Выше я неверно изложил мысль о том, что get_posts() не знает количества полученых записей - знает конечно. Она не знает количества ВСЕХ записей соответствующих запросу, если мы явно укажем сколько записей нам нужно.
Насчёт виджетов - не страшно, у меня все страницы самописные и я почти не юзаю плагины. А вот что касается кол-ва рабочих таблиц, то основных - 4. У меня несколько типов записей, которые связаны между собой с помощью нескольких таксономий(что-то вроде иерархии получается) и мне необходимо в любом месте иметь возможность вывода зависимостей между этими записями, так что не всё так просто, но работает нормально, за исключанием двух страниц, где итоговое кол-во sql переваливает за тысячу

[Игорь Воротнёв]

Да, я в принципе понял суть, у меня подобна кухня в прошлом году была, большой проект с кучей кастомного контента и сложных связей. При чем не только таксономии, но и postmeta через Advanced Custom Fields Pro, связи между custom post types через плагин Post-to-Posts, и это еще и на кастомный фронтенд завязано, куча своих форм с кучей chained селектов на базе Select2, который как раз и тягал все эти списки таксономий и прочих связок. Задачка еще та была) Например, на одной странице строилась таблица графика соревнований по разным видам танцев, в каждом элементе выводилось количество зарегистрированных участников, а также подробная инфа - откуда участники, их профессиональный класс, и так далее. Большая база пользователей с профайлам с кучей полей. Эта страничка к концу регистрации на соревнования генерила до 2-3к запросов в БД и собиралась больше 10 секунд. Пришлось разбить все на кусочки и отдельно их складывать в memcached с временем жизни от 5 до 15 минут, обновлять их тоже порциями, в результате чего страница грузилась как любая другая - не более секунды. В общем, масштаб вашей трагедии я себе представляю более-менее хорошо)

[Пашенька]

Игорь Воротнёв А зачем собственно собственно Posts2Posts? Это те лишняя прослойка, это те же таксономии с их терминами, вроде даже по структуре это тоже 4 таблицы выходит. Я кстати тоже изначально использовал p2p, но потом, когда понял что ванильный вп может тоже самое, я взял дамп своей базы, разобрал его и собрал новый, в котором уже использовал связи через кастомные тасономии. К тому же возникла необходимость добавления кастомных типов записей с фронтэнда, разумеется учитывая все связи между ними, это и стало отправной точкой в самопис.
Advanced Custom Fields Pro я не пользовал, но это же тоже вроде как реализовано в виде произвольных мета-полей, тогда зачем?
Вобщем: ВП - очень гибкий, но не безкостыльный)

[Игорь Воротнёв]

like-a-boss: Post-to-Posts пришлось использовать, это был наиболее гибкий вариант, так как у меня по сути некоторые custom post types выполняли роль таксономий. Например, есть cpt "событие", есть "таксономические" cpt - локация (у него есть место, адрес, фото, описание, карта и тд - таксономией не обойдешься), есть организация которая делает это событие (у нее тоже куча полей - название, описание, связки по географии, иерархические посты филиалов этой организации, контакты и связки с пользователями - руководители, манагеры и спортсмены, которые входят в эту организацию и тд). И таких связок было очень много. То же с cpt "объявления", которые разных типов и с кучей фильтров-таксономий, кроме аналогичных связок с тем же юзером (cpt user_profile, который в свою очередь тоже связан с кучей других объектов). Я рассматривал вариант добавлять эти поля к custom taxonomy именно через ACF Pro, пробовал даже создавать wp_termmeta и писать класс и template functions, аналогичные по работе с wp_postmeta/wp_usermeta, но в итоге вариант с P2P оказался более гибким и быстрым. Сам ACF Pro очень крутая штука, создание красивых и сложных бекендов для custom fields экономит тучу времени, например те же связки, галерея, repeater (повторяющиеся поля или наборы полей), flexible (добавление гибких наборов полей, по сути любых, в том числе repeater внутри flexible), карта, создание страниц опций и GUI для них, импорт-экспорт, синхронизация между multisite (а у нас эта платформа была еще и SaaS на базе WP Multisite), поддержка мультиязычки (там было 3 языка с возможностью расширения) и т.д. Так что использование этого плагина экономило очень много времени. А с недавних пор у ACF даже заработала более-менее функция создания этих форм и на фронтенде, что еще круче. Буквально 2 строчки - и форма готова. Правда, если это не просто создание поста/cpt, а нужно сохранять дополнительные данные - например, создать юзера, к нему cpt "user_profile", связать их, привязать к другим cpt через P2P - тогда понадобится дописывать немного, но ACF дает кучу разных экшнов на всех этапах, так что в паре с родными экшнами WP получается очень гибко, можно сделать практически все, что угодно. При этом девелоперская версия со всеми фичами и анлимом на количество сайтов стоит всего 100$ - это шара если учесть, сколько времени этот плагин экономит.

[Пашенька]

Игорь Воротнёв я вот смотрю плагином, у меня на каждой странице есть некий дефолтный вызов wp_query со стандартными аргументами в зависимости от типа страницы, хотя у меня всюду свои, кастомные запросы. Так то не мешает и выполняется быстро, но вот на странице поиска он отжирает аж 3 секунды. Как его кильнуть отовсюду, возможно?

[Игорь Воротнёв]

like-a-boss: боюсь, что никак) в теории можно, но категорически не рекоммендуется, ибо повылазить может куча бонусов в самых разных местах. Где-то так, почти слово в слово, ответили разрабы ядра на аналогичный вопрос на StackOverflow. Вообще, я тебе как раз на эту тему в каком-то вопросе пару месяцев назад писал, что по возможности надо всегда использовать именно стандартный запрос, то что называется Main Query, модифицируя его на нужных страницах с помощью хуков pre_get_posts и подобных, а также фильтров (см. один из своих старых вопросов). Это существенно упростит работу и снизит вероятность багов - все переменные и объекты будут корректно инициализированы, запросы нормально распарсены и т.д. Если везде тулить свои запросы, а потом еще пытаться создавать свои пермалинки на кастомные урлы, с передачей кастомных параметров - начнется каша и баги. Работа "по стандарту" убережет от многих граблей.

[Пашенька]

Игорь Воротнёв Да, но работа по стандарту не предоставляет нужного функционала. У меня, пожалуй, нет ни одной страницы где бы был один запрос, всюду по несколько. На странице поиска, например, 3 разных + дефолтный не пришей рукав. Если бы один то, да, можно было бы модифицировать, хотя может и так можно

[Пашенька]

Игорь Воротнёв хотя вру, не 3. 3 и в 2-х из них ещё по одному вложенному :)

[Игорь Воротнёв]

like-a-boss: так вот концепт как раз в том, чтобы один из 3х кастомных постов, который самый главный на этой странице, являлся как раз этим main_query. А остальные - на здоровье, хоть get_posts, хоть кастомный через $wpdb. Если порыться в исходниках, там на самом деле много фильтров для модификации, я выше скидывал ссылки на часть из них. Первый раз - необычно и непривычно, но добиться нужного результата в большинстве случаев можно. Не всегда, но все же. Зато если получится - тут есть и плюшки. Например, при подключении кеширующего бекенда, результаты главного запроса автоматом кешируются, как и postmeta к ним. Что существенно снижает нагрузку. И ничего писать не надо - все работает из коробки, достаточно кинуть object-cache.php в wp-content. А при использовании $wpdb придется это все ручками делать, хоть и через готове Cache API.

[Пашенька]

Игорь Воротнёв так и сделал корочи)

[Игорь Воротнёв]

like-a-boss: и правильно)