Problem

I have a big SQL query counting a bunch of relations and doing some calculations. However, in some cases, it runs forever (very very long, taking up 99% of DB CPU).

Research

I searched through SO and found posts like SO1 , SO2 , SO3 and others. The answer seems to be using indices to speed up the query. Further I learned to use explain to get more information about my query. My problem is, that my explain results tells me, that I am already using indices of some sort. However, I must not be using them effectively. Unfortunately, my understanding of them is also very limited. See the result of explain further down.

Background information

All tables have been generated by Doctrine . All present indices have also been created by the ORM.

Tables

Here are the tables used in the query as well as their primary and foreign keys
| name | primary & foreign |
| ------------ | ------------ |
| a | id, ... |
| b | id, a_id, ... |
| c | id, b_id, ... |
| d | id, c_id, ... |
| e | id, d_id, ... |
| f | id, e_id, ... |
| g | id, e_id, ... |
| h | id, e_id, ... |
| i | id, e_id, ... |
| j | id, e_id, ... |
| k | id, e_id, ... |
| l | id, e_id, ... |
| m | id, e_id, ... |
| n | id, e_id, ... |
| o | id, e_id, ... |
| p_e | p_id, e_id |
| q | id, e_id, ... |
| r | id, ... |
| r_f | r_id, f_id |

Query

select 
    e.id, 
    e.value_1, 
    count(DISTINCT f.id) as f_count, 
    count(DISTINCT g.id) as g_count, 
    count(DISTINCT h.id) as h_count, 
    count(DISTINCT i.id) as i_count, 
    count(DISTINCT j.id) as j_count, 
    count(DISTINCT k.id) as k_count, 
    count(DISTINCT l.id) as l_count, 
    count(DISTINCT m.id) as m_count, 
    count(DISTINCT n.id) as n_count, 
    count(DISTINCT o.id) as o_count, 
    count(DISTINCT p_e.p_id) as p_count, 
    count(DISTINCT q.id) as q_count,
    m_counts.example_1_count,
    m_counts.example_2_count,
    m_counts.example_3_count,
    m_counts.example_4_count,
    m_counts.example_5_count,
    m_counts.example_6_count,
    m_counts.example_7_count,
    m_counts.example_8_count,
    m_counts.example_9_count,
    m_counts.example_10_count,
    m_counts.example_11_count,
    m_counts.example_12_count,
    m_counts.example_13_count,
    case when e.value_1 = 'example' 
      then 
          0
      else 
        case when count(DISTINCT f.id) > 0 
              then
              (
                        select round(sum((
                                select
                                case 
                                    when f_inner.value_1 = 'a1' then f_inner.value_2 * sum(r.a1)
                                    when f_inner.value_1 = 'a2' then f_inner.value_2 * sum(r.a2)
                                    when f_inner.value_1 = 'a3' then f_inner.value_2 * sum(r.a3)
                                    when f_inner.value_1 = 'a4' then f_inner.value_2 * sum(r.a4)
                                    when f_inner.value_1 = 'a5' then f_inner.value_2 * sum(r.a5)
                                    when f_inner.value_1 = 'a6' then f_inner.value_2 * sum(r.a6)
                                    when f_inner.value_1 = 'a7' then f_inner.value_2 * sum(r.a7)
                                    when f_inner.value_1 = 'a8' then f_inner.value_2 * sum(r.a8)
                                    when f_inner.value_1 = 'a9' then f_inner.value_2 * sum(r.a9)
                                    when f_inner.value_1 = 'a10' then f_inner.value_2 * sum(r.a10)
                                    when f_inner.value_1 = 'a11' then f_inner.value_2 * count(r.id)
                                    when f_inner.value_1 = 'a12' then f_inner.value_2
                                    else 0
                                end
                                from f as f_inner
                                inner join f_r as f_e on f_r.f_id = f_inner.id
                                inner join r as r on r.id = f_r.r_id
                                where f_inner.id = f_2.id
                        )), 4)
                        from e as e_inner
                        inner join f as f_2 on e_inner.id = f_2.e_id
                        where e_inner.id = e.id
                    )
          else
                  0
          end
      end as f_value_1
    from e as e
    inner join d as d on d.id = e.d_id
    inner join c as c on c.id = d.c_id
    inner join b as b on b.id = c.b_id
    inner join a as a on a.id = b.a_id
    
    left join f as f on e.id = f.e_id
    left join g as g on e.id = g.e_id
    left join h as h on e.id = h.e_id
    left join i as i on e.id = i.e_id
    left join j as j on e.id = j.e_id
    left join k as k on e.id = k.e_id
    left join l as l on e.id = l.e_id
    left join m as m on e.id = m.e_id
    left join n as n on e.id = n.e_id
    left join o as o on e.id = o.e_id
    left join p_e as p_e on e.id = p_e.e_id
    left join q as q on e.id = q.e_id
    
    left outer join (
        select e_2.id, 
            sum(case when m_2.type = 'ex1' then 1 else 0 end) as example_1_count,
            sum(case when m_2.type = 'ex2' then 1 else 0 end) as example_2_count,
            sum(case when m_2.type = 'ex3' then 1 else 0 end) as example_3_count,
            sum(case when m_2.type = 'ex4' then 1 else 0 end) as example_4_count,
            sum(case when m_2.type = 'ex5' then 1 else 0 end) as example_5_count,
            sum(case when m_2.type = 'ex6' then 1 else 0 end) as example_6_count,
            sum(case when m_2.type = 'ex7' then 1 else 0 end) as example_7_count,
            sum(case when m_2.type = 'ex8' then 1 else 0 end) as example_8_count,
            sum(case when m_2.type = 'ex9' then 1 else 0 end) as example_9_count,
            sum(case when m_2.type = 'ex10' then 1 else 0 end) as example_10_count,
            sum(case when m_2.type = 'ex11' then 1 else 0 end) as example_11_count,
            sum(case when m_2.type = 'ex12' then 1 else 0 end) as example_12_count,
            sum(case when m_2.type = 'ex13' then 1 else 0 end) as example_13_count

            from e as e_2
            left join m as m_2 on m_2.e_id = e_2.id

            group by e_2.id
    ) as m_counts on m_counts.id = e.id
    
    where a.id = 504
    
    group by e.id;

Explain Result

More thoughts

The present indices, as far as I understand, are for the foreign key relations between the tables. I believe that I require more indices than those, but I have no clue how/where to put them. I also read that left joins can be very costly, but if the relations might be null or non existant, they are required, from what I've read. Am I wrong with this assumption? Are there better ways to count these kinds of relations?

此外，这些性能问题，只发生在一些数据示例（一些a.id），而不是其他。我还没有发现，哪些场景的查询是快速的，哪些需要永远。