A constant pain for every developer is how to handle sql queries of a database table with many, many, many rows. Well, here some tips for better sql queries.

1. Indexes

Indexes are basically shortcuts for the database — instead of scanning the whole table row by row, it can jump straight to what you need. For example, let’s say we’re always looking up orders by customer_id. Instead of the database digging through everything, we can help it out by creating an index. To create an index on a specified column you simply write CREATE INDEX idx_orders_customer_id ON orders(customer_id);

There are some types of indexes.

a. Primary Index: Automatically created on the primary key. Makes sure values are unique and quick to access.

b. Secondary Index: Something we create ourselves on non-primary columns (like customer_id) to speed up queries.

A good indicator that a column needs indexing is where it is used a lot in WHERE, JOIN or ORDER BY. You might say "why not index every column". Well, indexing it might hit you with slowdowns in insert/update operations because when a new record will be added on the table or you try to updated the indexing must be calculated. So, we need to carefully create indexing to only the columns that will actually be helpful.

2. SELECT only the required Columns

Another bad habit that many of us have (I have it too) is using * with the SELECT keyword. The most of the times we don't need every column of a table. Only request the columns that you need. This is because the more columns that you request the more you increase the memory usage.

3. Be cautious with you WHERE clauses

How fast your database will retrieve data depends on you WHERE clause. You must prevent using function on indexed columns or use functions that will be applied on every row of a table. For example don't use SELECT * FROM employees WHERE YEAR(joining_date) = 2022; because the YEAR function first must be applied to each row of the employees table.

Instead use the more efficient query SELECT * FROM employees WHERE joining_date >= '2022-01-01' AND joining_date < '2023-01-01'; that will give you the same result without any extra calculations