Post History

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](https://dev.mysql.com/doc/refman/8.0/en/with.html) which is supported starting from MySQL 8.0.

```sql
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)