20 Tips and Tricks Any MySQL Database Developer Should Consider

Since its introduction in 1995, MySQL quickly became the world's most popular database management system. With that type of popularity comes great responsibility...and countless improvements, updates, and fanciful tricks.

Scour the web and there really is no shortage of MySQL tips and tricks. So which ones are the most helpful? Below you will find the list of the top 20 MySQL tips for experts and rookies alike.


20 Tips and Tricks Any MySQL Database Developer Should Consider

SQL Database Tips and Tricks

1. Do Not Edit Dump files

If you have ever seen the dump files created by mysqldump, they look an awful lot like regular, harmless text files. But for anyone who has ever tried to edit them, they have quickly learned that they are anything but a text file. Most people edit them in a standard text editor, and then the corruptions start to appear. The only guaranteed way to avoid problems is to leave the dump files alone.

2. MyISAM Block Size

One of the better kept secrets is a setting for block sizes in the indexes of MyISAM tables. The setting, myisam_block_size can be found in the .MYI files in the key buffer, as well as the disc. It has a default value of 1k, which is quite small to be optimal on a modern system. Most file systems will have used a larger size block. And we know that writing a single index block will require a read and then a write. The operating system will never have to write to the underlying disc, just so long as the block size is either equal to, or greater than filesystem block size.

3. Turning on delay_key_write

By default delay_key_write is turned off. The reason for this is because if you experienced a crash mi-project, you could end up with a corrupted database. So why would you want to turn it on? Simple. Because turning delay_key_write on ensures that the database will not flush the MyISAM key file after every single write. So, if you are doing another write in the near future, you will be saved quite a lot of time. Turning on delay_key_write is version specific. To see the differences in turning it on for your version use the official MySQL site manual.

4. Joins: Create Same Column Types and Don't Forget to Index

MySQL needs to know how to optimize JOIN operations. So, if you have an application with numerous JOIN queries, the columns you by have to be indexed on both of your tables. Make sure that the columns you join are the same type, including the character encoding for string columns. Otherwise, MySQL will try to do a full table scan. So anytime you need to pull information from your tables, just modify the queries to only retrieve user info from particular columns.

5. Define WHERE clause with LIMIT 1

So you are looking for a single row, but you're querying your tables. Why not optimize your performance by adding LIMIT 1 and returning the the exact query intentions? This way, instead of going through the entire table(s) or index it will stop scanning after it finds only one. An example that is used constantly across the web can be found at Twiwoo, but it is a pretty simple and straightforward command in any regard.

6. Adding EXPLAIN keyword to SELECT queries

Anyone using MySQL, new or experienced, can appreciate the convenience and simplicity of this tip. By adding the EXPLAIN keyword MySQL will do exactly that; it will give you a little better explanation of exactly what your query is doing. EXPLAIN can tell you how your tables are being scanned, utilized indexes, etc. Now, take a complex SELECT query with joins and type EXPLAIN in front of it. Results will appear in a table. Use it and save time.

7. Query Cache: Optimizing Your Queries

Query caching is usually enabled on MySQL servers. This is because when it comes to improving performance, query caching is one of the most effective methods for the database engine. The same query may be run multiple times. And it is much faster to retrieve the results from a cache. However, CURDATE (current date) has this function disabled. In order to implement query caching for a CURDATE query simply add a line of PHP in front of the query, defining the operator.

//CURDATE is not deterministic
$query = 'SELECT id FROM table WHERE publish_date = CURDATE()';

8. Use Stack Trace To Isolate Bugs

Sky SQL has some great tips and tricks for MySQL. So we are going to borrow this one from them, since it is too simple and too convenient to be left out. MySQL stack_trace can be used to isolate various bugs. Rather that dig into all the possible uses, the programmer might want to take note on how easily a null pointer can easily ruin your code. Also, how easy it could be to spot, track, and fix, using Sky SQL's tip. Follow their start to finish mini-tutorial here.

9. ENUMS, set sql_mode to TRADITIONAL

ENUMS can be confusing. Because they can have more than one value it is easy to create an error. ENUMS can equal the specified value, NULL, and then actually be read as an empty string. Of course, you will get warnings that something is wrong. Luckily there is an easy fix. Set mysql_mode to TRADITIONAL. Voila!

Two ways to set SQL mode:

