Ускорить запрос или снова про LEFT / INNER JOIN

1104

Dram

25 сентября 2017, 17:26

1398

Добрый вечер уважемые натуралисты, вашему вниманию снова диковинный запрос для холивара а можно ли его ускрить и как тут работают LEFT / INNER JOIN

Итак подопытный:

SELECT t1.id, t1.name, t2.title AS category_title, t4.geo_lat, t4.geo_long,
CONCAT_WS('-', t1.id, t1.alias) AS item_alias,
CONCAT_WS('-', t2.id, t2.alias) AS category_alias,
CONCAT_WS('-', t3.id, t3.alias) AS section_alias
FROM tables1_items AS t1
LEFT JOIN tables2_categories AS t2 ON t1.category_id = t2.id
LEFT JOIN tables3_sections AS t3 ON t1.section_id = t3.id
LEFT JOIN tables4_address AS t4 ON t1.id = t4.item_id
WHERE
(t4.geo_long BETWEEN 45.161317 AND 45.181317) AND
(t4.geo_lat BETWEEN 54.181330999996 AND 54.201330999996)

LIMIT 300

Тесты показали что самая быстрая комбинация в тремя LEFT JOIN = 0,35 сек

Я пробовал менять в разных комбинациях LEFT на INNER вплоть до трех INNER - время было всегда хуже 0,45-0,7 сек.

Результат запроса от перестановок лефт/иннер не менялся.

Вот эксплейн в стремя лефт и тремя иннер. Мне непонятно почему с тремя иннер кол-во переборов строк в разы меньше, а запрос длиться дольше?

P.S. в тему призывается Ктулху.... ой не - Чукча!

jpg 12.jpg

A

130

admak

25 сентября 2017, 18:41

#1

условие у вас идет по t4, значит и начинайте джойнить с t4, т.е. как-то так:

...

FROM  tables4_address AS t4

LEFT JOIN tables1_items AS t1 ON t4.item_id = t1.id

LEFT JOIN tables2_categories AS t2 ON t1.category_id = t2.id

LEFT JOIN tables3_sections AS t3 ON t1.section_id = t3.id

WHERE

(t4.geo_long BETWEEN 45.161317 AND 45.181317) AND

(t4.geo_lat BETWEEN 54.181330999996 AND 54.201330999996)

после этого можете еще поэкспериментировать с заменой каждого BETWEEN на два условия "больше" и "меньше",

по переставлять их местами, например min.long min.lat max.long max.lat. возможно при каких то комбинациях ускорится выборка.

затем создайте составной индекс по long, lat и снова потестируйте все комбинации.

ps: а какую базу вы ковыряете: osm, 2gis или еще какую?

.............

В автостратегиях Директа появилась Яндекс ускорил код Метрики При создании целей в

D

1104

Dram

25 сентября 2017, 19:30

#2

admak:
условие у вас идет по t4, значит и начинайте джойнить с t4, т.е. как-то так:
FROM tables4_address AS t4

Получится бессмыслица - у меня ведь условиями CONCAT_WS собираются части урла и формируются ссылки на

фирмы из tables1_items в указанном диапазоне координат и данной категории и разделе.

Составной индекс по гео лат-лонг уже есть

myTarget добавил правило для В автостратегиях Директа появилась Формализатор новый компонент Яндекс.Маркета

C

288

Chukcha

25 сентября 2017, 19:32

#3

Вот эта штука

LEFT JOIN tables4_address AS t4 ON t1.id = t4.item_id

WHERE

(t4.geo_long BETWEEN 45.161317 AND 45.181317) AND

(t4.geo_lat BETWEEN 54.181330999996 AND 54.201330999996)

