I have a large database of horse racing results. A normal query is very fast to run, however when using a subquery, performance is incredibly slow to the point it's not viable.
The purpose of the subquery is to determine the results of a given horse, prior to the date of the race. Hence the where clause.
I would like to improve performance and as I understand it using a join is much faster, however I haven't been able to get it to work successfully.
I would love to know how to join this particular query?
I apologize for not adding a SQL Fiddle, however the sheer size of the data required, even for a sample, is just too large.
select date, raceref, horse, rank,
WHERE horse = t.horse
AND date < t.date
) AS totalracesprior,
FROM results t
group by horse, raceref
order by raceref, horse
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t ALL NULL NULL NULL NULL 110088 Using where; Using temporary; Using filesort
2 DEPENDENT SUBQUERY results ALL NULL NULL NULL NULL 110088 Using where
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
results 0 PRIMARY 1 id A 110088 NULL NULL BTREE
You absolutely need an index for the column
You can either use the index
results(horse) or the index
results(horse, date, raceref). The latter would provide full coverage of your subquery and probably yield better results, but it depends a little bit on your data if you would see a difference.