Suppose you have a form to filter a "cars" table. The form contains 3 filters "model" "brand" "speed". I want to perform a "plain" query, writing it textually, adding dynamically the filters comes from POST or GET array, and execute it with query command.
My doubt is: how to cache this query for different parameters combinations? Is it better to put the sql text as dependency or to set each parameter as a different dependency? And also, is yii able to recognize different combinations of the filters applied before?
I’m not sure if it’s a good idea to cache the result of queries with complex search criteria. Caching may save resources but it also consumes some resources by creating and validating caches. Is it likely that multiple users of your app will use the same search criteria within a short period of time?
Anyway, it seems to be better to create a mysql view and working on it.
The question for "Yii gurus" is: what is the best way to work on this kind of complex queries with Yii? Think about a statistical system, like Google Analytics. How do you manage that kind of data elaboration?
There are 4 joins under this query, it could be not so complex, for sure, but i would like to improve perfomances at all and, first of all, try to understand how to optimize large queries with many parameters and an high volume of underlying data.