Large-scale data is highly vulnerable to change. Even the smallest of modifications can have the biggest repercussions in terms of performance.
This makes tasks like SQL performance tuning extremely complicated, where a simple syntax adjustment can make or break performance. That's why you may want to consider these ten tips we've shared in our post below.
Quick MySQL Database and SQL Query Optimization Guidelines
1: Index Correctly
Indexes are data structures designed to boost the rate of data fetching operations related to tables in databases. Creating a unique index leads to individual data columns that don't superimpose on each other.
When done right, indexing enables faster access to your database. This means you'll be able to execute tasks like row selecting or sorting more rapidly.
2: Avoid SELECT * - Prefer SELECT
It is recommended that you specify the columns you require whenever possible in the SELECT clause. Try not to use SELECT * because it will have all the data fetched from the database - even that which you don't need.
The extra data fetching puts an unnecessary load on the database as well, slowing down the entire system as a result.
3: Steer Clear of Loops When Running Queries
Loops tend to slow down the whole sequence they are being used in. Therefore, SQL performance tuning tips suggest you should try to use bulk insert or update operations instead.
4: Checking if My Record Exists - the More Efficient Way
Typically, record entry matches are done using COUNT() queries. You can ensure greater efficiency with EXIT() because the query exits the moment a matching record is located. On the other hand, COUNT() ends up completely scanning the database regardless of which row the record is located.
5: Correlated Subqueries? Don’t Use Them!
The outer query or the parent query plays a big role in the results fetched by a correlated subquery. It executes data for each row, reducing the general statement execution rate.
6: Make Careful Use of Wildcard Characters
Wildcard characters are often applied as prefixes and suffixes. A leading wildcard (such as %), when used together with a concluding wildcard character, makes the database look through all records to find a match throughout the entire selected field.
7: Refrain from Using SQL Functions on the Right
Developers generally utilize certain methods or functions with their MySQL database and SQL statements. These elements may render one or more indexes useless. An example of this is applying the WHERE clause in a way that leaves an index inside AccountCreatedOn unusable.
8: Always Aim for Precise Outcomes when Fetching Data
This means you need to look for limited pieces of information wherever possible so that the database has to fetch lesser data. When lesser data is needed, the query will work quicker.
Also, instead of using an excessive amount of filters on the client-side, do as much filtering as you can on the server-side. This also restricts the amount of data needed to search, thereby speeding up the query.
9: Try to Load Lots of Data? Drop Index First
In case you must add hundreds or thousands of rows into a DBMS online, take advantage of a temporary table to load the information. Additionally, it is important to make sure there aren’t any indexes in this table.
In other words, moving data between tables is easier and quicker than loading data in bulk from an outside source. This means you can let go of indexes in your primary table and transfer data from temporary to the destination table before making those indexes again.
10: WHERE should be used instead of HAVING
The simple reason behind this is the way in which both these clauses work. HAVING clause selects all the rows then filters them one by one, working as a filter. It is, therefore, most suitable for exactly this purpose.
Also, statements with the HAVING clause are executed after queries containing the WHERE clause. This is why experts recommend giving preference to the latter.
1060