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

Andrew73 · 11.01.2012 16:20:47

Здравствуйте уважаемые

В отчете делая выборку для отчета, необходимо выбрать количество из другой таблицы, в которой около 4м записей.
В проекте сделано так:

SELECT 

  v.id, v.name, sum(j.fuel_consumption), sum(j.mileage), s.mileage

FROM avl_vehicle_state s, avl_journey_summary j, avl_vehicle v 

WHERE 

  j.validity = 1 AND 

  v.vehicle_state_id = s.id AND

  j.vehicle_id = v.id  AND

  v.company_id = $CID AND

  (j.started_at >= '$date1' AND j.ended_at <= '$date2') 

GROUP BY v.id, v.name

и далее В ЦИКЛЕ получаем еще одно значение для отчета
foreach (.....) {

  SELECT  COUNT(avl_event.ID) 

  FROM avl_event 

  WHERE 

    avl_event.EVENT_TYPE_ID IN (1,2,3,4,5,6,9) AND 

    avl_event.VEHICLE_ID=$VID AND 

    (avl_event.CREATED_AT>='$date1' AND avl_event.CREATED_AT<='$date2')

}

И это конечно ужасно.

Пытаясь LEFT JOIN avl_event муська умирает
Делая вложенным запросом полегче на много, но все равно медленней старого варианта (в цикле)

Я уверен что в цикле не правильно делать, но как же сделать правильно и быстро?

Заранее благодарен за помощь

vasya · 11.01.2012 17:48:49

Andrew73 написал:
Я уверен что в цикле не правильно делать, но как же сделать правильно и быстро?

На чем основана ваша уверенность?
Вы ведь воочию убедились, что несколько простых запросов работают быстрее одного громоздкого.

Andrew73 · 11.01.2012 18:06:46

Спасибо за ответ.
Уверенность [s]есть[/s] была на том, что "кормить" сервер сотнями мелких запросов это не профессионально раз и второе что несколько таких процессов могут нагрузить сервер.

Теперь я вообще в сомнении оказался

Стоит задача оптимизации этого участка.
Дебаг "выяснил", что тормоза именно из-за цикла
Моя попытка улучшить не получилось из-за малого опыта оптимизации.

Вы посоветуете оставить "как есть" в цикле?

vasya · 11.01.2012 19:59:59

Сотня мелких запросов дают добавочные расходы на открытие таблицы, парсинг запроса, составление плана и т.д. Можно оптимизировать их за счет использования подготовленного выражения, но это не то во что упирается производительность.

Правильно ли я понимаю, что $VID во втором запросе это v.id из первого и для каждого v.id может быть несколько v.name?
Если да, то делая вложенный запрос вы сделаете большее кол-во запросов к таблице avl_event по числу уникальных v.name, а не по числу уникальных v.id как в случае отдельного цикла.

evgeny · 12.01.2012 01:41:15

Какое количество записей возвращает первый запрос ?

Пытаясь LEFT JOIN avl_event муська умирает

Запрос с explain-ом а также show create table обоих таблиц в студию.

Andrew73 · 12.01.2012 11:52:57

evgeny написал:
Какое количество записей возвращает первый запрос ?

8

Запрос с explain-ом

EXPLAIN 

SELECT v.id, v.name, sum( j.fuel_consumption ) , sum( j.mileage ) , s.mileage, COUNT( i.id )

FROM avl_vehicle_state s, avl_journey_summary j, avl_vehicle v 

LEFT JOIN avl_event_index i 

ON 

  i.event_type_id

  IN ( 52, 53, 58, 59, 61, 62, 63, 64, 65, 68, 69, 72, 73, 74, 75, 76, 99, 100, 106, 107, 108, 109, 110, 111, 112, 113 )

  AND i.vehicle_id = v.id

  AND (i.created_at >= '1294704000' AND i.created_at <= '1326326340')

WHERE j.validity =1

  AND v.vehicle_state_id = s.id

  AND j.vehicle_id = v.id

  AND v.company_id =12

  AND (j.started_at >= '2011-01-11 00:00:00' AND j.ended_at <= '2012-01-11 23:59:00')

GROUP BY v.id, v.name

id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra

    SIMPLE     v     ALL     PRIMARY,avl_vehicle_FI_2,avl_vehicle_FI_3     NULL     NULL     NULL     8     Using where; Using temporary; Using filesort

    SIMPLE     s     eq_ref     PRIMARY     PRIMARY     4     avl.v.vehicle_state_id     1      

    SIMPLE     j     ref     avl_journey_summary_FI_1     avl_journey_summary_FI_1     5     avl.v.id     437     Using where

    SIMPLE     i     ref     avl_event_FI_3,avl_event_FI_created_at,avl_event_F...     avl_event_FI_1     5     avl.v.id     25240

а также show create table обоих таблиц в студию.

