When not to use DB indexing.md

Don't use like or ilike 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 column created_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 and total 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 and user_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 and total

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