trentcroad trentcroad - 5 months ago 30
MySQL Question

MySQL - Improving performance of a slow subquery

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,

(
SELECT ifnull(count(raceref),0)
FROM results
WHERE horse = t.horse
AND date < t.date
) AS totalracesprior,

FROM results t
group by horse, raceref
order by raceref, horse


The output from EXPLAIN:

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


Indexes:

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

Answer

You absolutely need an index for the column horse.

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.

Comments