Форум Сайтостроение Веб-строительство

Оптимизация SQL запроса.

12
D
На сайте с 28.06.2008
Offline
1101
Dram
588

Не могу придумать как оптимизировать этот запрос:

SELECT t0.id, t0.analog_id, t0.analog_title, t1.name, t1.comments, CONCAT_WS('-', t1.id, t1.alias)
AS item_alias, CONCAT_WS('-', t2.id, t2.alias)
AS section_alias,
MIN(t3.price) as item_price
FROM xfd3_lekarstva_analogs AS t0
LEFT JOIN xfd3_lekarstva_items AS t1 ON t0.analog_id = t1.id
LEFT JOIN xfd3_lekarstva_sections AS t2 ON t1.section_id = t2.id
left join xfd3_lekarstva_price2 as t3 on t3.original = t1.name
WHERE t0.item_id = '29076' GROUP BY id
ORDER BY analog_id DESC,item_price ASC LIMIT 5

Он выполняется порядка 2 сек. Вот его EXPLAIN

Насколько мне хватает скудных знаний - проблема в таблице xfd3_lekarstva_price2, вот ее структура

CREATE TABLE `xfd3_lekarstva_price2` (
`id` int(11) NOT NULL,
`available` varchar(255) CHARACTER SET utf8 NOT NULL,
`categoryId` varchar(255) CHARACTER SET utf8 NOT NULL,
`country_of_origin` varchar(255) CHARACTER SET utf8 NOT NULL,
`currencyId` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`description` blob NOT NULL,
`modified_time` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`oldprice` float NOT NULL,
`picture` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`price` float NOT NULL,
`type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`url` blob NOT NULL,
`original` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Индексы сохранённых таблиц
--

--
-- Индексы таблицы `xfd3_lekarstva_price2`
--
ALTER TABLE `xfd3_lekarstva_price2`
ADD PRIMARY KEY (`id`),
ADD KEY `price` (`price`),
ADD KEY `name` (`name`(50)) USING BTREE;
COMMIT;

Посоветуйте плиз где поколдовать?

png 166699.png
Оптимизайка
На сайте с 11.03.2012
Offline
396
Оптимизайка
#1

ALTER TABLE `xfd3_lekarstva_price2` ADD KEY `original` (`original`);

ALTER TABLE `xfd3_lekarstva_items` ADD KEY `name` (`name`);

В целом принцип такой, что поля, участвующие в сравнении (where, join) и сортировке (order, group) должны быть проиндексированы. Все не нужно.

Кроме того, следует подумать о целесообразности LEFT JOIN (почему не INNER JOIN?) и агрегирующих функций (MIN,GROUP) - возможно следует рассчитать заранее?

⭐ BotGuard (https://botguard.net) ⭐ — защита вашего сайта от вредоносных ботов, воровства контента, клонирования, спама и хакерских атак!
D
На сайте с 28.06.2008
Offline
1101
Dram
#2
Оптимизайка:
ALTER TABLE `xfd3_lekarstva_price2` ADD KEY `original` (`original`);
ALTER TABLE `xfd3_lekarstva_items` ADD KEY `name` (`name`);

Большое спасибо, Индекс name уже был, а вот индекс original очень здорово помог, благодарю!

PN
На сайте с 22.08.2012
Offline
103
proksey-net
#3
Dram:
Большое спасибо, Индекс name уже был, а вот индекс original очень здорово помог, благодарю!

Вообще индексы по хорошему надо ставить на все поля, участвующие в JOIN, WHERE и т.д. Это всегда помогает. Причем, если идет, например, WHERE id=:id AND type=:type, то делаем один индекс на оба поля - так еще быстрее.

Мой совет помог? Не скупись! Bitcoin 1Lseddet1o1B6odgXQHbGaWGwRkt1Db8Ef Ethereum 0x450f1a17461e25194B7F9226cDEe70173F39e1e1
CMS для потрала с Google Indexing API - Редиректы со страниц, которых
Оптимизайка
На сайте с 11.03.2012
Offline
396
Оптимизайка
#4
proksey-net:
Вообще индексы по хорошему надо ставить на все поля, участвующие в JOIN, WHERE

Это не по-хорошему. Т.к. запутает оптимизатор СУБД и плюс будет тормозить INSERT, UPDATE. Тут с умом нужно или с экспериментами. Все поля не нужно.

Мой совет помог? Не скупись! Bitcoin

На этом форуме это не работает, проверено :p

PN
На сайте с 22.08.2012
Offline
103
proksey-net
#5
Оптимизайка:
Это не по-хорошему. Т.к. запутает оптимизатор СУБД и плюс будет тормозить INSERT, UPDATE. Тут с умом нужно или с экспериментами. Все поля не нужно.

Так тогда нужно баланс оптимальный подбирать между чтением и записью (по времени).

Оптимизайка:

На этом форуме это не работает, проверено :p

Это не есть хорошо🤪

dlyanachalas
На сайте с 15.09.2006
Offline
693
dlyanachalas
#6

Ещё индекс на xfd3_lekarstva_analogs.item_id

Оптимизайка
На сайте с 11.03.2012
Offline
396
Оптимизайка
#7
dlyanachalas:
Ещё индекс на xfd3_lekarstva_analogs.item_id

Он есть, на плане запроса же видно.

D
На сайте с 28.06.2008
Offline
1101
Dram
#8

А вот с этим запросом такой фокус не прошел почему-то

explain SELECT t1.id, t1.created, t1.description, t2.title, CONCAT_WS('-', t2.id, t2.alias)
AS item_alias, CONCAT_WS('-', t3.id, t3.alias) AS category_alias, CONCAT_WS('-', t4.id, t4.alias) AS section_alias
FROM xfd3_comments_items AS t1
INNER JOIN xfd3_clinics_items AS t2 ON t1.object_id = t2.id
AND t1.object_group = 'com_clinics'
LEFT JOIN xfd3_clinics_sections AS t4 ON t2.section_id = t4.id
LEFT JOIN xfd3_clinics_categories AS t3 ON t2.category_id = t3.id
ORDER BY t1.id DESC LIMIT 3

на фото индексы таблицы xfd3_clinics_items. Я пробовал добавлять индексы на поля section_id и object_id - это не изменило эксплейн

png 12.png
png 14.png
dlyanachalas
На сайте с 15.09.2006
Offline
693
dlyanachalas
#9
Dram:
А вот с этим запросом такой фокус не прошел почему-то



на фото индексы таблицы xfd3_clinics_items. Я пробовал добавлять индексы на поля section_id и object_id - это не изменило эксплейн

Так у вас нету ключей для section_id и object_id. Вместо них есть ключи с такими же именами, но состоящие из нескольких полей.

В MySQL тупо сделано с этими объединениями ключей по-умолчанию, что порождает неочевидное новичкам поведение.

Удалите из групповых индексов все посторонние поля, оставьте только section_id и object_id в каждом соответствующем индексе.

D
На сайте с 28.06.2008
Offline
1101
Dram
#10

Удаление составных индексов и добавление отдельных на эти поля не изменили эксплейн

png 15.png
png 16.png
12

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий