One to many to many

Question

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:

• monitor(id, ...)

• 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?

edit:

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?

Answer 1

You can simplify that specific query:

SELECT COUNT(1)
FROM results r 
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'

If you don't have it already, try indexes like:

CREATE INDEX ... ON results (monitor_id, completed, id);
CREATE INDEX ... ON steps (result_id, id);  

Answer 2

Relational databases excel at joins. You could fetch all the data at once in a single result set:

select * from monitor m join result r on r.monitor_id=m.monitor_id join steps s on s.result_id=r.result_id
where m.monitor.id = ? and timestamp >= ?

But I cannot guarantee that this makes a difference. You can generate the execution plans for all the statements you tried to tell if there're any bottlenecks or the engine makes mistakes in optimizations.

Answer 3

You need the "Compound" index: INDEX(monitor_id, completed)