Optimising MySQL Queries for faster sites

If you want to run your MySQL queries faster than FLASH, then read on….

Parag Garg
Jul 8, 2019 · 6 min read

Table of Contents

Adding pagination to an API.

Let’s take an example where we need to create a paginated API which returns users data. For this we need to write SQL query, a basic version of query for this example would look like below :

SELECT id, name, company FROM users ORDER  BY id ASC LIMIT  10 offset 10;
SELECT id, name, company FROM users WHERE id > 10 ORDER BY id ASC LIMIT 10;

Not Equal Query in MySQL

Let’s take another example where we need to find all the records with != (Not equal) constraint. Indexes in MySQL do not work on queries with != constraint . Performance of != query on large data (for millions of record) can be very poor, because MYSQL will iterate each and every row and check if condition is true or not. Here is the example query below.

SELECT id, name, company FROM users WHERE company != 'tokopedia' LIMIT  10;
SELECT id, name, company FROM users WHERE category = 2 LIMIT 10;

Optimising OR Query

OR queries are not supported by indexes and therefore can be expensive if not optimised correctly. Let’s take an example of OR query and then optimise it to use indexes:

SELECT u.* 
FROM USER u
INNER JOIN company c
ON c.id = u.company_id
WHERE c.address_id = 1
OR c.NAME = 'TOKOPEDIA';
Index is not used and all rows are scanned (total dummy rows in company are 4)
SELECT u.* 
FROM USER u
JOIN company c
ON c.id = u.company_id
AND c.address_id = 1
JOIN company c2
ON c2.NAME = 'TOKOPEDIA'
AND c2.id = u.company_id;
Index is used and total rows to be scanned are 1 for each table.
SELECT u.* 
FROM USER u
JOIN company c
ON c.id = u.company_id
AND c.address_id = 1
UNION
SELECT u.*
FROM USER u
JOIN company c
ON c.id = u.company_id
AND c.NAME = 'TOKOPEDIA';

Count(1) vs Count(*) efficiency

Most of the time, to count total rows we use Select Count(*) from table_name. But this query is not that efficient because MySQL has to get all the columns before actually calculating the count. We can replace above query with below query to make it more efficient:

SELECT Count(primary_key_column) FROM table_name

Covering Index

Covering index is a special type of composite index where all the columns to be selected exist in the index. For example instead of writing Select * from user where id in (1,2,3) where we only need to know the first_name and last_name of the user we can create index i_f_l(id,first_name,last_name) and rewrite query to Select id, first_name, last_name from user where id in (1,2,3). Since all the selected columns are already available in the index, MySQL does not need to further lookup on the disk. It makes you query very fast!.

  1. On each insert/update/delete operation, index has to be changed as well. This makes these operations more expensive.

Sargable Queries

Just because we add an index to our table doesn’t mean we will get its benefit. Query running against that table needs to be written in such a way that it takes advantage of that index.

SELECT ...FROM   ...WHERE  created_at + interval 30day < now();
SELECT ...FROM   ...WHERE created_at < NOW() — INTERVAL 30days;

Composite Index

Composite index consists of multiple columns (e.g., index a_b(a,b) is a composite index which contains 2 columns in one index). Let’s have a look at few peculiar cases and see how MySQL makes use of the index a_b.

Tokopedia Engineering

Story from people who build Tokopedia

Thanks to Rajya Vardhan Mishra

Parag Garg

Written by

Senior Software Engineer @ Tokopedia | parag.garg37@gmail.com

Tokopedia Engineering

Story from people who build Tokopedia

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade