How to increase the search speed

d_tereschuk

Optimization is needed at the level of PHP, because the database was optimized to the maximum.
Now the search is performed for 8 seconds (due to the large amount of data to be processed and a complex price calculation based on a large number of price profiles, rules, periods of time, etc.).

Problem:
We need to decrease the search speed to 2 seconds .

Possible solution:
There is an idea to use caching, but we have a problem when the price of the product is changed. We need to generate cache again but generation of the whole cache takes a lot of time because of large amount of data.

How to increase the search speed?

johanafm

"There is as of yet unsufficient data for a meaningful answer"
- Weedpacket

In the meantime…

d.tereschuk;11036081 wrote:
'Optimization is needed at the level of PHP, because the database was optimized to the maximum.

A statement I strongly doubt but cannot verify

d.tereschuk;11036081 wrote:
due to the large amount of data

Which I doubt is the reason, but cannot verify. However, please define "large amount of data"

d.tereschuk;11036081 wrote:
but we have a problem when the price of the product is changed. We need to generate cache again but generation of the whole cache takes a lot of time because of large amount of data.

Which is not true. You said it takes "8 seconds for the highly optimized query". So use this "highly optimized query" to recalculate the value to cache in "8 seconds" whenever a price changes, and during those 8 seconds let users see the old value until the new value has been computed, at which point you replace the cached value.

d.tereschuk;11036081 wrote:
How to increase the search speed?

1. optimize schema and/or query
and/or
2. cache results

Based on the provided information, this would mean that you can't solve the problems because
1. you cannot optimize more
2. caching is not an option.

sneakyimp

If you are using MySQL, there is probably already a query cache working for your benefit which is smart enough to know when tables have changed.

Johanafm is correct to question whether your queries are really optimized. I once had a similar problem and spent a lot of time sorting out ways to improve performance. At a certain point, you have to really understand how things are working underneath. In some cases, it is simply not possible to get better performance without completely reimagining one's data storage approach.