Note: This article is about the
WHERE extension to
SHOW. I specifically use
SHOW STATUS as an example, but
WHERE is an extension to many
Often DBAs will assess the health of a system by looking at some of the status variables returned by
SHOW GLOBAL STATUS (specifying
GLOBAL is important; remember that
SHOW STATUS is the same as
SHOW SESSION STATUS).
There are many status variables that
SHOW GLOBAL STATUS returns. (
SHOW GLOBAL STATUS on a Windows machine, MySQL version 5.0.67 returned 249, 5.1.22 returned 256 and 6.0.6-alpha returned 295 status variables!). I have used the
SHOW STATUS LIKE syntax to help give me the output I really want, particularly when I forget the exact names of the status variables I am looking for.
But I did not know of a way to perform
SHOW STATUS NOT LIKE or have any other means of filtering the information. Until today, when I was reading up on
The 5.0 manual page for
SHOW STATUS states:
WHEREclause can be given to select rows using more general conditions
and links to the manual page for extensions to
Well, I immediately started testing this out.
SHOW GLOBAL STATUS only returns two columns,
Value, so the
WHERE clause can only filter on those.
SHOW STATUS has over 100 Com_ variables, and over 20 ssl variables. I don’t often use those when looking at output, so I can run:
SHOW GLOBAL STATUS WHERE Variable_name NOT LIKE "%ssl%" AND Variable_name NOT LIKE "com\_%";
(note that I escaped the _ because _ is the wildcard character for one position. If I had not escaped _, the status variable Compression would have been excluded as well).
I can also find all the “important” numbers by running:
SHOW GLOBAL STATUS WHERE Value>0;
Or, even better, all the values that are not 0 – including the text values:
SHOW GLOBAL STATUS WHERE Value!='0';
Using the string ‘0’ will cast
Value as a string and then compare to the string ‘0’. Your mileage may vary given different sql modes.
Interested in working with Sheeri? Schedule a tech call.