First on the use of pagers
When using MySQL from the commandline through its mysql client, one enjoys usage speeds many phpMyAdmin users are extremely jealous of. On the downside, though, comes that your queries sometimes generate so much information that it becomes unmanageable, a veritable landslide of information, untamed, pouring down your terminal like an unending avalanche. Page after page after page runs down your screen. You sent a CTRL+C ages ago but your terminal still hasn't processed it... why oh why didn't I put a
LIMITon that query....
Luckily, there are ways with which to stem the tide of information.
I am, of course, talking about the pager command.
moreare obvious candidates for pagination.
It also works with
grep. Say you want to find all records around a particular value but don't know precisely where it appears in the table(s) / output
pager fgrep -i -C 10 --color=yes searchstring
SELECT * FROM mytable;
This will run the query and pipe it through
, doing a case-insensitive match, colourising the output, and showing 10 lines before and after the match.
Of course, for advanced matching you can use
Secondly, on the use of the information_schema database
The information_schema database is a very welcome ANSI addition to MySQL and I'm sure you can find heaps of information about it elsewhere. However, I invite you to explore it for yourself. Almost every
SHOWcommand, which output often cannot be filtered directly, fetches (or exposes) its information through the information_schema database.
Say you want to list all processes from a particular user. You could of course use the pager trick mentioned above, or, you could use the information_schema database:
SELECT * FROM information_schema.processes WHERE user='username';
There is no need to include the @hostname part because the hostname is stored in a different column.
Just like the
/procfilesystem on Linux, so does the information_schema database provide an easy to use insight into its core.
Speaking of the
/procI just this morning came across a rather nice little article about it, see here on Ksplice blog.