Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
How to speed up MySQL query?
I have a website about vacation villa rentals. Around 800 properties are listed. When i start a search, i get properties with my first query which filters type, style, area, no. of rooms, capacity etc. After i get the list, a foreach loop shows the results. If check-in and check-out dates are not selected, 800 properties take 1 s max in mysql query.
But if dates are selected, inside the loop, i make another mysql query to get prices of the propery between selected dates from a table called property_prices. (if no price exists, or the condition of the propery is not "booked" for a or any day in the selected range)
Below is the first query which takes 1 second the return 800 property details.
$sql = "SELECT property_id, property_capacity, property_rooms FROM properties WHERE property_status = 2 ";
And this one is for the prices is check-in and check-out dates are given.
$res = $Db->row("SELECT SUM(price_value) AS TotalPrice, COUNT(price_id) AS TotalRecord FROM property_prices WHERE price_property_id = ? AND price_date BETWEEN '$sdate' AND '$edate' AND ((price_date = '$sdate' AND price_condition IS NOT NULL) OR (price_date > '$sdate' AND price_condition = ?)); ", array($listing['property_id'],1));
The problem starts here, i get the results in 30 seconds when i search for the total price for each property. I'm happy to make pagination but* i have a sorting filter for customer to sort from cheap to expensive or reverse.
As i don't know the total prices for each property, i can not sort the list if i make a pagination.
The property_price table has
| id | propery_id | date | price | condition
Any ideas/offers, how i can manage to speed up the query? As the property prices change alot, i cannot use memcache for search result.
Cheers
2 answers
The main issue with your approach is that, in some cases, you execute a lot of queries to fetch the data. When working with SQL (regardless of relational flavor), you must aim from a set-based approach, that is, you do not execute multiple queries in a loop, but a bigger query.
In your case, that means that you should compute the prices grouped by property for all the involved properties. This should be much faster.
The query would look like the following (not tested, as the schema/parameters are not completely clear to me). It relies on a CTE which is supported starting from MySQL 8.0.
WITH AggrPricesCte AS (
SELECT property_id, SUM(price_value) AS TotalPrice, COUNT(price_id) AS TotalRecord
FROM property_prices
WHERE price_date BETWEEN '$sdate' AND '$edate'
AND (
(price_date = '$sdate' AND price_condition IS NOT NULL)
OR (price_date > '$sdate' AND price_condition = ?)
)
GROUP BY property_id
)
SELECT ap.TotalPrice, ap.TotalRecord, p.property_id, p.property_capacity, p.property_rooms
FROM properties p
JOIN AggrPricesCte ap ON p.property_id = ap.property_id
WHERE p.property_status = 2
ORDER BY ap.TotalPrice DESC;
Note: I think that '$sdate' and '$edate' should be replaced with parameters and not use what seems to be string interpolation. This is required to prevent SQL injection and parameterized queries might perform better.
Another improvement would be to use a UNION to avoid the OR (this operator tends to slow down the execution quite a bit), but for your current record count (~800), I do not think it should make a difference.
Ref. to "As i don't know the total prices for each property, i can not sort the list if i make a pagination.", this will become a big issue on the long term. You will eventually need to make a compromise. Some ideas:
- sort by unit price (e.g. per day)
- sort by some precomputed total price (an async job might compute from time to time the total price for all units)
0 comment threads
Sounds like you may also have a problem with your database structure. A query that fetches 800 records from a table with only those 800 records in it should not take a full second - it should be milliseconds.
Make use of the EXPLAIN function to find out what your queries are trying to do and what indexes they're trying to use.
SELECT property_id, property_capacity, property_rooms
FROM properties
WHERE property_status = 2
This query should ideally be using an index for property_status to filter your records. Do you have an index on the property_status column? Go through each of the queries you're finding slow and identify whether you have appropriate indexes for it to use.
0 comment threads
0 comment threads