Improving Database Response Time Using Index

I had an experience when our web application has taken time to load a page the gets data from database. It’s around 6 seconds before the user can see the page. When it was new, less records, everything was fast.

I did an audit to the code checking the time of every process looking for the part that is taking a lot of time. The problem was some queries are joining tables with large rows.

The solution was to create column indexes.

From around 6 seconds it is able to load around 0.200 ms now.

Consider query below:
SELECT users.* FROM users
JOIN addresses ON addresses.user_id =
JOIN cities ON = addresses.city_id
JOIN provinces ON = addresses.province_id

If it takes time to load you could consider indexing address table
ALTER TABLE addresses ADD INDEX(user_id,city_id,province_id)

Note, the order of column index in addresses table is important. First is user_id, followed by city_id then province_id as also shown in the query.

While indexing speeds the read of query, it also has negative impact on write query as indexes needs to be updated when a new row is added or modified.

Combine indexing and cache technology to further increase response time.