MySQL Performance Tuning

 

MySQL Performance Tuning: Top 10 Easy Tips

MySQL is a proven, reliable database that is quick to set up easy to maintain, and has outstanding performance. However, that performance can usually be improved with the judicious use of the following tips that are all easy to implement. 

The best way to proceed is to work with one schema or database and go through this list of tips. This is the type of work to do in small steps for each database on the server rather than trying to get all of one tip accomplished over the entire server instance.

 

#1 - Use The InnoDB Storage Engine not MyISAM

InnoDB is transactional, does point-in-time-recovery very well, locks at the row level, and a whole of other great stuff with your data.

MyISAM is none of those. Many years ago it was the prime choice for websites but InnoDB has superseded it.

Use an ALTER TABLE <table_name> ENGINE=InnoDB; to covert and usually the performance gains are evident immediately. It also helps to make sure that you refer to the MySQL manual on locking records to make sure that you queries are taking advantage of all the needed optional available.

 

#2 - Have Unique Primary Keys on Your Table

InnoDB stores data by the primary key and it is much better for you to define one that works for your data usage plans than have InnoDB pick one for you.  Uniqueness means there can be only row with that value witch greatly helps indexes return the data quested.

InnoDB does best when the key for each record monotonically increases. Some identified like UUIDs do not do that which makes then a poor choice for an InnoDB primary key. But they can be used as a secondary index and that secondary index will reference that unique primary key.

 

#3 - Use Invisible Columns in Cases Where Older Applications Need Them

Have an old application that you really do not want to re-engineer the tables or the queries but you need a primary key? Use an invisible column as the primary key.

 

#4 - Benchmark Your Queries

Save the query plan in FORMAT=TRADITIONAL and FORMAT=TREE along with the information on your data size for future reference and the output from SHOW CREATE TABLE. When the inevitable cries of ‘the database is slow’ rise there is now a basis from which to do your assessment.

Without a reference there is no way to determine what, if anything, has changed.

 

#5 - Check Your Indexes

MySQL Workbench will report on unused indexes. Unused indexes take up memory and need to be maintained by the server. If you are not using an index then get rid of it to free up resources.
In addition, Workbench can tell you what queries are running without indexes. These queries would be good candidates for investigation to see if an index would appreciably increase the performance of a query.

 

#6 - Use Invisible Indexes

But before you get rid of that unused index, please check the server uptime. If the system is fairly recently reboot then the system may not have built up the statistics on that index as it runs only once a week or so. When in doubt make that index invisible so that the optimizer is unaware of it but it can quickly be made visible just in case it is needed for a monthly or quarterly report.

 

#7 - Use Prepared Statements

You do not need to send the entire query over each time along with the new data. Prepared statements allow you to send over just the needed data, which can be big wins in bandwidth and time.

Many MySQL connectors for the various programming languages also help reduce the occurrences of SQL Injection problems as it will check the type of the parameters being passed so that the ‘Little Bobby Drop Tables’ can not happen.

 

#8 - Use Functional Indexes

Functional indexes are a handy way to use a calculation on values to speed search. Need to be able to search for rows where you want the combination of the cost of goods sold and the shipping are a certain amount or the birth month of a customer is February?

But be careful! Functional index are order dependent. This means that if you create and index as (the cost of goods sold + the cost of shipping) you have to pass the parameters in that exact order. So (the cost of goods sold + the cost of shipping) will use the index while (the cost of shipping + the cost of goods sold) will not.

 

#9 - Run ANALYZE TABLE Regularly

ANALYZE TABLE does an analysis of the keys in the index of a table and updates the statistics that the optimizer uses when determining the optimal query plan. If the statistics are out of date it is very much like using out of data directions for travelling between cities – you will get there eventually but there may be a better way.

 

#10 - Backups

A dog steward of data never has enough backups. With MySQLSH’s util.dumpInstance() and util.dumpSchema() there are fast and reliable ways to make backups of your data.  You can move a copy from the MySQL Data Service cloud to on premises and vice versa.

Also save those binary logs if you need to do precise point in time recovery.

And then make another backup.

If you are not regularly checking you backups and testing your data restoration methods then you are doing the equivalent of locking your keys in your home.

#11  - Use MySQL HeatWave on Oracle Cloud to Speed Your Queries

HeatWave is a massively parallel, high performance, in-memory query accelerator for MySQL Database Service that accelerates MySQL performance by orders of magnitude for analytics and mixed workloads.