1

Topic: To accelerate request

There is a request which is fulfilled about 40-45 seconds, understand that it brakes enclosed  but as this  can either be optimized, or to transfer its logic to other part

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

How it can be simplified?
Here a part of structure of a DB with an example of the data:

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 0:49:51 PM ', ' 2017-12-19 0:49:51 PM ', 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 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL);
(2, ' SAEL 17 ', 7, 8, ' 2017-12-19 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL);
(3, ' YAVI 19-2 ', 7, 8, ' 2017-12-19 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL);
(4, ' AENBY 11 ', 7, 6, ' 2017-12-19 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL);
(351, ' OLON 30-03 ', 7, 8, ' 2017-12-19 0:50:33 PM ', ' 2017-12-19 0:50:33 PM ', 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 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL);
(2, 2, ' 2017-12-19 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL);
(3, 3, ' 2017-12-19 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL);
(4, 4, ' 2017-12-19 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL);
(351, 351, ' 2017-12-19 0:50:33 PM ', ' 2017-12-19 0:50:33 PM ', 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 0:56:07 PM ', ' 2017-12-19 0:56:07 PM ', NULL, 2);
(15, 2, ' 2018-01-02 ', ' 2017-12-27 ', ' 2018-01-02 ', ' 2017-12-19 0:56:07 PM ', ' 2017-12-19 0:56:07 PM ', NULL, 2);
(16, 2, ' 2018-01-03 ', NULL, NULL, ' 2017-12-19 0:56:07 PM ', ' 2017-12-19 0:56:07 PM ', NULL, 1);
(20, 2, ' 2018-01-07 ', NULL, NULL, ' 2017-12-19 0:56:07 PM ', ' 2017-12-19 0:56:07 PM ', NULL, 1);
(152562, 351, ' 2019-12-10 ', NULL, NULL, ' 2017-12-19 1:00:04 PM ', ' 2017-12-19 1:00:04 PM ', NULL, 1);
(152569, 351, ' 2019-12-17 ', NULL, NULL, ' 2017-12-19 1:00:04 PM ', ' 2017-12-19 1:00:04 PM ', NULL, 1);
(152570, 351, ' 2019-12-18 ', NULL, NULL, ' 2017-12-19 1:00:04 PM ', ' 2017-12-19 1:00:04 PM ', 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 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL, 15, 0);
(3, 1, ' 4 ', NULL, ' 2017-12-19 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL, 8, 0);
(4, 1, ' 4 ', NULL, ' 2017-12-19 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL, 9, 0);
(5, 1, ' 3.5 ', NULL, ' 2017-12-19 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL, 10, 0);
(6, 1, ' 4.5 ', NULL, ' 2017-12-19 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL, 11, 0);
(7, 1, ' 3 ', NULL, ' 2017-12-19 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL, 12, 0);
(98, 5, ' 1-BDR ', NULL, ' 2017-12-19 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL, 15, 0);
(99, 5, ' 1-BA ', NULL, ' 2017-12-19 0:50:03 PM ', ' 2017-12-19 0:50:03 PM ', NULL, 16, 0);
(170, 8, ' 2-BDR ', NULL, ' 2017-12-19 0:50:04 PM ', ' 2017-12-19 0:50:04 PM ', NULL, 15, 0);
(7744, 351, ' 34.7558818 ', NULL, ' 2017-12-19 0:50:33 PM ', ' 2017-12-19 0:50:33 PM ', 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 `);

2

Re: To accelerate request

What for bedrooms_data in ORDER BY?

3

Re: To accelerate request

Try

select
` unit `. *,
unv_2.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`
LEFT JOIN unit_setting_value AS unv_2 ON unv_2.build_setting_id = 15 AND unv_2.unit_id = unit.id AND unv_2.deleted_at is null
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
;

On your yielded the result is identical, and the logic like did not suffer...

4

Re: To accelerate request

It is necessary still  on option_value

5

Re: To accelerate request

Akina, bedrooms_data superfluous, it is not necessary to take in calculation

6

Re: To accelerate request

PavelKe , yes sort by what want... It does not influence correctness or an inaccuracy of request.
Check up, whether I where missed logic of sampling... If is not present - my variant should be more bright...
If the logic is true, and acceleration not to see - show plans of both requests (and too in code tags).

7

Re: To accelerate request

Akina, already checked up with sorting, all is excellent, you again gained me)