10 Database Optimization Best Practices for Web Developers

Database optimization has become crucial to web developers for improving the performance of web applications and thus bettering user experience. It might sound a bit unappealing to some, but the benefits are worth the work. If you optimize your database properly, you’ll improve performance, reduce bottlenecks, and save resources.

Luckily, there are some optimization techniques that don’t require DBA-level (database administrator) expertise in SQL-queries.

To help you get started, we’ll take a look at 10 best practices for web developers regarding how to optimize your database. Once you have these covered, you can always go deeper into database optimization.

1. Use EXPLAIN with Your SELECT Queries

Using the EXPLAIN keyword is a great way to quickly fix slow queries. It gives you insight into how your query will be carried out and help you find potential places for query optimization.

Simply type the EXPLAIN keyword in front of your SELECT query. This won’t trigger the actual query, but instead, you will see the execution plan.

When you take a closer look at the results, you’ll be able to identify potential bottlenecks or any other problems, like missing indexes, and reduce the number of scanned rows.

Thanks to the EXPLAIN tool, you can optimize your query or if necessary, even improve your table structures.

2. Add Indexes to Searched Columns

If you’re searching your table by a specific column, you’ll most likely be able to improve query performance by indexing the column.

By adding an index to your searched column, you can decrease the response time and optimize resource usage. And although an index might not be helpful with all queries, it’s definitely useful in most cases.

Keep in mind, though, that an indexed table will take more time to update than an unindexed one. This is because the indexes need to be updated, too. So, it’s wise to create indexes only for the columns that are searched frequently and not for tables that are updated more often than they are read.

3. Use Identity Fields Whenever Possible

Using an Identity field as the PRIMARY KEY in your tables is beneficial for several reasons.

First of all, it’s faster — you can simply use an integer in your queries, instead of a longer string field. This will save you some memory, as the integer can be much shorter.

Secondly, it’s safer — using application data fields can lead to many problems. For example, if you use a name or address as the PRIMARY KEY, you might run into issues if a client or user changes their name, moves, or even just makes a typo.

So, in order to speed up your queries and make your work more efficient, add an Identity column to every table so that you can use it as a PRIMARY KEY, with AUTO_INCREMENT and a suitable type of INT variable.

4. Avoid NULL Values by Default

As much as possible, use NOT NULL instead of NULL.

Generally speaking, opting for NOT NULL means faster queries thanks to more efficient use of indexes and avoiding the need to specifically test whether each value is NULL. On top of that, you’re using less storage, as according to MySQL docs, NULL columns require additional space.

Saving up space is especially important if you’re using a web host for your database, as even some of the best web hosting services do not offer unlimited storage. Now, it’s true that saving one bit per column doesn’t seem like a lot, but if you’re running an eCommerce store with hundreds of thousands of products, you’ll save a lot of resources.

Using NOT NULL, you’ll be able to work with your fields like with any variables while avoiding certain edge cases that might arise from using NULL.

Of course, there are still some cases where it’s more beneficial to use NULL — but in the majority of cases, you can achieve the same result with NOT NULL.

5. Use the Unbuffered Mode With Queries

To save time and memory, you can use unbuffered queries.

By default, SQL queries use the buffered mode. This increases your wait time and takes a toll on your resources, as the results aren’t returned before the query is finished — they’re stored in memory instead. If it’s a larger query and you have a huge database, then using buffered queries will require a lot of memory.

In contrast, when using an unbuffered query, your results won’t be stored automatically until the query has been executed. And you’ll be able to start working on them as soon as the first row has been retrieved.

But keep in mind that unbuffered queries don’t allow issuing further queries on the same connection while you’re working on the result set.

6. Make Your Columns Compact

Optimizing disk space is vital to keeping your database engine running well. A really simple way to ensure you’re not hindering performance is to use small and compact columns.

So, you should always choose the integer type that’s most useful for your application. For example, if you know your table isn’t going to have a huge number of rows, don’t automatically use INT as the primary key — you might actually benefit from using SMALLINT or even TINYINT instead.

The same goes for DATE and DATETIME — if you don’t specifically need the time part, simply use DATE instead. The DATETIME data type takes up 8 bytes and DATE takes only 3 — so this way you can save 5 bytes.

7. Keep Your Tables Static (Fixed-length)

Another great way to optimize your database performance is to use static tables.

This means that your table shouldn’t include any columns with variable lengths, like TEXT or BLOB. You can use CHAR, VARCHAR, BINARY, and VARBINARY columns, but they need to be padded so that they match the specified column width.

Using fixed-length tables is beneficial as they are faster and easier to cache. In addition to that, static tables are more secure — they’re easier to reconstruct after a crash.

However, static tables can in some cases require more disk space than dynamic-format tables — especially when you’re using CHAR and VARCHAR columns. But the improvement in performance will likely outweigh any concern for disk space.

8. Install an Object-Relational Mapper (ORM)

Using an ORM is another great way of optimizing the way you work with your database.

First off, an ORM helps remove the factor of human error as it does many things for you. You won’t have to write as much code yourself and you’ll reduce your workload as an ORM handles many repetitive tasks for you.

An ORM can also increase the security of your system as SQL injection is made a lot more difficult, thanks to queries being prepared and sanitized.

On top of all that, an ORM will cache entities in memory, which reduces the load on your database and CPU.

Naturally, an ORM has its pros and cons and might not be exactly perfect for your use. But there are some easy ways to avoid misuse of object-relational mapping. Also, there are some other performance-tuning and optimizer plugins that could be more suitable for you.

9. Run DELETE and UPDATE Queries in Batches

Deleting and updating data — especially in very large tables — can be complicated. It can take a lot of time and both these commands are executed as a single transaction. This means that in case of any interruptions, the entire transaction must be rolled back, which can be even more time-consuming.

However, if you follow the good practice of running DELETE and UPDATE queries in batches, you will be able to save time by increasing concurrency and reducing bottlenecks.

If you delete and update a smaller number of rows at a time, other queries can be executed when the batches are being committed to the disk. And any roll-backs that you might have to do will take less time.

10. Use PROCEDURE ANALYSE() to Get More Tips

A final best practice of optimizing a database is to use the built-in functionality: PROCEDURE ANALYSE(). More specifically, if added to one of your SQL statements, this command will take a look at your columns and will then recommend optimal data types and data lengths.

This might be especially useful after you have imported new data to your table — or even to check your existing tables for any inconsistencies.

If you implement the recommendations, you’ll likely be able to save some space. However, do keep in mind that these are only suggestions and you’ll have to actually consider if they are suitable for your specific purposes.

Conclusion

Database optimization is a bit tricky but avoiding it altogether can have a huge impact on your web application, resulting in performance problems.

If you follow these 10 best practices of database optimization for web developers, you’ll start on a path to improved user experience and resource management.

Image Credit