Achieving readable output with MySQL

Introduction

Although I like using GUI tools for a lot of things, there are some things I feel you should be able to do without being dependent on a graphical user interface. Using the MySQL console to administer a database is one of them. More than often your only channel of access to a MySQL database server is using a SSL connection to the server host. This is often the case if the database server is configured to disallow connections from any other host than the localhost.

A useful feature

Most users associated with MySQL are familiar with a SQL statement of the form “SELECT * FROM table;” to retrieve table information. It’s one of the first things you learn. However, if the table in question has a lot of columns and contains a lot of records, then you are going to find the MySQL console a bit of a pain when reading the data. Check out the familiar SQL output result in the figure below.

Default output for “SELECT * FROM table;”

The alternative

Of course you can limit the output listing by using a SQL WHERE clause and specifying the exact names of the columns of interest, but that is not the point of this posting and in some cases you may not be aware of column names you need to use (yes, I am aware of the ‘DESCRIBE table;’ command). As you know, you terminate each SQL statement with the default semicolon ‘;’ character. Without this character the database server console just waits for you to finish your SQL statement indefinitely, but there is an alternative. By replacing ‘;’ with ‘\g’ gives the exact same console output as before. OK, I know what you are thinking. Why is that any better? It’s not, however, replacing ‘;’ with the uppercase variant ‘\G’ is, and gives an output listing in a different, and often more readable, format listing each table record by it’s columns:

SELECT * FROM table \G

See the figure below showing the end of row number 2, the full row number 3, and the beginning of row number 4 from the same table as earlier:
The alternative to using the default SQL statement terminator

It was only recently that I stumbled across this feature, but I have found it useful on many occasions already.

Summary

I’m sure you can see the benefit of using this alternative statement termination character on occasion. As you can see, records with many columns are a lot easier to read without being forced to limit the record column output using a WHERE clause. I’m pretty sure that using the ‘\G’ termination character isn’t standard SQL, and not something you would want to use when terminating embedded SQL in your application, but a useful feature all the same. It definitely makes reading records easier even if the output may appear to be more verbose at first. You can of course use a WHERE clause or similar as before to narrow down your output further, but using ‘\G’ makes the output easier to read in many situations:

SELECT * FROM table WHERE id = ‘something’ \G

Advertisements

Removing default search limitations in MediaWiki and MySQL

Introduction

So let’s assume you have created a page in your Mediawiki site, named it with an appropriate page title consisting of only 3 characters, say SSL or SQL, only to find that it never shows up when searching for it in the MediaWiki site search. You have tried both uppercase and lowercase search query words, you haven’t misspelled the search query (only 3 characters, after all), and you are certain you are searching through the correct MediaWiki namespace. You are also certain that you saved the page, not only previewed it and then made the mistake of closing your browser.

Come to think of it, that page you are 110% certain you read yesterday, referring to the exact same search query word, say ‘SSL’, doesn’t show up either when searching for either of ‘ssl’ or ‘SSL’. How can that be? You didn’t delete the page by mistake, did you? This is getting annoying, but are you starting to see a pattern?

As we will see this has nothing to do with searching the wrong MediaWiki namespace (although easy enough to do), misspelling the search query word, distinguishing between uppercase or lowercase search query words, failing to save a page edit or deleting a page by mistake. The cause is simple: by default, the MySQL database server does not fulltext index words of 3 characters or below. To make this possible, you will have to change the configuration of both MediaWiki and MySQL to enable it to search for useful words and abbreviations of character length 3, such as apt, ssh, nfs and the like.

Editing the MediaWiki configuration

It is simple to correct this problem, but you need access to both the web server file system and administrator privileges to the MySQL database server. Locate the LocalSettings.php file in your MediaWiki installation directory, usually a subdirectory of your web server’s document root, say /htdocs on Apache. Edit the file and add the variable wgDBminWordLen to it. Set it’s value to 3, so:

$wgDBminWordLen = 3;

Editing the MySQL configuration

You must also tell the MySQL database server to index 3-letter words in fulltext indexes. This is the core of the problem and this configuration change will also apply to any application using the MySQL database server, not just MediaWiki. Add or edit the [mysqld] heading in your my.conf file with the following configuration line:

[mysqld]
ft_min_word_len=3

And that’s really all there is to it!

Recreate the MySQL table index

So just one more step to go before you can use it. You now need to restart your MySQL database server to load the new configuration setting, and then recreate the search index. So restart your server process, open a console and proceed to enter the MySQL database you use for your MediaWiki installation. Recreate the necessary search index by typing:

REPAIR TABLE searchindex QUICK;

If you prefixed your MediaWiki database tables with a prefix when installing the wiki then the name of your ‘searchindex’ table may be wk_searchindex or similar. A simple ‘show tables;’ query will give you a hint of the correct table name.

Summary

This particular fix took me a little while to find. Not that it was hidden, but I was unsure which component was limiting the search. As you have seen it turns out both MediaWiki and MySQL needed to be tweaked to correct the problem. Remember there is a reason why MySQL sets the index limitation to 3 characters by default. By changing it you will increase your index sizes leading to a performance hit. The default setting is 4 characters and I’m sure there is a statistical reason for this. Take a closer look at this page for more information on tuning the fulltext search in MySQL if you are unsure of the impact it might have.