//Start mysqld with
$--sql-mode=”modes”
//or
$sql-mode=”modes” (my.ini – Windows / my.cnf - Unix)
//Change at runtime, separate multiple modes with a comma 
$set [GLOBAL|SESSION] sql_mode='modes'
//TRADITIONAL is equivalent to the following modes:
STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_AUTO_CREATE_USER

10. MySQL Changing ROOT Password

It may seem basic but knowing how to change the ROOT password is equally important whether it is on your home operating system or on MySQL servers. Overzealous beginners and absent-minded experienced users sometimes cannot figure out why they cannot change certain settings, and are returning errors. It can be as simple as giving yourself ROOT access, and a user shouldn't have to Google it either. Here's how to set up ROOT password and how to change a users ROOT password.

//Straightforward MySQL 101
$mysqladmin -u root password [Type in selected password]
//Changing users ROOT password
$mysqladmin -u root -p [type old password] newpass [hit enter and type new password. Press enter]
//Use mysql sql command 
$mysql -u root -p
//prompt “mysql>” pops up. Enter:
$use mysql;
//Enter user name you want to change password for
$update user set password=PASSWORD (Type new Password Here) where User = 'username';
//Don't forget the previous semicolon, now reload the settings for the users privileges
$flush privileges; 
$quit

11. Database backup using Mysqldump command

