Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Web программирование > SQL, базы данных
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 07.03.2019, 10:01   #1
PTyTb32
Форумчанин
 
Регистрация: 06.10.2013
Сообщений: 216
По умолчанию Оптимизация SQL запроса

Привет, есть запрос, он работает, возвращает нужные данные, но выполняется очень долго. Программа из которой он выполняется успевает совершить дисконект, помогите его оптимизировать, что бы он выполнялся быстрее
Код:
SELECT
d.ad as `Номер`,
DATE_FORMAT(FROM_UNIXTIME(i1.tim - 10800),"%m.%Y") as `Дата`,
(SELECT
max(obem) - min(obem)
FROM `isdata`
WHERE
device = i1.device and
DATE_FORMAT(FROM_UNIXTIME(i1.tim - 10800),"%m.%Y") = 
DATE_FORMAT(FROM_UNIXTIME(tim - 10800),"%m.%Y")
GROUP BY device) as `Прошло воды`
FROM `isdata` as i1
LEFT JOIN isdev as d on i1.device = d.id
GROUP BY DATE_FORMAT(FROM_UNIXTIME(i1.tim - 10800),"%m.%Y"),device
Вот такую ошибку получаю
[Err] 2013 - Lost connection to MySQL server during query

Задача запроса выдать таблицу:
Номер счетчика | Дата | Сколько прошло через него воды

Таблица isdev:
id:int A_I // индекс
ad:int // адрес устройства
name:varchar // название устройства
object:int // индекс объекта из таблицы isobjects
discript:varchar // описание

Таблица isdata:
id:int A_I // индекс
device:int // id из таблицы isdev
rashod:float // показания текущего расхода
height:float // показания уровня жидкости в баке
narabotka:float // наработка устройства
obem:float // перекаченный объем воды
tim:int // дата и время в UNIXTIME

Последний раз редактировалось PTyTb32; 07.03.2019 в 10:19.
PTyTb32 вне форума Ответить с цитированием
Старый 07.03.2019, 10:09   #2
Serge_Bliznykov
Старожил
 
Регистрация: 09.01.2008
Сообщений: 26,238
По умолчанию

что можно сделать по быстрому и по простому - проверьте и ОБЯЗАТЕЛЬНО добавьте индексы в таблицах
isdev поле id
в таблице isdata
поля tim device obem

