WTF???

…the cache is not used for queries of the following types:

* Prepared statements
* Queries that are a subquery of an outer query
* Queries executed within the body of a stored function or trigger

http://dev.mysql.com/doc/refman/5.0/en/query-cache-operation.html

+

For certain cases, a correlated subquery is optimized. For example:

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

Otherwise, they are inefficient and likely to be slow. Rewriting the query as a join might improve performance.

http://dev.mysql.com/doc/refman/5.1/en/correlated-subqueries.html

I have 5 nested views… from 1 to 4, I have GROUP BY, ORDER BY, JOIN of the data over themselfs and other amenities… and on a ~2milion rows table, it doesn’t take more than 14 seconds…

the last view… has a subquery… evaluating the data related to the “currently evaluated row”, looking on the same table for values of “the same group”… and it gets DAMN SLOW… cause the “query cache” mechanism of MySQL does not kick in

k, tomorrow morning I go for the rewriting… I’ll create a VIEW about the data I need to be correlated, and I’ll JOIN it… let’s see 🙂