Database Indexing Explained

During a query, a database will scan all the rows of a table to find the matching data. Indexing serves the purpose of speeding up SQL searches/queries and functions the same as a book 📚 index. With the latter, you would refer to the index to find out on which page your desired content is located.

In a database, you create an index for one or more columns as follows.

CREATE INDEX index_name ON table_name (column_name_1, column_name_2, ...)

Pay attention to the order of the columns when creating your index as it will look up the first column in the index, then the next one, and so on in a query. Depending on which column data you require, it can have an impact on your query speed 💨.

Furthermore, any non-indexed column will benefit from the increase in speed if it's looked up together with an indexed column.

SELECT COUNT(*)
FROM table_name
WHERE non_indexed_column = 'value 1' AND indexed_column = 'value 2'