Автоматически трансформируется в INNER JOIN (LEFT JOIN вырождается в INNER

JOIN tables4_address AS t4 ON t1.id = t4.item_id

WHERE

(t4.geo_long BETWEEN 45.161317 AND 45.181317) AND

(t4.geo_lat BETWEEN 54.181330999996 AND 54.201330999996)

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

И здесь, судя по всему, основное время - это построение плана запроса, и для LEFT он может оказаться проще. Такое встречается и на простейших запросах.

после этого можете еще поэкспериментировать с заменой каждого BETWEEN на два условия "больше" и "меньше",

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_between

This is equivalent to the expression (min <= expr AND expr <= max)

но там есть оговорка о типах данных

---------- Добавлено 25.09.2017 в 22:35 ----------

Dram:
Составной индекс по гео лат-лонг уже есть

А зачем? Чисто теоретически ? Индекс по real?

---------- Добавлено 25.09.2017 в 23:02 ----------

Объяснить такое поведение можно

У вас есть маленькая таблица, и индекс..

Индекс это дерево (туда-сюда) (перемещение по разным частям таблицы)

А таблица - (fullsscan) - только туда (последовательное чтение)

Вот оптимизатор и выбрал оптимальное решение

сделать optimaze и analyze

И проверить

2

Исследование рынка CMS: какие Google: как удалить веб-результаты Power BI для SEO

D

1104

Dram

25 сентября 2017, 20:10

#4

Chukcha:
сделать optimaze и analyze
И проверить

Делается каждую ночь по крону

Ну раз нет простых способов ускорить пусть работает так - 0,4 сек тоже не плохо.

A

130

admak

25 сентября 2017, 20:16

#5

Dram:
Получится бессмыслица - у меня ведь условиями CONCAT_WS собираются части урла и формируются ссылки на
фирмы из tables1_items в указанном диапазоне координат и данной категории и разделе.

CONCAT_WS - просто склеивает стринги, никаких условий в нем нет, все возвращаемые поля остаются без изменений.

хотя трудно не щупая базы давать советы...

---------- Добавлено 26.09.2017 в 00:48 ----------

Chukcha:
https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_between
This is equivalent to the expression (min <= expr AND expr <= max)
но там есть оговорка о типах данных
...
А зачем? Чисто теоретически ? Индекс по real?

э-э... хранить гео в риал-е - это плохая идея.

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

не... в сад флоаты и реалы, я с ними достаточно намучился при учете денег до 8 знака после запятой.

я предпочитаю гео хранить как varchar - нигде никакая дробная циферка не убежит и не станет девяткой в периоде.

по работе с гео есть еще несколько тонкостей/лайфхаков, например в случае ТС мы видим выборку(округлив, это допустимо):

WHERE `lon` > '45.16' and `lon` < '45.18' AND lat > '54.18' AND lat < '54.20'

т.е. по сути нас интересуют только первые 5 символов, что дает нам возможность построить частичный составной индекс по lon, lat, который будет максимально эффективен. что-то типа:

ALTER TABLE `geo_firm` ADD KEY `lon5lat5` (`lon`(5), `lat`(5))

ps: погонял на своей небольшой базе в 1.8М записей, можно и одним индексом по lon в пять символов обойтись

ALTER TABLE `geo_firm` ADD KEY `lon5` (`lon`(5))

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

SELECT *  FROM `geo_firm` WHERE `lon` > '45.16' and `lon` < '45.18' AND lat > '54.18' AND lat < '54.20'
Showing rows 0 - 49 (706 total, Query took 0.0007 seconds.)

1

Яндекс, Минусинск и реальность Как отметить checkbox после Вопросы по Letitbit.net

C

288

Chukcha

25 сентября 2017, 23:16

#6

admak:
в сад флоаты и реалы, я с ними достаточно намучился при учете денег до 8 знака после запятой.

хм, а Decimal для кого выдумали?

а в mssql - money для денег (19,4) на самом деле более интересна цифра 4

И кому нужны 8 знаков?

В банках за день такая погрешность составляет до 2коп в ту или другую сторону.

PHP иногда неправильно считает Xen VPS/VDS, Германия/CША, unmetered Сколько взять за продвижение

D

1104

Dram

26 сентября 2017, 04:59

#7

admak, спасибо, сокращение составного индекса (а он уже был) до 5 символов ускорило запрос до 0,06 сек!!!

Как удалить плохие SEO-ссылки и очистить ссылочную массу сайта

VK приобрела 70% в структуре компании-разработчика red_mad_robot