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'
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
I had to substitute y.radish by sr.radish in your query as y was only the name for the temporary table. It was really quick, but didn't provide the right outcome, as there were duplicated instances of g.enton, which was not the case in the original query
EDIT: Never mind, it was because of the ordering. Thank you, with the restructuring, it was much quicker