Database Optimization

Database optimization refers to a variety of strategies for reducing database system response time. In this guide, We will take you through the steps of optimizing SQL queries and databases.

#1: Index All Columns Used in ‘WHERE’, ‘ORDER BY’ and ‘GROUP BY’ Clauses

Apart from guaranteeing uniquely identifiable records, an index allows MySQL server to fetch results faster from a database. An index is also very useful when it comes to sorting records.

MySQL indexes may take up more space and decrease performance on inserts, deletes and updates. However, if your table has more than 10 rows, they can considerably reduce select query execution time.

#2: Optimize Like Statements with Union Clause

Sometimes, you may want to run queries using the comparison operator ‘OR‘ on different fields or columns in a particular table. When the ‘OR‘ keyword is used too much in WHERE clause, it might make the MySQL optimizer to incorrectly choose a full table scan to retrieve a record.

A UNION clause can make the query run faster especially if you have an index that can optimize one side of the query and a different index to optimize the other side.

#3: Avoid Like Expressions with Leading Wildcards

MySQL is not able to utilize indexes when there is a leading wildcard in a query. If we take example on the students table, a search like this will cause MySQL to perform full table scan even if you have indexed the ‘first_name’ field on the students table.

#4: Take Advantage of MySQL Full-text Searches

If you are faced with a situation where you need to search data using wildcards and you don’t want your database to under perform, you should consider using MySQL full-text search (FTS) because it is far much faster than queries using wildcard characters.
Furthermore, Full Text Search can also bring better and relevant results when you are searching a huge database.

#5: MySQL Query Caching

If your website or application performs a lot of select queries (e.g. WordPress), you should take advantage of MySQL query caching feature. This will speed up performance when read operations are conducted.

#6: Avoid unnecessary columns in SELECT clause

Instead of using ‘SELECT *’, always specify columns in the SELECT clause to improve MySQL performance. Because unnecessary columns cause additional load on the database, slowing down its performance as well whole systematic process.

#7: Use INNER JOIN, instead of OUTER JOIN if possible

Use OUTER JOIN only when it is necessary. Using it needlessly not only limits database performance but also limits MySQL query optimization options, resulting in slower execution of SQL statements.

#8: Use DISTINCT and UNION only if it is necessary

Using UNION and DISTINCT operators without any major purpose causes unwanted sorting and slowing down of SQL execution. Instead of UNION, using UNION ALL brings more efficiency in the process and improves MySQL performance more precisely.

#9 : Use DISTINCT instead of GROUP BY when appropriate

If one of them is faster, it’s going to be DISTINCT. This is because, although the two are the same, a query optimizer would have to catch the fact that your GROUP BY is not taking advantage of any group members, just their keys. DISTINCT makes this explicit, so you can get away with a slightly dumber optimizer.

#10: Don’t Use MySQL as a Queue

Queues can really affect your database performance right from the core and could enter in your app databases without your knowledge. For instance, if you are setting up a status for particular item so that a ‘relevant process’ can access it, you are unintentionally creating a queue. What it actually does is that it builds up extra load time to access the resource without any major reason.

#11 : Avoid Unused Tables

No matter how carefully you plan the initial approach to the database, as time goes by, you inevitably end up with unused tables. Just do not hesitate: delete them! Having unused tables is quite similar to keeping things you don’t use in a full drawer. When you go looking for something, it can take you much longer to find it! The same thing happens in the databases: the systems have to track all the tables and elements until they find the answer to the query.

#12 : Use Optimal Data Types

MySQL supports different data types including integer, float, double, date, date_time, Varchar, and text, among others. When designing your tables, you should know that “shorter is always better.”

For instances, if you are designing a system user’s table which will hold less than 100 users, you should use ‘TINYINT’ data type for the ‘user_id’ field because it will accommodate all your values from -128 to 128.

Also, if a field expects a date value (e.g. sales_order_date), using a DATE_TIME data type will be ideal because you don’t have to run complicated functions to convert the field to date when retrieving records using SQL.

Use integer values if you expect all values to be numbers (e.g. in a student_id or a payment_id field). Remember, when it comes to computation, MySQL can do better with integer values as compared to text data types such as VARCHAR.

#13 : Avoid Null Values

Null is the absence of any value in a column. You should avoid this kind of values whenever possible because they can harm your database results. For instance, if you want to get the sum of all orders in a database but a particular order record has a null amount, the expected result might misbehave unless you use MySQL ‘ifnull‘ statement to return alternative value if a record is null.

In some cases, you might need to define a default value for a field if records don’t have to include a mandatory value for that particular column/field.

#14 : Avoid Too Many Columns

Wide tables can be extremely expensive and require more CPU time to process. If possible, don’t go above a hundred unless your business logic specifically calls for this.

Instead of creating one wide table, consider splitting it apart in to logical structures. For instance, if you are creating a customer table but you realize a customer can have multiple addresses, it is better to create a separate table for holding customers addresses that refer back to the customers table using the ‘customer_id’ field.

#15 : Optimize Joins

Always include fewer tables in your join statements. An SQL statement with poorly designed pattern that involves a lot of joins may not work well.

Conclusion

In this guide, we have shown you how to optimize your MySQL server for speed and performance.

This article provides in detail, the account of the best practices for database optimization and handy MySQL performance tuning tips every developer must know. It’s a complete guide for those backend devs, who are uncertain about their poor database performance and need some handy techniques to optimize MySQL database from the core.

We believe that the guide will allow you to craft better queries and have a well-structured database structure that will not only be simple to maintain but also offer more stability to your software applications or website.

Thats all about Database Optimization. 🙂