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.
Post History
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 ...
#3: Post edited
by
Alexei
·
2023-11-25T08:40:42Z (12 months ago)
reverted the title
Remove the non-standard abbreviation sec
- How to speed up MySQL query?
#2: Post edited
by
H_H
·
2023-11-25T08:40:17Z (12 months ago)
The unit symbol for second is s not sec . Please use s or write the complete word: second. Thank you. https://www.bipm.org/en/publications/si-brochure
How to speed up mysql query?
- Remove the non-standard abbreviation sec
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 sec 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
- 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
#1: Initial revision
by
erenlerhakan
·
2023-11-23T11:38:06Z (12 months ago)
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 sec 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