Top 08 hack for Boost MySQL Performance

What you Learn, MySQL

1. Use The InnoDB Storage Engine not MyISAM
2. Benchmark Your Queries
3. Check Your Indexes
4. Use Invisible Indexes
5. Use Prepared Statements
6. Use Functional Indexes
7. Run ANALYZE TABLE Regularly
8. Backups – util.dumpInstance()

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 table

2. 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.

3. 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.

4. Use Invisible Indexes
   • But before you get rid of that unused index, please check the server uptime.
   • If the system is fairly recently rebooted 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.

5. 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.

6. 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?

7. 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 traveling between cities – you will get there eventually but there may be a better way.

8. Backups – util.dumpInstance()
   • 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

Follow Our Laravel.Tips account on Instagram to get advanced tips on Laravel.