How to Optimize Queries in your MySQL and MariaDB Databases

How to Optimize Queries in your MySQL and MariaDB Databases

MariaDB and MySQL are the best options when it comes to database management systems that use SQL (Structured Query Language). SQL is used in relational databases to store, manipulate and retrieve data.

SQL queries are simple to craft. However, poorly written queries run inefficiently, hence slowing down the server response time.  Inefficient queries can further degrade the performance of your application as information grows to thousands or millions of seconds.

This article focuses on the measures that you can take to increase the speed of SQL queries running on MySQL/MariaDB.

Note
Special note: if you are looking to improve you SQL server performance, you may also consider switching to a better web host. Go to HostAdvice’s best MySQL hosting page to find the leading web hosts in this category, including expert as well as user reviews.

Normalize your tables

Normalizing is the first step you should take when it comes to storing data in your database. Your tables should be well organized to reduce redundancy. You should also design rules to link data on different tables.

Formalizing SQL databases may require additional tables to avoid duplicating data. For instance, if you have a customer’s and an order’s table, you should not repeat the customer name on the orders table. Instead, you can use a Customer’s ID on the order’s table that links back to the main customer’s table.

Use the right data types

Ensure that you are using the right data types in tables. For instance, you can store the Customer ID filed on an ‘Integer’ Field instead of a ‘Varchar’ data type because the former is light-weight and faster to query.

Index all columns

All columns used in ‘where’, ‘group by’, ‘join’ and ‘order by’ clauses should be indexed from the beginning. This ensures that the database does not perform a full table scan when trying to retrieve records.

For instance, in the below query, the Customer_Id and Transaction_Date fields should be indexed:

Select * from orders where Customer_Id='1041' order by Transaction_Date asc

Use the Explain Keyword to analyze queries

If a query is running slow, precede it with ‘Explain’ keyword to see its behaviour and get the information of how the statement is operating behind the scenes.

For instance, we can repeat our query above using the explain keyword as follows:

Explain select * from orders where Customer_Id='1041' order by Transaction_Date asc

Once you run a query like this, you should check the column possible_keys’ on the record set returned. If the value of possible_keys’ is ‘null’, you can add indices to speed up the query.

Use ‘union by’ to speed up queries

The like statement can be extremely slow if it is run on multiple columns. For instance consider the query below:

Select * from customers where First_Name like 'fra%' or Last_Name like 'joh%'

We can optimize the same query by using a union clause as shown below:

Select * from customers where First_Name like 'fra%' union Select * from customers where Last_Name like 'joh%'

Avoid using functions on Indices

When performing queries, using a function on an indexed column causes MySQL/MariaDB to ignore the index. For instance, if you have indexed the ‘Company_Name’ field in a vendor’s table and use a query like the one below, the Index will be ignored making the query to run slowly.

Select * from vendors where Upper(Company_Name)='ABC'

Don’t use a wild character in front of an index

The below query will cause MySQL/MariaDB to do a full table scan.

Select * from customers where First_Name like '%fra%'

Instead, use the syntax below:

Select * from customers where First_Name like 'fra%'

Only include the required columns on the select statement

For instance, if you want to return an order list with ‘Order_Id’ and ‘Amount’, you can use the query below:

Select Order_Id, Amount from orders

The query above will run faster than the one below because the latter requests all fields.

Select * from orders

Conclusion

The above are the different ways you can use to optimize queries on your MySQL/MariaDB servers. The tips should get you started although the specifics of fine-tuning a database performance depend on what you want to accomplish in your web application or website. In case you detect slow queries, try to tweak the syntax of your SQL using the recommendations above and you will see great improvements in your MySQL/MariaDB database performance.

Check out the top 3 MySQL hosting services:

Kamatera
AU$6.23 /mo
Starting price
Visit Kamatera
Rating based on expert review
  • User Friendly
    3.5
  • Support
    3.0
  • Features
    3.9
  • Reliability
    4.0
  • Pricing
    4.3
A2 Hosting
AU$4.66 /mo
Starting price
Visit A2 Hosting
Rating based on expert review
  • User Friendly
    4.5
  • Support
    4.0
  • Features
    4.5
  • Reliability
    4.8
  • Pricing
    4.0
FastComet
AU$2.79 /mo
Starting price
Visit FastComet
Rating based on expert review
  • User Friendly
    4.7
  • Support
    5.0
  • Features
    4.8
  • Reliability
    4.5
  • Pricing
    5.0

How to Backup Your MySQL Database on an Ubuntu 18.04 VPS or Dedicated Server

This article shows you how to back up a MySQL database on a Linux Ubuntu 18.04 s
3 min read
Eliran Ouzan
Eliran Ouzan
Web Designer & Hosting Expert

How to Backup and Restore MySQL from cPanel

This article teaches you how to backup and restore a MySQL database from within
2 min read
Max Ostryzhko
Max Ostryzhko
Senior Web Developer, HostAdvice CTO

How to Move Your WordPress Site from Your Local Web Server to Your Live Site

You can always accelerate the development process by developing your WordPress s
4 min read
Max Ostryzhko
Max Ostryzhko
Senior Web Developer, HostAdvice CTO

How to Disable MySQL 5 "Strict Mode" on an Ubuntu 18.04 VPS or Dedicated Server

How to Disable MySQL Strict Mode on MySQL 5.6 Running on an Ubuntu 18.04 VPS.
2 min read
Idan Cohen
Idan Cohen
Marketing Expert
HostAdvice.com provides professional web hosting reviews fully independent of any other entity. Our reviews are unbiased, honest, and apply the same evaluation standards to all those reviewed. While monetary compensation is received from a few of the companies listed on this site, compensation of services and products have no influence on the direction or conclusions of our reviews. Nor does the compensation influence our rankings for certain host companies. This compensation covers account purchasing costs, testing costs and royalties paid to reviewers.
Click to go to the top of the page
Go To Top