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.
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:
- Want info about best web hosting? Clicking this link can be of great help.
