I have just inherited an app with a MySQL database in need of performance tuning. One particular task that takes way too long involves three tables:
result(id, FK(monitor_id), timestamp, ...)
step(id, FK(result_id), ...)
where monitor is one to many results, and result is one to many steps.
Basic flow is the user asks for all the results for a monitor with monitor_id x and timestamp below y.
That works at an acceptable speed, but the user may also choose to see each individual step that makes up the result. The number and name of steps varies so they can't go into the result table.
My predecessor solved this by first get all results matching monitor_id and timestamp in one query.
select * from results where monitor_id = ? and timestamp >= ?
and then iterate through all results and fetch the matching steps, one query per each result.
select * from steps where result_id = ?
I have indexed the columns used in the where caluse which sped things up considerably but even though the query to get each step is fast, it is executed once per each result which can quickly go into thousands, or tens of thousands which together takes way too much time.
I have tried to do something like this query:
select * from steps where result_id in ( select id as result_id from results where monitor_id = ? and timestamp >= ? )
which gives a performance boost but still far from where I'd like it to be. This feels like a common problem, so my question is: what's the most time efficient solution to this bottleneck?
So I took @Falcon advice and tried it out. This is how a query can look:
select count(*) from monitors m join results r on r.monitor_id = m.id join steps s on s.result_id=r.id where r.monitor_id = 64 and r.completed >= '2014-10-01' and r.completed <= '2014-10-03'
This particular query retuns 1728 rows. MySQL's explain for this query gives:
select type table type key ref rows extra SIMPLE, m const PRIMARY, const, 1 SIMPLE, r range mon_id_compl null 575 Using where SIMPLE, s ref result_id db.r.id 2
However, queries like these take 5-10 seconds. The query uses the best indexes so that can't be the problem?
The tables' sizes are as following
monitors 133 results 8.441.842 steps 33.738.264
Is this too much to expect a quick query?