List:General Discussion« Previous MessageNext Message »
From:<andrew.2.moore Date:November 18 2010 8:00am
Subject:RE: Does mysql cache strip out /* comments */ first?
View as plain text  
I think you will probably find that the code you write isn't what MySQL executes or stores
in the cache.

________________________________________
From: vegivamp@stripped [vegivamp@stripped] On Behalf Of ext Johan De Meersman
[vegivamp@stripped]
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 <daevid@stripped> 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=1
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
Thread
Does mysql cache strip out /* comments */ first?Daevid Vincent18 Nov
  • Re: Does mysql cache strip out /* comments */ first?Johan De Meersman18 Nov
    • RE: Does mysql cache strip out /* comments */ first?andrew.2.moore18 Nov
      • Re: Does mysql cache strip out /* comments */ first?Johan De Meersman18 Nov
  • Re: Does mysql cache strip out /* comments */ first?Ewen Fortune18 Nov
    • RE: Does mysql cache strip out /* comments */ first?Daevid Vincent18 Nov
      • Re: Does mysql cache strip out /* comments */ first?Ewen Fortune18 Nov
        • Re: Does mysql cache strip out /* comments */ first?Waynn Lue19 Nov