A default installation of MySQL is easy to perform, but if you really want your databases to sing, you should tune them like you would tune a piano. In MySQL tuning pertains to either the application or the database system. In this post, we cover some common tuning techniques and best practices to increase your MySQL application performance. An upcoming post will discuss tuning the DB system via your my.cnf configuration file.
Optimize – If you are working with a MySQL database that has a table or tables that are constantly changing, disk space will be wasted and in need of optimization. This is where the OPTIMIZE Table command comes in handy. You should create automation around this command or run it periodically. In order to run this command on a table, perform the following query:
“OPTIMIZE TABLE <Database>.<Table>.”
Optimizing Queries – One of the most common issues today in MySQL database performance is un-optimized queries. Common issues include; queries that do not use indexes, queries that search full-text fields, queries that use select * and queries that overuse ORDER BY. If you can eliminate these rookie mistakes, you can avoid a lot of performance issues.
Indexes – When running queries it is important to make sure you research performance. You can review your indexes by running the query but putting the word Explain in front of it. For example, I ran the following simple query on the test database: Explain select * from sakila.actor.
It returned the following:
Now you have to realize, this simple query only had to run through 200 rows to return data — imagine if it had 50 million rows! Using the Explain statement will help you determine if you need an index by using the ALTER TABLE ADD INDEX command. Once the index is created, you can run the EXPLAIN Query to see performance improvement.
Select * – I love writing select * statements but that doesn’t mean you should. It’s much easier to do a select * then have to write the actual field names necessary. Writing your select statement with the actual information you need is a much better practice. I recommend using the DESCRIBE command to get a listing of all your fields to write efficient queries.
LIMIT – When writing queries, you should also use the LIMIT command if you only need a certain amount of rows. This will help in not returning thousands of records and increase performance
Scalability – Whether your databases are available locally or in the cloud, planning for scaling can be expensive and time consuming. In addition to cost, you have the limited capabilities of MySQL . Examples of scaling include sharding as well as using read only replicas. Using a vendor that specializes in enhancing the database scalability that MySQL lacks, you can move away from a DIY scale out architecture to a software thats whole business revolves around the success of your MySQL High Availability.
Subscribe to the Scalebase blog to stay informed about the latest MySQL topics, industry news and developments.
Sign up for a free database scalability assessment to find out how your application will benefit from scaling out.