Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics
Linux Systems
Linux Systems
Power Users
Power Users
Tabletop RPGs
Tabletop RPGs
Community Proposals
Community Proposals
tag:snake search within a tag
answers:0 unanswered questions
user:xxxx search by author id
score:0.5 posts with 0.5+ score
"snake oil" exact phrase
votes:4 posts with 4+ votes
created:<1w created < 1 week ago
post_type:xxxx type of post
Search help
Notifications
Mark all as read See all your notifications »
Q&A

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

60%
+4 −2
Q&A 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 ...

2 answers  ·  posted 5mo ago by erenlerhakan‭  ·  last activity 5mo ago by ArtOfCode‭

Question php mysql
#3: Post edited by user avatar Alexei‭ · 2023-11-25T08:40:42Z (5 months ago)
reverted the title
  • Remove the non-standard abbreviation sec
  • How to speed up MySQL query?
#2: Post edited by user avatar H_H‭ · 2023-11-25T08:40:17Z (5 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 user avatar erenlerhakan‭ · 2023-11-23T11:38:06Z (5 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