Re: optimize select query

[Robert Koziol]

I rewrote the query to get rid of correlated queries.

select je.id as id
from events je
join bookmakers bo on bo.event_id=je.id join (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) as b1 on b1.bookmaker_id = bo.bookmaker_id and b1.event_id = je.id

join bookmakers boo on boo.event_id=je.id join (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) as b2 on b2.bookmaker_id = boo.bookmaker_id and b2.event_id = je.id

join bookmakers book on book.event_id=je.id join (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) as 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

Unfortunately it is even worse now. My explain looks like this:

+----+--------------------+-------------+-----------------+--------------------------------------+-------------------+---------+------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------------+-----------------+--------------------------------------+-------------------+---------+------------------------------------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1064 | Using temporary; Using filesort |
| 1 | PRIMARY | boo | ref | bookmakers_events,bookmakers_bookies | bookmakers_events | 4 | b1.event_id | 1 | |
| 1 | PRIMARY | book | ref | bookmakers_events,bookmakers_bookies | bookmakers_events | 4 | baza51_findsure_copy.boo.event_id | 1 | Using where |
| 1 | PRIMARY | je | eq_ref | PRIMARY | PRIMARY | 4 | baza51_findsure_copy.book.event_id | 1 | Using where |
| 1 | PRIMARY | bo | ref | bookmakers_events,bookmakers_bookies | bookmakers_events | 4 | baza51_findsure_copy.book.event_id | 1 | Using where |
| 1 | PRIMARY | <derived5> | ALL | NULL | NULL | NULL | NULL | 1064 | Using where |
| 1 | PRIMARY | <derived8> | ALL | NULL | NULL | NULL | NULL | 1064 | Using where |
| 11 | DEPENDENT SUBQUERY | leagues | unique_subquery | PRIMARY,leagues_sports | PRIMARY | 4 | func | 1 | Using where |
| 8 | DERIVED | <derived9> | ALL | NULL | NULL | NULL | NULL | 1509 | Using temporary; Using filesort |
| 9 | DERIVED | <derived10> | ALL | NULL | NULL | NULL | NULL | 1509 | Using temporary; Using filesort |
| 9 | DERIVED | book1 | ref | bookmakers_events,index_insert_date | bookmakers_events | 4 | bo.event_id | 1 | Using where |
| 10 | DERIVED | bookmakers | ALL | NULL | NULL | NULL | NULL | 994 | Using temporary; Using filesort |
| 5 | DERIVED | <derived6> | ALL | NULL | NULL | NULL | NULL | 1509 | Using temporary; Using filesort |
| 6 | DERIVED | <derived7> | ALL | NULL | NULL | NULL | NULL | 1509 | Using temporary; Using filesort |
| 6 | DERIVED | book1 | ref | bookmakers_events,index_insert_date | bookmakers_events | 4 | bo.event_id | 1 | Using where |
| 7 | DERIVED | bookmakers | ALL | NULL | NULL | NULL | NULL | 994 | Using temporary; Using filesort |
| 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 1509 | Using temporary; Using filesort |
| 3 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 1509 | Using temporary; Using filesort |
| 3 | DERIVED | book1 | ref | bookmakers_events,index_insert_date | bookmakers_events | 4 | bo.event_id | 1 | Using where |
| 4 | DERIVED | bookmakers | ALL | NULL | NULL | NULL | NULL | 994 | Using temporary; Using filesort |
+----+--------------------+-------------+-----------------+--------------------------------------+-------------------+---------+------------------------------------+------+---------------------------------+


Did I rewrite the queries in a bad way?