How can we optimize database query?
MySQL is one of the most popular open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL).
Useful tips to improve MySQL Query for speed and performance.
1. Optimize Your Database
1. Optimize Your Database
You need to know how to design schemas to support efficient queries. Well-designed queries and schema are crucial for your application to work properly.
Optimizing your MySQL queries alone will not bring excellent database performance. A well-structured database is crucial along with an optimized query.
The following steps will help you to optimize your database.
- a. Normalize Tables
- b. Use Optimal Data Types
- c. Avoid Null Values
- d. Avoid Too Many Columns
3. Index All Columns Used in ‘where’, ‘order by’, and ‘group by’ Clauses
4. Use Full-Text Searches -
MySQL full-text search (FTS) is far faster than queries using wildcard characters. To add a full-text search index to the students’ sample table, we can use the below MySQL command:
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.
5. Optimize Like Statements With Union Clause
6. MySQL Query Caching -
MySQL full-text search (FTS) is far faster than queries using wildcard characters. To add a full-text search index to the students’ sample table, we can use the below MySQL command:
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.
5. Optimize Like Statements With Union Clause
6. MySQL Query Caching -
- In order to enable the query caching, we need to add the following configuration directives.
- query_cache_size=SIZE
- query_cache_type=OPTION
- query_cache_size=SIZE
We can optimize performance at three different levels -
- Optimizing at the Database Level.
- Optimizing at the Hardware Level.
- Balancing Portability and Performance.
Comments
Post a Comment