Database Engines


This article is intended to help you use industry best practices for managing your MySQL databases.

Choose a proper database engine

Generally, choosing the default engine type of InnoDB will result in better performance and robustness for your application. If you are using an off-the-shelf web application, ensure you understand which database engine is being used, and why the author chose the one they did. The MyISAM engine type has a significant performance impact on your application when performing backups of your dataset.

Use query caching

Most MySQL servers have query caching enabled by default, and utilizing it is one of the best methods of improving your performance.

Query cache stores the query of SELECT statements together with that statement’s result set. If an identical query is issued, MySQL returns the cached result, instead of executing the same work again.

Use SELECT * only when needed

It’s best to select the exact columns you need when performing SQL queries, as using SELECT * will return all columns. Depending on how many columns you have this could significantly slow down your response time. Instead, specify explicitly which column names are needed. Also, remember to use a WHERE clause for every SELECT.

SELECT tonight FROM whatarewegoingtodo;

Use EXPLAIN to understand your SELECT queries

When you use EXPLAIN, the result shows you how your tables are being scanned and exactly which indexes are being used. This can help you track down problems or give you valuable insights that might help you discover bottlenecks or other issues with your queries.

Add EXPLAIN before your SELECT.

Use LIMIT 1 for unique rows

Sometimes you already know before you write a query that you will only get a single row returned, as the row is unique. In these cases, adding LIMIT 1 can increase your performance. The database engine stops scanning after the first row found.

SELECT state,capital FROM states WHERE state = 'Texas' LIMIT 1;

Use the smallest datatypes wherever possible

Despite the fact that memory and drive space is fairly plentiful these days, utilizing the smallest datatype for your use case will still help you achieve the best performance for your database.

Consider using int instead of bigint, for numerals, and char, or varchar, instead of largechar for text fields.

Use the smallest columns possible

Remember that in most cases, disk performance will be your most significant bottleneck. Therefore, you should keep things as compact as possible to reduce the amount of disk transfers.

Use an ID field

Create every table with an ID field that is INT, and both AUTO_INCREMENT and the PRIMARY KEY. You may also choose to make it UNSIGNED, because it is always a positive number.

Even if you have a table that you are sure you have a unique text field, like states, using INT as the primary key is much faster than using varchar.

Use an INDEX whenever possible

When there are columns in your table that will be searched, you should almost always index them.

Note, however, that LIKE searches do not use indexes.

Use persistent connections sparingly

Persistent connections are meant to reduce overhead in establishing connections to MySQL, and sound great in theory, but can actually cause issues with connection limits, memory, etc.

Modern web servers run parallel connections extremely well by creating many child processes. In this environment, persistent connections simply aren’t very useful.

Use your backups periodically to test restores

It’s a good idea to create periodic backups of your data. While that is easy to say, or write, the step that many people forget is to actually test restoring their data on a periodic basis. Do your backups actually restore properly?

Have you documented your restore procedures so that you know exactly what needs to be done during an emergency? Backups are like a fire extinguisher in your kitchen, you won’t need one until you do. The ultimate validation of your backups is to restore them, bring your restored instance online, and validate that the data returned is correct and as up-to-date as the restore purports it to be. Also, you should have a fire extinguisher in your kitchen.

Use the ISO date format

When using DATE or DATETIME, always use the ISO date format YYYY-MM-DD. Regional formats like MM-DD-YYYY will not be stored properly.

Conclusion

The information presented here helps you make informed decisions regarding technologies and methods when managing your databases.


Related Content