MySQL is the most popular open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL).
A single poorly-designed SQL query will pose a significant threat to the overall performance of your application. Therefore, optimizing query performance is essential. MySQL comes with tools that help us in the optimization of queries. Let’s have a look at the most important and useful tips to improve MySQL Query for speed and performance.
- Optimize Database
- Use Full-Text Searches
- Avoid Like Expressions With Leading Wildcards
- Optimize Like Statements With Union Clause
- MySQL Query Caching
If you optimize your MySQL queries and fail to come up with a good database structure, your database performance can still halt when your data increases. Optimizing your MySQL queries alone will not bring excellent database performance. A well-structured database is crucial along with an optimized query. Otherwise, in the event of a data surge, database performance will be adversely affected.
The following strategies will help you to optimize your database.
- Normalize Tables
- Use Optimal Data Types
- Avoid Null Values
- Avoid Too Many Columns
Use 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 underperform, you should consider using MySQL full-text search (FTS) because it is far much faster than queries using wildcard characters.
mysql>Alter table students ADD FULLTEXT (first_name, last_name);
mysql>Select * from students where match(first_name, last_name) AGAINST (‘Jones’);
In the above example, we have specified the columns that we want to be matched (first_name and last_name) against our search keyword (‘Jones’).
Only a single row will be scanned even if our students’ database has huge rows and this will speed up the database.
Avoid Like Expressions With Leading Wildcards
When creating an index, the equality conditions in the WHERE and JOIN conditions are really important. For instance, conditions such as name = ‘John’ will allow the database to filter most of the rows from the table and run through a small number of rows to return the required results. Thus, we should start indexing by adding these columns to the index. MySQL is not able to utilize indexes when there is a leading wildcard in a query.
Optimize Like statement 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.
If you have an index that can optimize one side of the query and a different index to optimize the other side, a union clause can make the query run faster.
MySQL Query Caching
Caching is used to improve performance. It will faster the site or application. The MySQL query cache is a global one shared among the sessions. The query cache stores results of SELECT queries enabling the quick return of the query if an identical query is received in the future. When the server for the same query, MySQL ask will retrieve the results from the cache instead of running the query again. This will indeed fasten the process significantly. The results will be set in a memory cache like Memcached or Cassandra.