Форум пользователей MySQL

webJunior · 06.04.2017 12:19:26

Здравствуйте, для практики делаю что-то вроде сервиса по бронированию номеров. Есть вот такой запрос, фильтр для поиска свободных номеров. Цены сортируются с учетом количества дней. например если меньше 7 дней, то сортировка идет по room.price, если больше 7 и меньше 30 дней по room.price_week, если больше 30 дней по room.price_month, но это все вычисляется с помощью php. Цена вычисляется с прибавлением комиссии сервиса (В данном случае 5%).

SELECT room.id, users.id user_id, room_images.thumb_img, room.price, room.price_week, room.price_month, lang_budget.title budget_title, lang_stars_numb.title stars_title, lang_property_type.title type_title FROM room

INNER JOIN room_images 

INNER JOIN lang_stars_numb

INNER JOIN lang_budget

INNER JOIN lang_property_type

INNER JOIN users 

LEFT JOIN busy_dates ON busy_dates.room_id=room.id 

AND ((busy_from <= '2017-05-01' AND busy_to >= '2017-05-04') 

OR (busy_from = '2017-05-04' OR busy_to = '2017-05-01')

OR (busy_from >= '2017-05-01' AND busy_from <= '2017-05-04') 

OR (busy_from <= '2017-05-01' AND busy_to > '2017-05-01' 

AND busy_to < '2017-05-04')) 

WHERE  users.id = room.user_id 

AND room.budget_id IN ('1','2','3','4','5','6','7','8','9','10') 

AND room.stars_numb_id IN ('1','2') 

AND room.type_id IN ('1','2','3') 

AND room.facility_id IN ('1','2','3','4','5') 

AND room.district_id IN ('1','2')

AND room.status = '1' 

AND room_images.id = room.first_image_id 

AND lang_budget.budget_id = room.budget_id 

AND lang_stars_numb.stars_numb_id = room.stars_numb_id 

AND lang_lang_property_type.id_type = room.id_type 

AND lang_budget.id_lang = 2 

AND lang_stars_numb.id_lang = 2 

AND lang_lang_property_type.id_lang = 2 

AND busy_dates.room_id IS NULL AND ((room.price*"5"/"100")+room.price)*"4" BETWEEN '1' AND '10000' 

ORDER BY ((room.price*"5"/"100")+room.price)*"4" ASC LIMIT 0, 10

Индексы в таблице room:

id (PRIMARY KEY)
room_1 (budget_id, stars_numb_id, type_id, facility_id, district_id)
room_2 (status, id)
room_3 (user_id,parent_id)
EXPLAIN

