Есть запрос, который выполняется около 40-45 секунд, понимаю, что его тормозит вложенный селект, но как этот селект можно либо оптимизировать, либо перенести его логику в другую часть
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
select
`unit`.*,
(SELECT unv_2.value
FROM unit_setting_value AS unv_2
WHERE unv_2.build_setting_id = 15 AND unv_2.unit_id = unit.id AND unv_2.deleted_at is null
) AS `option_value`,
`unit_availability_date`.`booking_date` as `booking_date`,
`unit_availability_date`.`start_date` as `start_date`,
`unit_availability_date`.`end_date` as `end_date`,
`agency`.`name` as `agency_name`
from `unit`
left join `hotel` on `hotel`.`id` = `unit`.`hotel_id`
left join `agency` on `agency`.`id` = `hotel`.`agency_id`
left join `unit_availability_date` on `unit_availability_date`.`unit_id` = `unit`.`id`
inner join `unit_setting_value` as `usv` on `usv`.`unit_id` = `unit`.`id`
where usv.build_setting_id IN (15,23,16,19,17,35,36,21,37,2) and `unit`.`deleted_at` is null and usv.`deleted_at` is null
group by `unit`.`id`
order by `option_value` asc, `bedrooms_data` asc
limit 50 offset 0
Как его можно упростить?
Вот часть структуры БД с примером данных:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
CREATE TABLE IF NOT EXISTS `agency` (
`id` int(10) unsigned NOT NULL,
`name` varchar(63) NOT NULL,
`label` int(10) unsigned NOT NULL,
`email` varchar(255) NOT NULL,
`type` varchar(63) NOT NULL,
`languages` varchar(255) NOT NULL,
`phones` varchar(255) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
INSERT INTO `agency` (`id`, `name`, `label`, `email`, `type`, `languages`, `phones`, `created_at`, `updated_at`, `deleted_at`) VALUES
(8, 'AGR', 1, 'awd@awd.com', 'Asdorl', '{0:2,1:4,2:3}', '{0:97215331111111}', '2017-12-19 12:49:51', '2017-12-19 12:49:51', NULL);
CREATE TABLE IF NOT EXISTS `hotel` (
`id` int(10) unsigned NOT NULL,
`name` varchar(63) NOT NULL,
`label` int(10) unsigned NOT NULL,
`agency_id` int(10) unsigned NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=352 DEFAULT CHARSET=utf8;
INSERT INTO `hotel` (`id`, `name`, `label`, `agency_id`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'PEREM 5', 7, 8, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL),
(2, 'SAEL 17', 7, 8, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL),
(3, 'YAVI 19-2', 7, 8, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL),
(4, 'AENBY 11', 7, 6, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL),
(351, 'OLON 30-03', 7, 8, '2017-12-19 12:50:33', '2017-12-19 12:50:33', NULL);
CREATE TABLE IF NOT EXISTS `unit` (
`id` int(10) unsigned NOT NULL,
`hotel_id` int(10) unsigned NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=352 DEFAULT CHARSET=utf8;
INSERT INTO `unit` (`id`, `hotel_id`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 1, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL),
(2, 2, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL),
(3, 3, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL),
(4, 4, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL),
(351, 351, '2017-12-19 12:50:33', '2017-12-19 12:50:33', NULL);
CREATE TABLE IF NOT EXISTS `unit_availability_date` (
`id` int(10) unsigned NOT NULL,
`unit_id` int(10) unsigned NOT NULL,
`booking_date` date NOT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` timestamp NULL DEFAULT NULL,
`status_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=152571 DEFAULT CHARSET=utf8;
INSERT INTO `unit_availability_date` (`id`, `unit_id`, `booking_date`, `start_date`, `end_date`, `created_at`, `updated_at`, `deleted_at`, `status_id`) VALUES
(1, 2, '2017-12-19', '2017-12-14', '2017-12-27', '2017-12-19 12:56:07', '2017-12-19 12:56:07', NULL, 2),
(15, 2, '2018-01-02', '2017-12-27', '2018-01-02', '2017-12-19 12:56:07', '2017-12-19 12:56:07', NULL, 2),
(16, 2, '2018-01-03', NULL, NULL, '2017-12-19 12:56:07', '2017-12-19 12:56:07', NULL, 1),
(20, 2, '2018-01-07', NULL, NULL, '2017-12-19 12:56:07', '2017-12-19 12:56:07', NULL, 1),
(152562, 351, '2019-12-10', NULL, NULL, '2017-12-19 13:00:04', '2017-12-19 13:00:04', NULL, 1),
(152569, 351, '2019-12-17', NULL, NULL, '2017-12-19 13:00:04', '2017-12-19 13:00:04', NULL, 1),
(152570, 351, '2019-12-18', NULL, NULL, '2017-12-19 13:00:04', '2017-12-19 13:00:04', NULL, 1);
CREATE TABLE IF NOT EXISTS `unit_setting_value` (
`id` int(10) unsigned NOT NULL,
`unit_id` int(10) unsigned NOT NULL,
`value` mediumtext NOT NULL,
`text_id` int(10) unsigned DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` timestamp NULL DEFAULT NULL,
`build_setting_id` int(10) unsigned DEFAULT NULL,
`changeable` tinyint(4) NOT NULL DEFAULT '0' COMMENT '1 or 0, changeable or none'
) ENGINE=InnoDB AUTO_INCREMENT=7746 DEFAULT CHARSET=utf8;
INSERT INTO `unit_setting_value` (`id`, `unit_id`, `value`, `text_id`, `created_at`, `updated_at`, `deleted_at`, `build_setting_id`, `changeable`) VALUES
(2, 1, '2-BDR', NULL, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL, 15, 0),
(3, 1, '4', NULL, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL, 8, 0),
(4, 1, '4', NULL, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL, 9, 0),
(5, 1, '3.5', NULL, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL, 10, 0),
(6, 1, '4.5', NULL, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL, 11, 0),
(7, 1, '3', NULL, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL, 12, 0),
(98, 5, '1-BDR', NULL, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL, 15, 0),
(99, 5, '1-BA', NULL, '2017-12-19 12:50:03', '2017-12-19 12:50:03', NULL, 16, 0),
(170, 8, '2-BDR', NULL, '2017-12-19 12:50:04', '2017-12-19 12:50:04', NULL, 15, 0),
(7744, 351, '34.7558818', NULL, '2017-12-19 12:50:33', '2017-12-19 12:50:33', NULL, 68, 0);
ALTER TABLE `agency`
ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `agency_name_unique` (`name`), ADD KEY `agency_label` (`label`), ADD KEY `agency_deleted_at` (`deleted_at`);
ALTER TABLE `hotel`
ADD PRIMARY KEY (`id`), ADD KEY `hotel_label` (`label`), ADD KEY `hotel_agency_id` (`agency_id`), ADD KEY `hotel_deleted_at` (`deleted_at`);
ALTER TABLE `unit`
ADD PRIMARY KEY (`id`), ADD KEY `unit_hotel_id` (`hotel_id`), ADD KEY `unit_deleted_at` (`deleted_at`);
ALTER TABLE `unit_availability_date`
ADD PRIMARY KEY (`id`), ADD KEY `unit_availability_date_unit_id` (`unit_id`), ADD KEY `unit_availability_date_deleted_at` (`deleted_at`), ADD KEY `unit_availability_date_status_id` (`status_id`);
ALTER TABLE `unit_setting_value`
ADD PRIMARY KEY (`id`), ADD KEY `unit_setting_value_unit_id` (`unit_id`), ADD KEY `unit_setting_value_text_id` (`text_id`), ADD KEY `unit_setting_value_deleted_at` (`deleted_at`), ADD KEY `unit_setting_value_build_setting_id` (`build_setting_id`);
ALTER TABLE `agency`
ADD CONSTRAINT `agency_label_foreign` FOREIGN KEY (`label`) REFERENCES `text` (`id`);
ALTER TABLE `hotel`
ADD CONSTRAINT `hotel_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agency` (`id`),
ADD CONSTRAINT `hotel_label_foreign` FOREIGN KEY (`label`) REFERENCES `text` (`id`);
ALTER TABLE `unit`
ADD CONSTRAINT `unit_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotel` (`id`);
ALTER TABLE `unit_availability_date`
ADD CONSTRAINT `unit_availability_date_status_id_foreign` FOREIGN KEY (`status_id`) REFERENCES `unit_availability_date_status` (`id`),
ADD CONSTRAINT `unit_availability_date_unit_id_foreign` FOREIGN KEY (`unit_id`) REFERENCES `unit` (`id`);
ALTER TABLE `unit_setting_value`
ADD CONSTRAINT `unit_setting_value_build_setting_id_foreign` FOREIGN KEY (`build_setting_id`) REFERENCES `build_setting` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `unit_setting_value_text_id_foreign` FOREIGN KEY (`text_id`) REFERENCES `text` (`id`),
ADD CONSTRAINT `unit_setting_value_unit_id_foreign` FOREIGN KEY (`unit_id`) REFERENCES `unit` (`id`);
|
