Index Table Properly

How to index table and check if the index is used when querying the table

To make queries faster, you can add an index to it. Index works like table of contents in a book. You mark where chapter 1 is, where chapter 2 is, etc.

Index Types

There are some types of index, but generally we only use B-Tree and Hash.

In simple terms, user Hash if the column is searched by “=” and use B-Tree if the column is searched using “>”, “<”, “=” and any other operator (B-Tree for general purposes).

Example

Let’s say we have table and query like this

SELECT * FROM posts WHERE created_at = '2024-06-01'

If the created_at field only queried using “=” operator, its better to use Hash index than B-Tree index. But if the created_at field queried using “<” or “>” like this query :

SELECT * FROM posts WHERE created_at > '2024-06-01' AND created_at <= '2024-06-09'

or

SELECT * FROM posts WHERE created_at BETWEEN '2024-06-01' AND '2024-06-09'

Its preferable to use B-Tree index because it can accomodate most of the operator used in WHERE or JOIN