I need a complex MySQL query optimising for a booking system

I have the below MySQL query that needs optimising, currently, it takes 22 seconds to run but this need to run in under 2 seconds. Some other date ranges take as little as 10 seconds.

You are free to change the query, add indexes to tables, etc but the data within tables cannot change. The results of this query must be identical. It may be possible to make modifications to my.cnf if they do not affect other databases.

I will provide you with access to a copy of the database on our servers with sensitive information changed to dummy data.

PLEASE DO NOT APPLY IF YOU ARE NOT CONFIDENT YOU CAN DO THIS.

The query:
SELECT datediff('2018-09-04', '2018-09-01') as `nights`, '2018-09-01' as arrival, '2018-09-04' as departure, sum(property_bands.propband_pricedirect) as bookingprice, sum(property_bands.propband_pricespecial) as bookingspecial, datediff('2018-09-04', '2018-09-01')-(count(*)) as mismatch, property_bands.propband_minduration, properties.*, resorts.resort_name, resorts.resort_area, resorts.resort_country, resorts.resort_url, resorts.resort_tripid, resorts.resort_desc, property_types.proptype_name, ( select booking_properties.booking_id from booking_properties where booking_properties.property_id = property_bands.property_id and ((booking_properties.bprop_start = '2018-09-01' and booking_properties.bprop_start '2018-09-01' and booking_properties.bprop_end now()) and booking_properties.bprop_void = '2018-09-01' and dates.day_date < '2018-09-04' and property_bands.propband_start now()) group by property_bands.property_id order by properties.booking_count asc;

The query calculates availability based for all properties on a per night basis for a given date range.

dates = a table that contains every possible date from 2010 to 2037
resorts = location of the property, like a holiday resort may contain 10 villas

properties = list of all properties
property_bands = list of property rates eg, 1st feb to 28th feb ... £28 per night
property_types = the type of property... apartment, villa, ...

bookings = list of all bookings, like an invoice
booking_properties = list of properties associated to the booking, like invoice lines.