Release the full potential of MySQL with these 10 power tips

This document looks under the hood to introduce you to some of the less well-known capabilities of MySQL.

MySQL is easy to learn and use, and comes with extensive documentation—two factors that have contributed a great deal to its popularity. However, given the speed at which the MySQL code base evolves, even veteran MySQL users are sometimes pleasantly surprised to discover hitherto-unexpected capabilities. This document looks under the hood to introduce you to some of these less well-known capabilities.

Note: The tips discussed below apply to MySQL 4.1.x and/or MySQL 5.0.x.

View query results in XML format

You can have MySQL return query results to you in well-formed XML (instead of the traditional tabular format), by invoking the MySQL command-line client with the additional --xml option. This is useful if you plan to integrate the query output with some other application. Here's an example:

Listing A

shell> mysql --xml

mysql> SELECT * FROM test.stories;
<?xml version="1.0"?>

<resultset statement="SELECT * FROM test.stories">
        <headline>This is a test</headline>
        <tstamp>2005-07-28 00:14:57</tstamp>

        <headline>This is the second test</headline>
        <tstamp>2005-07-28 00:15:11</tstamp>
2 rows in set (0.11 sec)

Quickly rebuild indexes

Normally, every time you make changes to the server's full-text search variables, you need to rebuild the full-text indexes on your tables to ensure that your changes are reflected. This can take a fair amount of time, especially if you have a large volume of data. A quicker alternative is to use the REPAIR TABLE command. Here's how:

Listing B

mysql> REPAIR TABLE content QUICK;
| Table     | Op     | Msg_type | Msg_text |
| content   | repair | status   | OK       |
1 row in set (0.05 sec)

Compress certain table types

If you have read-only MyISAM tables, MySQL lets you compress them to save disk space. Use the included myisampack utility for this, as illustrated below:

Listing C

shell> myisampackmovies.MYI
Compressing movies.MYD: (146 records)
- Calculating statistics
- Compressing file

Use conditional SQL

MySQL supports the use of conditional tests within an SQL query. Both IF and CASE structures are supported, and they can come in handy if you need your query results to change depending on the results of a conditional test. Here is a simple example:

Listing D

mysql> SELECT IF (priv=1, 'admin', 'guest') As usertype FROM privs WHERE username = 'joe';
| usertype |
| admin    |
1 row in set (0.00 sec)

Dump table data in CSV format

Normally, a MySQL dump file contains a complete list of the SQL commands needed to recreate the table. This is very handy if you intend to import the dump file back into MySQL at a later date, but isn't such a great idea if your target application (for example, Excel) doesn't understand SQL. In such situations, you can save yourself some time by telling MySQL to create dump files in CSV format, which can be easily imported into most applications. Here's how to do it using mysqldump:

shell> mysqldump -T . --fields-terminated-by=", " mydbmytable

This will produce a text file in the current directory, containing the records from the table mydb.mytable in comma-delimited format.

Reduce the incidence of "bad" data by activating strict mode

The MySQL server can be run in a number of different "modes", each one optimized for a particular purpose. By default, no modes are set; however, it's easy to alter this and run MySQL in "strict mode" by adding the following option to the server command line:

shell> mysqld --sql_mode="STRICT_ALL_TABLES" &

In strict mode, many of the server's automatic corrections are disabled, with MySQL aborting query execution and returning an error instead of internally correcting the bad input. Stricter date checking is also implemented in this mode.

Note: This mode is only available in MySQL 5.0.2 and better

Monitor the server

You can obtain a report of server activity and statistics—the number of open connections, the number of active queries, the server uptime, and so on—by issuing a SHOW STATUS command. Here's an example:

Listing E

| Variable_name    | Value |
| Aborted_clients  | 0     |
| Aborted_connects | 0     |
| Uptime           | 851   |
156 rows in set (0.16 sec)

Automatically retrieve CREATE TABLE code

MySQL lets you automatically obtain the SQL commands to re-create a particular table. Simply issue the SHOW CREATE TABLE command to see the table creation code, as in this example:

Listing F

mysql> SHOW CREATE TABLE products;
| Table    | Create Table
| products | CREATE TABLE `products` (
  `id` int(8) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `price` int(10) default NULL,
  PRIMARY KEY  (`id`)
1 row in set (0.27 sec)

Create a more useful command prompt

By default, the MySQL command-line client displays a simple mysql> prompt. However, you can use special modifiers to alter this prompt and make it more useful, by including the name of the currently-logged in user, the host name, and the currently-selected database. Here's how:

Listing G

mysql> prompt \U:/\d>
PROMPT set to '\U:/\d>'

You can obtain a complete list of modifiers supported by the MySQL client from the documentation files.