I think you will probably find that the code you write isn't what MySQL executes or stores in the cache.
________________________________________ From: vegiv...@gmail.com [vegiv...@gmail.com] On Behalf Of ext Johan De Meersman [vegiv...@tuxera.be] Sent: 18 November 2010 07:48 To: Daevid Vincent Cc: mysql Subject: Re: Does mysql cache strip out /* comments */ first? Given that even spacing is important, it's a safe bet that it takes comments into consideration, too. Easily tested, though: grab one of the heaviest queries you have from your slowlog, and execute with identical and different comments. On Thu, Nov 18, 2010 at 8:31 AM, Daevid Vincent <dae...@daevid.com> wrote: > > Like most developers, I have a wrapper that all of my SQL queries go > through in PHP. > > We have a dedicated "NOC" screen that shows the "mytop" status of each > DEV/TEST/PROD master/slave pair. > http://daevid.com/content/examples/snippets.php (Automatic Monitoring of > remote servers) > > We sometimes see stuck queries and are always hesitant to "kill" them off > because we never know WHO is executing that SQL. Is it a customer? Is it a > developer? Is it the boss? Is it rogue from some script gone awry? Mytop > doesn't give the full query due to screen real-estate amongst other > reasons. The downside is they bog down the server until they eventually > time-out or complete. > > Anyways, today I implemented a simple, transparent and effective step > towards this puzzle. > > I prefix ALL SQL (since it goes through my "sql_query()" function) with > > /* ${SCRIPTNAME} */ > > Now all sql in the mytop shows up as: > > /* foo.php */ SELECT * FROM foo WHERE id = 1; > /* bar.php */ UPDATE bar SET a = b WHERE id = 2; > Etc... > > What I'd REALLY like to do is add more information in there. Perhaps add > the FUNCTION/METHOD and the logged-in web USER that is actually executing > that SQL, etc. > > My concern is, my gut tells me that the built in mysql cache system is > "dumb". And by that I mean, I suspect that mySQL isn't "smart" enough to > strip out comments from the SQL statement string BEFORE storing it as the > cache hash key (yet I have no facts either way to back it up and hence the > reason for this email). > > http://dev.mysql.com/doc/refman/5.0/en/query-cache.html > http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html > > Can anyone please tell me I'm wrong and that it is smarter than I give it > credit for, as I think this would be a very useful "feature" (or bug-fix as > the case may be). > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