Simple and straightforward, in no way negates importance or convenience in the world of PHP and MySQL. A command no programmer can live without is the simple, albeit short, backup of the database. And once you have memorized "Changing ROOT Password" (#10 above), you will naturally memorize this crucially important trick. Notice how similar the commands are for this action. To achieve your backup, use the following:

$mysqldump –user [user name] –password=[password] [database name] > [dump file]
//Also, rather than using “user” and “password” you can use the shortened switches “--u” or “--p”
//Add multiple databases by including them inline. For example:
mysqldump –user [user name] –password=[password] [first database name] [second database name] > [dump file]
//As many databases can be added sequentially inline. To back up all databases just add the operator [--all-databases]. Or if you don't feel like typing at all, just download 'automysqlbackup' from Sourceforge!

12. Fixing your Config Files

Certain tips and tricks in MySQL are amazingly convenient. And then there are tools like MySQL Tuner that are a god-send and deserve to be in a category all their own. MySQL Tuner is a Perl script that somehow can optimize your performance by suggesting changes to your configuration files.

Since MySQL Tuner is not a single specific tip for MySQL there are a variety of tweaks and mods that can be applied. It is something that can be used by novices and pros alike but the more it is used, the more tweaks you can learn to apply for your own use.

The manual --man db and all notes, necessary reading, and settings are printed out on the official project homepage. While it may seem intimidating at first, MySQL Tuner should quickly become your go-to tool for speeding up MySQL and testing your config files.

13. Identify Slow Queries

Just because MySQL does not track and record slow queries by default that doesn't mean that you can not make it do your bidding for you. As we have learned through these tips, MySQL has many functions outside of the default settings that can help the user greatly. In order to enable the slow-query-log edit the my.cnf config file and then run these commands under mysqld:

$long_query_time = 1
$log-slow-queries = /var/log/mysql/mysql-slow-query.log

That "1" means that any query over 1 second long will be logged. By default the setting is zero. Once you make the change to the setting the server needs a restart to confirm the changes.

Now, when a query that takes too long is spotted, use the EXPLAIN command we discussed earlier (#6) to learn why it is taking so long to execute.

14. Autoincrement: Direct Reset

One of the great default features of MySQL is the Autoincrement Column Index. When making a new row, the program will automatically calculate and apply the values to the table. However, this default value is not always needed in its default settings. A few helpful commands to change the settings that everyone will eventually need can be easily applied.

Reset Autoincrement Directly
ALTER TABLE [table name] AUTO_INCREMENT =1;
Drop Table and Recreate
DROP TABLE [table_name];
CREATE TABLE [table_name] {….};

15. Partitioning MySQL Tables

Sounds like MySQL 101? It is amazing, and revealing, just how many database programmers will either forget, try to get around, or outright refuse to partition their tables. All tables start out small and manageable. But sometimes they grow to thousands of rows and/or columns. Furthermore, what happens when that table that had a few thousand rows grows into hundreds of thousands of rows? This is more common that most think. Especially with a rapidly growing user database.

The only solution is to create partitions. These can be set to include however many rows and columns you like. The biggest feature of this, of course, is to shorten your look-up times. After all, no one wants to wait around while a query executes. (Please note not all versions of MySQL allow for partitioning of tables. Run SHOW PLUGINS; at prompt to determine if your version has it).

The most common partitions would be RANGE. Lets say we had a table of Universities organized by their founding date (for simplification). The RANGE partitions will list them by founding date regardless of what order they are in. That would be executed like so:

CREATE TABLE founded (order_date DATETIME NOTNULL –omit other columns)
PARTITION BY RANGE (YEAR (order_date))
PARTITION p_1900 VALUES LESS THAN (1900),
PARTITION p_2013 VALUES LESS THAN (2013)
PARTITION p_all VALUES LESS THAN MAXVALUE ) ;

So now we will have all of the Universities created from year 0 up to 1900. This would be the majority of Ivy League schools. The second column will include the rest up until 2013. The last command will show everything else. So, lets just say we forgot to add a year a university was founded. It will pop up here rather than going through the whole database. Or lets say we added a number in a year. This would appear here too. (ex. 17502)

16 ENUMS and SQL

ENUMS or enumerations can be a tricky tool in MySQL and sometimes requires the programmer to tread lightly. Users of PostgreSQL supports functions like CREATE DOMAIN and CREATE TYPE. But MySQL does not. Keep ENUM behavior and rules in mind for MySQL. This will save you a lot of work; no one likes to have to go back and re-enter all your names, fields, values, etc again. Even attempting to migrate them into tables later can cause a wide array of problems. For an idea of just how lightly you should tread, a wise place to start would be Komlenic's "8 Reasons Why MySQL's ENUM Data Type is Evil".

17. "Store IP Addresses as UNSIGNED INT"

A quick and priceless tidbit. Storing IP addresses this way actually allows MySQL to store them as integer values. Use INET_ATON() to convert the target IP to an integer. Anyone familiar with PHP might recall a similar function called ip2long. John Bafford has spent too much time with the most complete analysis on this technique, including every last detail down to saving money on storage costs.

18. Use Your Indexes to Create Tables

Save time in this process by killing two birds with one stone. An index can actually be created while the table itself is being created.

CREATE TABLE records (
name VARCHAR(50), 
age INT,
id_num INT, INDEX (id)

)

To create the index on multiple columns

ALTER TABLE (records ADD INDEX id(id_num), 
ADD INDEX name(name);.

19. Vertical Partitioning

This is just a tip to easier view or organize your tables. This can be used to structure your tables in a vertical manner. This can be useful to troubleshoot and some people use it as a performance enhancing tool. Many times this will result in cutting the table down once you realize how large it is. Thus, enhancing performance. However, users should note that vertical partitioning will not work with version 5.1. The storage engine can be found here on Launch Pad.

20. Storage Engines Matter

In MySQL there are two main storage engines. You can choose from InnoDB or MyISAM. We talked briefly of some tricks with MyISAM and stayed far away from InnoDB. But this is all personal preference. InnoDB tends to be a little more complicated and is more difficult for some to use and navigate. When in doubt try them both. But for beginners and novices, always start in MyISAM. Chances are you will stay there.

The Wrap-Up

MySQL is undoubtedly one of the most important and influential programs to ever appear in the world of computer science. We have outlined only 20 tips and tricks that everyone should know. But, the more you use MySQL, the more you will use these, develop your own, and find your own top 20 that you will rely on.

MySQL is so involved, and so versatile, that just when it looks like all the possible tricks and shortcuts have been discovered, someone else chimes in with a new way to use a new argument or setting.

Another wonderful result of the popularity of MySQL is that the development community has created tutorials for all levels of expertise. The top three sites suit the community well. They serve newbies and oldies alike, and are an integral part of the learning experience.

The MySQL main site launches right into tutorials right after the user's manual sections. For reference, check out the tutorial for 5.0 here. You will notice the left-hand sidebar has all the other versions listed as well. In addition, W3schools have extensive resources as well as MySQL Tutorial; who also have their own “tips” and “tricks” section as well with examples not listed here. With these tutorials and numerous forums, learning MySQL's tips and tricks gets easier as the list grows and grows with each user's submission.

So, the question now is, what will you discover and add to the MySQL community?