Tuesday, February 8, 2011

MySQL Tricks

I've got two tips for you today.

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 LIMIT on that query....

Luckily, there are ways with which to stem the tide of information.

I am, of course, talking about the pager command.

\P pagercmd


pager pagercmd;

The venerable less or more are 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 fgrep, 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 egrep or grep -E

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 SHOW command, 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 /proc filesystem on Linux, so does the information_schema database provide an easy to use insight into its core.

Speaking of the /proc I just this morning came across a rather nice little article about it, see here on Ksplice blog.

No comments:

Post a Comment