MySQL Bugs: #60560: insert and query cache

Bug #60560	insert and query cache
Submitted:	21 Mar 2011 3:49	Modified:	26 Mar 2011 10:13
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S4 (Feature request)
Version:	any	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	insert, qc, query cache

Description:
hi guys, i was trying this:

INSERT INTO any_table SELECT SQL_CACHE * FROM table2

i executed twice....
INSERT INTO any_table SELECT SQL_CACHE * FROM table2;
INSERT INTO any_table SELECT SQL_CACHE * FROM table2;

the time was near the same (14 seconds)

my question... SQL_CACHE don't work with INSERT?
could/should it work?

for example, if SQL_CACHE was founded in query and query starts with:
INSERT INTO xxxx <SELECT part>
could we rewrite it to :
<SELECT part> 
and check if it's in query cache?

How to repeat:
create a table and populate
write a complex SELECT query (14seconds without SQL_CACHE)
and the same with a fast SQL_CACHE (1second)

create table XXX <SELECT query with SQL_CACHE>
just to create table without keys... (it's a test...)

INSERT INTO TABLE XXX <SELECT query with SQL_CACHE>;
INSERT INTO TABLE XXX <SELECT query with SQL_CACHE>;
INSERT INTO TABLE XXX <SELECT query with SQL_CACHE>;

the <SELECT query with SQL_CACHE> at the first query will be slow (14 seconds, no cache in use)
the second must be faster (1 seconds)

Suggested fix:
maybe change query cache

check if we have a SQL_CACHE
if yes:
check if start with INSERT, CREATE TABLE, (in future with: DELETE, UPDATE)
if yes, try to remove INSERT/CREATE SQL text (get only SELECT part)
check if SELECT part exists in query cache, if yes use it, if no put it on cache

i don't know if delete,update and others could work with it
today work around is:

1)
at app side:
create table x <SELECT cache part>
INSERT INTO y SELECT * FROM x

change queries that use <SELECT cache part> to drop table x (very complex! but we are app developer, it's possible!, the bad part is: dba should know about this and drop table x if he change the table)

2)
SELECT SQL_CACHE SQL_BUFFERED_RESULT * FROM x

execute the INSERT INTO y with (vars) VALUES (values) at app side
the SQL_CACHE will execute at SELECT, app will use the returned value to insert into table y (if we use a slow network to connect to database this will be a problem...)

3) any other?

changed to quey cache category

Current limitation is described in the manual, http://dev.mysql.com/doc/refman/5.5/en/query-cache-operation.html:

"The cache is not used for queries of the following types:

    - Queries that are a subquery of an outer query "

Here we have a request to remove this limitation.

This is far from possible in the current query cache architecture. The current architecture caches packets that are sent to the user, not intermediate results. A query cache is intended to cache results that are sent to a user, not server internal intermediate results.

Also, the caching and strategy of a INSERT SELECT operates on a completely different level. There much different caches opportunities and strategies in the caching of intermediate results.

Also, the SELECT part of a INSERT ... SELECT has side-effects (e.g. locking) that differ it from the effects of of a plain SELECT or the SELECT part of a CREATE TABLE ... SELECT. So, they all aren't exactly the same.