Don't use
like
orilike
with query.
✔️ select * from account where name = 'HBL';
❌ select * from account where name like '%HBL%';
If you are using function in a
where
clause you can't apply index on columncreated_at
❌ select SUM(total) from orders where YEAR(created_at) = 2013; Query actually see something like YEAR(...), string output needs to compare interger value.
Even if we preciely tell the database from where to where search the data in where
clause
that doesn't work. Database do ALL table scan instead of range (index)
❌ select SUM(total) from orders where created_at BETWEEN '2013-01-01 00:00:00' AND '2013-12-31 23:59:59';
Create on index on
created_at
andtotal
columns
✔️ select SUM(total) from orders where created_at BETWEEN '2013-01-01 00:00:00' AND '2013-12-31 23:59:59';
If you add
AND
operator in where clause, that will do full table scan
❌ select SUM(total) from orders where created_at BETWEEN '2013-01-01 00:00:00' AND '2013-12-31 23:59:59' AND user_id = 319;
If you add
user_id
in the index with the order,created_at
,total
anduser_id
❌ It does the index search but take too many columns in their search. Ultimately it takes time.
In indexes Column order matters. It goes from left to right
❌ Instead of above you need to re-arrange the index columns something like created_at
, user_id
and total
But still search space take many columns and take time because after the created_at, user_id will be search.
See here for more detail explanation and visualization https://stackoverflow.com/questions/2292662/how-important-is-the-order-of-columns-in-indexes
In indexes Inequality matters
✔️ Due to that you should add user_id
first in the index and then created_at
and total
.
:heavy_check_mark: select SUM(total) from orders where created_at BETWEEN '2013-01-01 00:00:00' AND '2013-12-31 23:59:59' AND user_id = 319;
You will see the drastic reduction on column search on index
Index is written for query. That means one index doesn't satisfy all type of queries. Suppose you have an index with above mentioned order like
user_id
,created_at
andtotal
Remove user_id
from query
❌ select SUM(total) from orders where created_at BETWEEN '2013-01-01 00:00:00' AND '2013-12-31 23:59:59';
Searching in done from index but it search all columns from index one by one. Not full table search.
So the moral of the long story is
You are a developer and index is your concern
I learn this lesson from Laracon EU talk "Things every developer absolutely, positively needs to know about database indexing - Kai Sassnowski" https://www.youtube.com/watch?v=HubezKbFL7E&ab_channel=LaraconEU