5 Simple Tips to Improve Your Website’s Database Performance
Your database is the backend workhorse for your site, so you should always keep it finely tuned to avoid performance issues. A poorly tuned database might work fine for a while, but it eventually suffers performance issues if tables aren’t indexed correctly, code is poorly created, and data is improperly stored. With just a few small changes, you can significantly improve your database’s performance.
Here are a few ways to speed up your database and your website.
1. Place Indexes on Commonly Queried Columns
When you create queries you use the WHERE SQL clause to set up filters on your data sets. The columns commonly used for filtering data sets should be indexed. You should also place indexes on the column used as foreign keys. These keys link tables across your database, and they should be sorted for quick referencing.
Just be aware that your tables can be over-indexed, which can hurt performance rather than improve it, so you should only index columns used in your queries or for your foreign keys. Primary keys are automatically indexed when you design your tables You should always delete unused indexes.
2. Use NOCOUNT in Your Queries
Every time you run a query, you’ll notice that an aggregate count of rows listed at the bottom. The database counts the number of rows affected for your queries such as updates to a set of records. For your users they don’t need to know the number of files affected when they update their data.
The NOCOUNT directive tells the database to skip the aggregate count. Imagine that you run an update that changes thousands of records. When your database makes the changes, it keeps track of how many it does, which takes up unnecessary memory and CPU resources, and the NOCOUNT feature eliminates it. Always use NOCOUNT when you don’t need to know the number of records affected by your queries.
3. Use Stored Procedures Instead of Inline SQL
Inline SQL is familiar with PHP and WordPress developers. When a developer creates a WordPress plugin, querying the database is a standard feature in the code. The developer doesn’t have access to your database, so they use inline SQL instead. Inline SQL uses strings in your backend code that are later compiled and executed by the database.
With stored procedures the queries are already compiled, so it takes less time for them to run. Stored procedures are much faster than littering your code with inline SQL. Not only are they faster, but they also improve your site’s security. Inline SQL is particularly vulnerable to SQL injection attacks.
4. Don’t Use the Asterisk in SELECT Queries
Here is an example of a SELECT query that uses an asterisk:
SELECT* FROM Customers;
The asterisk in this query returns all columns in the Customers table. If you don’t need the columns in the front end, then you should not return them. The asterisk is a lazy way to create a query, and it hurts performance on your database. Instead, specify the columns you need in the query. The following is an example of a properly formed SELECT query:
SELECT first_name, last_name FROM Customers;
The above query takes much less memory to run and return results to your web server than using the asterisk.
5. Check That All Tables Have Primary Keys
It’s common for new webmasters to create tables without the necessary components that keep speed optimal. One missing feature is a primary key in each table. Primary keys are required for table performance, and they are automatically clustered indexes. You should always have one clustered index on your tables and it must be a column that has unique values.
Using the “Customer” table as an example again, you need a column that provides your clients a unique ID that won’t work with anyone else, making it an excellent column for your primary key. If you attempt to add a duplicate value in a primary key column, the database returns an error.
Coding for Future Performance
These five tips will improve your database performance, but you need to always think of performance as you design future components of your database. Most databases come with a performance monitoring tool, so you can identify any code problems before you deploy a stored procedure into production.
You will eventually need to add tables to your database, so always design your tables to follow best practices. Always use a primary key on your table and normalize it so that you don’t repeat data across multiple tables. Table and stored procedure optimization are both necessary when you create components in your database, and they can make or break your database speed.
It’s better to design a database with performance in mind. Don’t use these tips after you’ve already deployed a slow database. A slow database means a slow website, and this can harm your user engagement and customer acquisitions.