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 = users.id
JOIN cities ON cities.id = addresses.city_id
JOIN provinces ON provinces.id = 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.