List:General Discussion« Previous MessageNext Message »
From:Sergei Golubchik Date:March 29 2005 7:14pm
Subject:Re: Why doesn't MySQL cache queries that start with parenthesis? (further info)
View as plain text  
Hi!

On Mar 28, Homam S.A. wrote:
> It seems that MySQL freaks out whenever it seems
> something that looks like a derive table and refuses
> it to cache. Even a non-UNION query like:
> 
> SELECT * FROM 
> (SELECT * FROM X WHERE A = 5) AS DerivedTable
> 
> Won't be cached.

It's a bug. Could you submit a test case to bugs.mysql.com ?
Something like:

  create table t1 (a int);
  insert t1 values (1);
  show status like 'Qc%';
  select * from ((select * from t1) union all (select * from t1) order by 1);
  show status like 'Qc%';

would be enough for a test case.
(I could submit a bugreport myself, but then you won't be notified when
the bug is fixed)
 
> I read a comment in the documentation that if you put
> SQL_CACHE in the SELECTs of the parenthesized queries,
> it will cache the individual queries:
> 
> http://dev.mysql.com/doc/mysql/en/query-cache.html
> 
> That's not true. It won't cache even the parenthsized
> queries, and the execution time is still the same with
> SQL_CACHE and not.

That's not true. Comment is wrong.
Query cache works at the very low level, basically it caches the result
of the query as it is sent to the client. Raw data on the wire (almost).
So it can only cache the complete query, not a part of it.

By the way, there's no need to measure query execution time, you can
do 'SHOW STATUS' and watch Qcache% variables - just like I did in the
test case above.

Regards,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
       <___/  www.mysql.com
Thread
Why doesn't MySQL cache queries that start with parenthesis? (further info)Homam S.A.29 Mar
  • Re: Why doesn't MySQL cache queries that start with parenthesis? (further info)Sergei Golubchik29 Mar
    • MSSQL Import from MySQLAndrew Maynes29 Mar
      • Re: MSSQL Import from MySQLMartijn Tonies29 Mar