1 SIMPLE room ALL room_1,room_2,room_3 NULL NULL NULL 8 Using where; Using temporary; Using filesort
1 SIMPLE busy_dates ALL busy_from NULL NULL NULL 2 Using where; Not exists; Using join buffer (flat, ...

По всем остальным таблицам используются индексы, тип eq_ref

Скажите пожалуйста как можно избавиться в таком запросе от Using temporary; Using filesort и от сканирования всей таблицы. Проблема еще и в том, что при сортировке по room.id, тоже получаю Using temporary; Using filesort. Спасибо.

Отредактированно webJunior (06.04.2017 12:21:15)

vasya · 06.04.2017 20:49:02

от файловой сортировки (Using temporary; Using filesort) никак.
можно лишь уточнить где создается временная таблица - в памяти или на диске
show status like
Created_tmp_disk_tables -- количество неявных временных таблиц на диске, созданных во время выполнения операторов.
сравните значение до и после выполнения запроса, при необходимости увеличьте tmp_table_size

от all у busy_dates, имхо, не избавится
а вот заставить использовать room_1 можно, но стоит ли? сервер думал прежде чем отказаться от него

и зачем вы числовые значения указываете как строки?

webJunior · 06.04.2017 23:58:26

vasya написал:
от файловой сортировки (Using temporary; Using filesort) никак.
можно лишь уточнить где создается временная таблица - в памяти или на диске
show status like
Created_tmp_disk_tables -- количество неявных временных таблиц на диске, созданных во время выполнения операторов.
сравните значение до и после выполнения запроса, при необходимости увеличьте tmp_table_size

от all у busy_dates, имхо, не избавится
а вот заставить использовать room_1 можно, но стоит ли? сервер думал прежде чем отказаться от него

и зачем вы числовые значения указываете как строки?

Насчет числовых значений, да действительно, исправил.

Created_tmp_disk_tables до запроса 0, после запроса 4. tmp_table_size 25m, чтобы проверить увеличил до 100, тот же результат. Никак нельзя оптимизировать такой запрос?

Отредактированно webJunior (06.04.2017 23:58:46)

vasya · 08.04.2017 15:43:49

webJunior написал:
tmp_table_size 25m, чтобы проверить увеличил до 100, тот же результат.

увеличьте ещё max_heap_table_size и емнип, наличие text/blob тоже приводят к такому результату.

webJunior · 08.04.2017 16:15:39

vasya написал:
webJunior написал:
tmp_table_size 25m, чтобы проверить увеличил до 100, тот же результат.
увеличьте ещё max_heap_table_size и емнип, наличие text/blob тоже приводят к такому результату.

Увеличил tmp_table_size 256m, max_heap_table_size 256m. Так же Created_tmp_disk_tables 4

Насчет text/blob имеете ввиду типы полей которые выбираются? Нет там только числовые и varchar

Отредактированно webJunior (08.04.2017 16:15:58)

vasya · 08.04.2017 17:23:14

а в чем вы запускаете запрос? в консоли?
будут ли отличаться показания, если после запроса дважды выполнить
SHOW SESSION STATUS LIKE ‘Created%’;

webJunior · 08.04.2017 17:42:11

vasya написал:
а в чем вы запускаете запрос? в консоли?
будут ли отличаться показания, если после запроса дважды выполнить
SHOW SESSION STATUS LIKE ‘Created%’;

В консоли phpmyadmin

SHOW SESSION STATUS LIKE ‘Created%’; выводит ошибку

Ответ MySQL: Документация

#1064 - У вас ошибка в запросе. Изучите документацию по используемой версии MariaDB на предмет корректного синтаксиса около '‘Created%’' на строке 1

Работает только SHOW SESSION STATUS, без каких-либо изменений

Отредактированно webJunior (08.04.2017 17:46:32)

vasya · 08.04.2017 20:35:24

а есть возможность выполнить через консоль, чтобы исключить глюки phpadmina?

webJunior · 08.04.2017 21:54:43

vasya написал:
а есть возможность выполнить через консоль, чтобы исключить глюки phpadmina?

Отредактированно webJunior (08.04.2017 21:56:04)

vasya · 08.04.2017 23:18:03

webJunior написал:
Created_tmp_files даже без запроса показывает 5

это нормально

возвращаясь к исходному запросу, имхо, вряд ли можно его улучшить

webJunior · 09.04.2017 11:19:27

vasya написал:
webJunior написал:
Created_tmp_files даже без запроса показывает 5
это нормально

возвращаясь к исходному запросу, имхо, вряд ли можно его улучшить

Я правильно понимаю, что с увеличением записей в таблицах, при настройках по умолчанию временные таблицы могут создаваться на диске?

Подскажите пожалуйста почему когда идет сортировка по room.id, так же получаю Using temporary; Using filesort?

vasya · 09.04.2017 19:11:08

webJunior написал:
Я правильно понимаю, что с увеличением записей в таблицах, при настройках по умолчанию временные таблицы могут создаваться на диске?

Временная таблица создается на диске если её размер превышает минимум из max_heap_table_size/tmp_table_size. Т.е. это связано с объемом выборки, а не вообще данных. Например, при бронировании вы ищите свободные номера на ближайший месяц, хотя сама таблица может содержать данные о постояльцах за последнее столетие.

webJunior написал:
Подскажите пожалуйста почему когда идет сортировка по room.id, так же получаю Using temporary; Using filesort?

видимо оптимизатор считает, что придется перебрать много записей и проще прочитать последовательно всю таблицу.
http://sqlinfo.ru/forum/viewtopic.php?pid=25439#p25439

webJunior · 09.04.2017 19:28:30

Временная таблица создается на диске если её размер превышает минимум из max_heap_table_size/tmp_table_size. Т.е. это связано с объемом выборки, а не вообще данных. Например, при бронировании вы ищите свободные номера на ближайший месяц, хотя сама таблица может содержать данные о постояльцах за последнее столетие.

Т.е размер временной таблицы может зависеть от количества проверяемых строк? То что показывает ячейка rows при Explain?

vasya · 09.04.2017 19:35:43

от количества выбираемых
пусть в таблице N строк
проверяем M строк
удовлетворяет условию выборки X строк
эти X строк сортируются методом файловой сортировки и если X*(размер одной строки) > min(max_heap_table_size/tmp_table_size), то временная таблица создается на диске

rows в explain показывает ожидаемую оптимизатором оценку M

webJunior · 09.04.2017 23:58:22

vasya написал:
от количества выбираемых
пусть в таблице N строк
проверяем M строк
удовлетворяет условию выборки X строк
эти X строк сортируются методом файловой сортировки и если X*(размер одной строки) > min(max_heap_table_size/tmp_table_size), то временная таблица создается на диске

rows в explain показывает ожидаемую оптимизатором оценку M

Ясно, большое спасибо!

Форум пользователей MySQL

#1 06.04.2017 12:19:26

Просьба помочь с запросом

#2 06.04.2017 20:49:02

Re: Просьба помочь с запросом

#3 06.04.2017 23:58:26

Re: Просьба помочь с запросом

vasya написал:

#4 08.04.2017 15:43:49

Re: Просьба помочь с запросом

webJunior написал:

#5 08.04.2017 16:15:39

Re: Просьба помочь с запросом

vasya написал:

webJunior написал:

#6 08.04.2017 17:23:14

Re: Просьба помочь с запросом

#7 08.04.2017 17:42:11

Re: Просьба помочь с запросом

vasya написал:

#8 08.04.2017 20:35:24

Re: Просьба помочь с запросом

#9 08.04.2017 21:54:43

Re: Просьба помочь с запросом

vasya написал:

#10 08.04.2017 23:18:03

Re: Просьба помочь с запросом

webJunior написал:

#11 09.04.2017 11:19:27

Re: Просьба помочь с запросом

vasya написал:

webJunior написал:

#12 09.04.2017 19:11:08

Re: Просьба помочь с запросом

webJunior написал:

webJunior написал:

#13 09.04.2017 19:28:30

Re: Просьба помочь с запросом

#14 09.04.2017 19:35:43

Re: Просьба помочь с запросом

#15 09.04.2017 23:58:22

Re: Просьба помочь с запросом

vasya написал:

Board footer