CREATE TABLE `avl_event_index` (

 `id` int(11) NOT NULL DEFAULT '0',

 `event_type_id` int(11) DEFAULT NULL,

 `created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

 `received` datetime DEFAULT NULL,

 `driver_id` int(11) DEFAULT NULL,

 `vehicle_id` int(11) DEFAULT NULL,

 `location_hashcode` int(11) DEFAULT NULL,

 PRIMARY KEY (`id`,`created_at`),

 KEY `avl_event_FI_2` (`driver_id`),

 KEY `avl_event_FI_3` (`event_type_id`),

 KEY `avl_event_FI_received` (`received`),

 KEY `avl_event_FI_created_at` (`created_at`),

 KEY `avl_event_FI_1` (`vehicle_id`),

 KEY `event_type_id_created_at` (`event_type_id`,`created_at`),

 KEY `location_hashcode_created_at` (`location_hashcode`,`created_at`),

 KEY `ID_INDEX` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))

(PARTITION p1103_1 VALUES LESS THAN (734572) ENGINE = InnoDB,

......

PARTITION p2112_3 VALUES LESS THAN (738521) ENGINE = InnoDB) */

и

CREATE TABLE `avl_vehicle` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `device_id` int(11) DEFAULT NULL,

 `vehicle_state_id` int(11) DEFAULT NULL,

 `plate` varchar(50) DEFAULT NULL,

 `name` varchar(255) DEFAULT NULL,

 `phone` varchar(25) DEFAULT NULL,

 `icon_type` varchar(25) DEFAULT NULL,

 `make` varchar(25) DEFAULT NULL,

 `model` varchar(25) DEFAULT NULL,

 `colour` varchar(25) DEFAULT NULL,

 `chassis_number` varchar(20) DEFAULT NULL,

 `engine` varchar(20) DEFAULT NULL,

 `transmission` varchar(20) DEFAULT NULL,

 `registration_number` varchar(20) DEFAULT NULL,

 `insurance_company` varchar(50) DEFAULT NULL,

 `insurance_number` varchar(25) DEFAULT NULL,

 `year` int(11) DEFAULT NULL,

 `notes` text,

 `tank_capacity` float DEFAULT NULL,

 `company_id` int(11) DEFAULT NULL,

 `external_id` varchar(25) DEFAULT NULL,

 PRIMARY KEY (`id`),

 KEY `avl_vehicle_FI_1` (`device_id`),

 KEY `avl_vehicle_FI_2` (`vehicle_state_id`),

 KEY `avl_vehicle_FI_3` (`company_id`),

 CONSTRAINT `avl_vehicle_FK_1` FOREIGN KEY (`device_id`) REFERENCES `avl_device` (`id`) ON DELETE SET NULL,

 CONSTRAINT `avl_vehicle_FK_2` FOREIGN KEY (`vehicle_state_id`) REFERENCES `avl_vehicle_state` (`id`) ON DELETE SET NULL,

 CONSTRAINT `avl_vehicle_FK_3` FOREIGN KEY (`company_id`) REFERENCES `avl_company` (`id`) ON DELETE SET NULL

) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=latin1

evgeny · 12.01.2012 16:22:22

В вашем первом посте используется совсем другая таблица:
avl_event а в последнем avl_event_index.
Привёл пару примеров, соединения первых двух запросов через LEFT JOIN.
Возможно нужно слегка подредактировать.

1)

SELECT

  v.id, v.name, sum(j.fuel_consumption), sum(j.mileage), s.mileage,COUNT(avl_event.ID)

FROM avl_vehicle_state s, avl_journey_summary j, avl_vehicle v

left join avl_event

on (v.id=avl_event.VEHICLE_ID)

WHERE

  j.validity = 1 AND

  v.vehicle_state_id = s.id AND

  j.vehicle_id = v.id  AND

  v.company_id = $CID AND

  (j.started_at >= '$date1' AND j.ended_at <= '$date2')

  AND avl_event.EVENT_TYPE_ID IN (1,2,3,4,5,6,9)

  AND (avl_event.CREATED_AT>='$date1' AND avl_event.CREATED_AT<='$date2') 

GROUP BY v.id, v.name

2)

select a.*,COUNT(avl_event.ID) from (

SELECT

  v.id, v.name, sum(j.fuel_consumption), sum(j.mileage), s.mileage

FROM avl_vehicle_state s, avl_journey_summary j, avl_vehicle v

WHERE

  j.validity = 1 AND

  v.vehicle_state_id = s.id AND

  j.vehicle_id = v.id  AND

  v.company_id = $CID AND

  (j.started_at >= '$date1' AND j.ended_at <= '$date2')

GROUP BY v.id, v.name

)  a

left join avl_event on(a.id=avl_event.VEHICLE_ID)

WHERE 

avl_event.EVENT_TYPE_ID IN (1,2,3,4,5,6,9) AND

avl_event.CREATED_AT>='$date1' AND avl_event.CREATED_AT<='$date2')

Если скорость выполнения не устроит, то используйте первый вариант как vasya советовал Stored Procedure Cursor, при 8-ми записях это может быть весьма практично.

Andrew73 · 12.01.2012 17:53:42

Да, прошу прощения, что не объяснил
из avl_event
сделал 2 таблицы
Индексную avl_event_index
Данные avl_event_data
Соединив их через VIEW в avl_event
для того что бы хоть чуть ускорить вышеуказанный запрос

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

#1 11.01.2012 16:20:47

Ускорение запроса

#2 11.01.2012 17:48:49

Re: Ускорение запроса

Andrew73 написал:

#3 11.01.2012 18:06:46

Re: Ускорение запроса

#4 11.01.2012 19:59:59

Re: Ускорение запроса

#5 12.01.2012 01:41:15

Re: Ускорение запроса

#6 12.01.2012 11:52:57

Re: Ускорение запроса

evgeny написал:

#7 12.01.2012 16:22:22

Re: Ускорение запроса

#8 12.01.2012 17:53:42

Re: Ускорение запроса

Board footer