если это не поможет (или не допустимо), тогда остаётся только анализировать план запроса и изменять запрос.
возможно, что решением проблемы будет просто переписать запрос вообще по другому.
возможно, для решения проблемы нужно завести дополнительное поле (можно и вычисляемое) и хранить там дату в формате "m.Y" (с дополнительным полем - это уже крайний вариант. Тут минусов может быть больше, чем выигрыш на запросе.
Serge_Bliznykov вне форума Ответить с цитированием
Старый 07.03.2019, 10:23   #3
PTyTb32
Форумчанин
 
Регистрация: 06.10.2013
Сообщений: 216
По умолчанию

Цитата:
Сообщение от Serge_Bliznykov Посмотреть сообщение
что можно сделать по быстрому и по простому - проверьте и ОБЯЗАТЕЛЬНО добавьте индексы в таблицах
isdev поле id
в таблице isdata
поля tim device obem

если это не поможет (или не допустимо), тогда остаётся только анализировать план запроса и изменять запрос.
возможно, что решением проблемы будет просто переписать запрос вообще по другому.
возможно, для решения проблемы нужно завести дополнительное поле (можно и вычисляемое) и хранить там дату в формате "m.Y" (с дополнительным полем - это уже крайний вариант. Тут минусов может быть больше, чем выигрыш на запросе.
индексы есть, через них LEFT JOIN работает если убрать внутренний запрос, то все быстро работает, но не возвращает самые важные данные
PTyTb32 вне форума Ответить с цитированием
Старый 07.03.2019, 10:28   #4
Serge_Bliznykov
Старожил
 
Регистрация: 09.01.2008
Сообщений: 26,238
По умолчанию

Цитата:
Сообщение от PTyTb32 Посмотреть сообщение
индексы есть
отдельные индексы есть именно для всех полей: tim, device, obem ?


Цитата:
Сообщение от PTyTb32 Посмотреть сообщение
если убрать внутренний запрос, то все быстро работает, но не возвращает самые важные данные
ну, значит, нельзя его убирать
ваш кэп.
Serge_Bliznykov вне форума Ответить с цитированием
Старый 07.03.2019, 10:30   #5
PTyTb32
Форумчанин
 
Регистрация: 06.10.2013
Сообщений: 216
По умолчанию

Цитата:
Сообщение от Serge_Bliznykov Посмотреть сообщение
отдельные индексы есть именно для всех полей: tim, device, obem ?
нет, такого нет, и честно говоря не представляю как это может выглядеть...
PTyTb32 вне форума Ответить с цитированием
Старый 07.03.2019, 10:37   #6
evg_m
Старожил
 
Регистрация: 20.04.2008
Сообщений: 5,515
По умолчанию

Цитата:
Задача запроса выдать таблицу:
Номер счетчика | Дата | Сколько прошло через него воды
когда? за какое время? с ... -по ... ?
всего!?

для ВСЕХ внесенных показаний по каждому прибору ?
от каждого внесенного времени по ...?
за каждый месяц ?!

Код:
select device, dataformat() as t, max() -min() as q
from isdata
group by device, dataformat()

(+) left join isdev
программа — запись алгоритма на языке понятном транслятору

Последний раз редактировалось evg_m; 07.03.2019 в 10:39.
evg_m вне форума Ответить с цитированием
Старый 07.03.2019, 10:38   #7
Serge_Bliznykov
Старожил
 
Регистрация: 09.01.2008
Сообщений: 26,238
По умолчанию

Цитата:
Сообщение от PTyTb32 Посмотреть сообщение
нет, такого нет, и честно говоря не представляю как это может выглядеть...
Код:
ALTER TABLE `isdata` ADD INDEX(`device`);
ALTER TABLE `isdata` ADD INDEX(`obem`);
ALTER TABLE `isdata` ADD INDEX(`tim`);
а почему для времени используется тип INT, чем Вас timestamp не устроил?


Цитата:
Сообщение от evg_m Посмотреть сообщение
select device, dataformat() as t, max() -min() as q
from isdata
group by device, dataformat()

(+) left join isdev
Категорически согласен!!!!
Serge_Bliznykov вне форума Ответить с цитированием
Старый 07.03.2019, 10:50   #8
evg_m
Старожил
 
Регистрация: 20.04.2008
Сообщений: 5,515
По умолчанию

Цитата:
Категорически согласен!!!!
вот ТОЛЬКО не будут учтены "переходные" периоды.
(с последней даты предыдущего месяца до первой даты нового месяца)
впрочем исходный запрос их также не учитывал.
программа — запись алгоритма на языке понятном транслятору
evg_m вне форума Ответить с цитированием
Старый 07.03.2019, 10:54   #9
PTyTb32
Форумчанин
 
Регистрация: 06.10.2013
Сообщений: 216
По умолчанию

Цитата:
Сообщение от evg_m Посмотреть сообщение
когда? за какое время? с ... -по ... ?
всего!?

для ВСЕХ внесенных показаний по каждому прибору ?
от каждого внесенного времени по ...?
за каждый месяц ?!

Код:
select device, dataformat() as t, max() -min() as q
from isdata
group by device, dataformat()

(+) left join isdev
периоды, приборы задаются в WHERE, в программе он есть, а для запроса убрал... он те так важен. параметры можно настроить таким образом что будет охвачен весь диапазон, за решение моей проблемы большое спасибо)
PTyTb32 вне форума Ответить с цитированием
Ответ


Купить рекламу на форуме - 42 тыс руб за месяц

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Оптимизация запроса. EnMac SQL, базы данных 20 15.04.2015 15:11
Оптимизация запроса russian-stalker SQL, базы данных 1 11.10.2011 13:17
Оптимизация запроса stalsoft SQL, базы данных 0 05.07.2011 14:51
оптимизация запроса pray_driver SQL, базы данных 3 13.12.2010 15:40
Оптимизация SQL запроса Phantom SQL, базы данных 4 24.09.2009 16:07