Actually no, I'm not using any prepared statements. But thanks for the pointer on that.
-----Original Message----- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 2:24 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Ah, I think I may have figured it out... Are you using prepared statements? If so, doing that prevents the query cache from caching the resultset entirely. On Wed, 2006-08-30 at 14:17 -0500, Mazur Worden, Kathy wrote: > @@global.query_cache_type > ON > > Variable_name Value > Qcache_free_blocks 1 > Qcache_free_memory 10477008 > Qcache_hits 0 > Qcache_inserts 0 > Qcache_lowmem_prunes 0 > Qcache_not_cached 20318 > Qcache_queries_in_cache 0 > Qcache_total_blocks 1 > > Thanks > > -----Original Message----- > From: Jay Pipes [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 30, 2006 2:12 PM > To: Mazur Worden, Kathy > Cc: mysql@lists.mysql.com > Subject: RE: query cache question > > Please show the output of: > > SELECT @@global.query_cache_type; > > and then: > > SHOW GLOBAL STATUS LIKE 'Qc%'; > > Thanks! > > On Wed, 2006-08-30 at 13:43 -0500, Mazur Worden, Kathy wrote: > > The output of that query is: > > > > @@global.query_cache_size > > 10485760 > > > > Kathy Mazur Worden > > Prairie Area Library System > > > > -----Original Message----- > > From: Jay Pipes [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, August 30, 2006 12:42 PM > > To: Mazur Worden, Kathy > > Cc: mysql@lists.mysql.com > > Subject: RE: query cache question > > > > Those queries look just fine to me. > > > > Could you output the result of the following: > > > > SELECT @@global.query_cache_size; > > > > On Wed, 2006-08-30 at 12:27 -0500, Mazur Worden, Kathy wrote: > > > Maybe that's my issue, I had though the type of query would not > > matter. > > > > > > I've run simple queries in query browser like this: > > > Select * from table t; > > > > > > And refreshed web pages hit about a hundred times a day containing > > > counting queries like this: > > > select count(BuildingInfo.deliverycode) AS 'Total' from > BuildingInfo, > > > AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND > > > AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = > > 'Full' > > > OR BuildingInfo.membershipstatus = 'Developmental') > > > > > > And select queries at least as long as these: > > > SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where > > > AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' > > OR > > > AgencyInfo.membershipstatus = 'Developmental') ORDER BY > > AgencyInfo.Name > > > > > > SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', > > > AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER > > > JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid > ORDER > > BY > > > AgencyInfo.Name, BuildingInfo.Name; > > > > > > There are indexes on the id and name related fields used in the > WHERE > > > clauses. > > > > > > Kathy Mazur Worden > > > Prairie Area Library System > > > > > > -----Original Message----- > > > From: Jay Pipes [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, August 30, 2006 12:07 PM > > > To: Mazur Worden, Kathy > > > Cc: mysql@lists.mysql.com > > > Subject: Re: query cache question > > > > > > Could you post an example of a query you are expecting to be cached? > > > > > > On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: > > > > Hi, > > > > > > > > > > > > > > > > I just turned on the query cache by modifying its size and limit > and > > > > restarting the server. However queries aren't being stored to it: > > > > > > > > > > > > > > > > Variable_name > > > > > > > > Value > > > > > > > > Qcache_free_blocks > > > > > > > > 1 > > > > > > > > Qcache_free_memory > > > > > > > > 10477008 > > > > > > > > Qcache_hits > > > > > > > > 0 > > > > > > > > Qcache_inserts > > > > > > > > 0 > > > > > > > > Qcache_lowmem_prunes > > > > > > > > 0 > > > > > > > > Qcache_not_cached > > > > > > > > 3759 > > > > > > > > Qcache_queries_in_cache > > > > > > > > 0 > > > > > > > > Qcache_total_blocks > > > > > > > > 1 > > > > > > > > > > > > > > > > > > > > > > > > I've found > > > > > http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html > > > > which says the qcache_not_cached variable is the number of queries > > > which > > > > were either not SELECT's or have SQL_NO_CACHE specified so I ran > > > several > > > > selects (selected records in a few tables using Query Browser and > > > > refreshed a few web pages hitting the db). None of the queries > have > > > the > > > > no cache option specified. > > > > > > > > > > > > > > > > What else would prevent queries from getting into the cache? > > > > > > > > > > > > > > > > Kathy Mazur Worden > > > > > > > > Prairie Area Library System > > > > > > > > > > > > > > > > > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
