MySQL Forums
Forum List  »  Optimizer & Parser

Re: optimize select query
Posted by: Rick James
Date: January 06, 2011 09:37PM

Give this a try... I see two subqueries beginning "( SELECT * ". Put them into temporary tables, then add an index. Finally reformulate the main query using those two temp tables.
CREATE TEMPORARY TABLE b1
       SELECT  *
            from  
              ( SELECT  max(book1.first_value) as first_value,
                        book1.bookmaker_id, book1.event_id,
                        book1.insert_date
                    from  bookmakers book1
                    join  
                      ( SELECT  max(insert_date) as insert_date, event_id
                            from  bookmakers
                            group by  event_id, bookmaker_id
                      ) as bo on bo.insert_date = book1.insert_date
                              and  bo.event_id = book1.event_id
                    group by  event_id, bookmaker_id
                    order by  first_value DESC
               ) as x
            group by  event_id;
ALTER TABLE b1
    ADD INDEX(event_id, bookmaker_id);

CREATE TEMPORARY TABLE b2
      SELECT  *
            from  
              ( SELECT  max(book1.second_value) as second_value,
                        book1.bookmaker_id, book1.event_id,
                        book1.insert_date
                    from  bookmakers book1
                    join  
                      ( SELECT  max(insert_date) as insert_date, event_id
                            from  bookmakers
                            group by  event_id, bookmaker_id
                      ) as bo on bo.insert_date = book1.insert_date
                          and  bo.event_id = book1.event_id
                    group by  event_id, bookmaker_id
                    order by  second_value DESC
              ) as x
            group by  event_id;
ALTER TABLE b1
    ADD INDEX(event_id, bookmaker_id);

CREATE TEMPORARY TABLE   b3   
       SELECT  *
            from  
              ( SELECT  max(book1.draw_value) as draw_value,
                        book1.bookmaker_id, book1.event_id,
                        book1.insert_date
                    from  bookmakers book1
                    join  
                      ( SELECT  max(insert_date) as insert_date, event_id
                            from  bookmakers
                            group by  event_id, bookmaker_id
                      ) as bo on bo.insert_date = book1.insert_date
                           and  bo.event_id = book1.event_id
                    group by  event_id, bookmaker_id
                    order by  draw_value DESC
               ) as x
            group by  event_id;
ALTER TABLE b1
    ADD INDEX(event_id, bookmaker_id);

select  je.id as id
    from  events je
    join  bookmakers bo on bo.event_id=je.id
    join  b1 on b1.bookmaker_id = bo.bookmaker_id
      and  b1.event_id = je.id
    join  bookmakers boo on boo.event_id=je.id
    JOIN b2 on b2.bookmaker_id = boo.bookmaker_id
      and  b2.event_id = je.id
    join  bookmakers book on book.event_id=je.id
    join  b3 on b3.bookmaker_id = book.bookmaker_id
      and  b3.event_id = je.id
    where  je.league_id=any
      ( SELECT  id
            from  leagues
            where  sport_id = 1)
    group by  je.id
    having  count(je.id)>=1 ;

See how long each step takes. Then we can focus on the slower statement(s).

Also, try to turn
    where  je.league_id=any
      ( SELECT  id
            from  leagues
            where  sport_id = 1)
into a JOIN

Please us http://en.wikipedia.org/wiki/BBCode to improve readability.

Options: ReplyQuote


Subject
Views
Written By
Posted
3267
December 30, 2010 10:59AM
1390
December 31, 2010 08:22PM
1569
January 03, 2011 01:02PM
1505
January 04, 2011 04:07AM
Re: optimize select query
1735
January 06, 2011 09:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.