CODEWITHSUNDEEP
mysqlsqlquery-optimization
Laura Baker
Laura Baker

Reputation: 517

mysql query optimiser

I have a query written in mysql that is taking, what feels like, a ridiculous amount of time to complete. I really need to speed this query up, it is taking in excess of 7 hours to complete. There are 2 million row in the table that it is querying - historic_runner_races. I have thought about lots of different ways that I can optimise this, I have also tried sql server in case that was quicker. I have investigated all different types of index combinations and found that they didn't really make a massive difference in the over all run time.

The query is 

    insert into smartform.historic_jockey
    SELECT runners.id, 
     ( select count(1) 
      FROM smartform.historic_runners_races ru
     where ru.jockey_id = runners.jockey_id 
     and ru.race_id < runners.race_id 
     and ru.finish_position =  1
     ) jockeyWins

    FROM smartform.historic_runners_races as runners ;

I am really at a loss with this. The index's I have are on runner_is, race_id, and then jockey_id, race_id, finish_position.

Any advice is gratefully received.

Laura

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

For this query:

insert into smartform.historic_jockey (runner_id, jockyWins )
    select runners.id, 
           (select count(1) 
            from smartform.historic_runners_races ru
            where ru.jockey_id = runners.jockey_id and
                  ru.race_id < runners.race_id and
                  ru.finish_position =  1
           ) as jockeyWins
    from smartform.historic_runners_races runners ;

For this query, you want an index on historic_runners_races(jocky_id, finish_position, race_id). Run the select independently and see if that helps.

Upvotes: 2

Related Questions