Slow triple inner join

Question

I have the following query (names were altered) and it is really slow. I don't know if it is that slow because it could be written better or because I am lacking indexes. Also, how should I create indexes, as most of the joins are on imaginary tables?

select y.radish, g.enton
from great g 
inner join(
    select sr.radish, sr.greatReferenceID
    from spaceRadish sr
    inner join(
        select s.id
        from super s
        inner join experiments e
        on s.CID = e.analysis) x
    on sr.springID = x.id) y
on g.id = y.greatReferenceID

Output from explain select:

'1', 'PRIMARY', '<derived2>', 'ALL', NULL, NULL, NULL, NULL, '14085960', ''
'1', 'PRIMARY', 'g', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'y.greatReferenceID', '1', ''
'2', 'DERIVED', '<derived3>', 'ALL', NULL, NULL, NULL, NULL, '287', ''
'2', 'DERIVED', 'sr', 'ref', 'springID', 'springID', '4', 'x.id', '831666', ''
'3', 'DERIVED', 'e', 'ALL', NULL, NULL, NULL, NULL, '3271', ''
'3', 'DERIVED', 's', 'ref', 'CID,CID_2', 'CID', '767', 'cpp.e.analysis', '16', 'Using where; Using index'

Answer 1

try avoid subbquery

    select y.radish, g.enton
    from great g 
    inner join spaceRadish sr ON  sr.greatReferenceID = g.id
    inner join super s  s.id = sr.springID
    inner join experiments e on s.CID = e.analysis 

and be sure you have proper index on

    table  great  composite index on (id, enton)
    table spaceRadish composite index on (greatReferenceID, springID)
    table super cmposite index on (id, cid)
    table experiments index  on analysis