optimize select query

[Robert Koziol]

Hi,

I have been fighting with my query for a couple of days and now I have decided to ask for a help. The query looks like this:

select je.id as id
from events je, bookmakers bo, bookmakers boo, bookmakers book
where je.league_id=any(select id from leagues where sport_id = 1)
and bo.event_id = je.id and boo.event_id = je.id and book.event_id = je.id
and bo.first_value = (select max(book1.first_value) from bookmakers book1 where book1.event_id = je.id and book1.insert_date = (select max(insert_date) from bookmakers where event_id = je.id and bookmaker_id = book1.bookmaker_id))
and boo.second_value = (select max(book2.second_value) from bookmakers book2 where book2.event_id = je.id and book2.insert_date = (select max(insert_date) from bookmakers where event_id = je.id and bookmaker_id = book2.bookmaker_id))
and book.draw_value = (select max(book3.draw_value) from bookmakers book3 where book3.event_id = je.id and book3.insert_date = (select max(insert_date) from bookmakers where event_id = je.id and bookmaker_id = book3.bookmaker_id))
group by je.id having count(je.id)>=1

My explain looks like this:

+----+--------------------+----------------------------+-----------------+--------------------------------------+-------------------+---------+-----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------------------------+-----------------+--------------------------------------+-------------------+---------+-----------------------------+------+-------------+
| 1 | PRIMARY | je | index | PRIMARY | PRIMARY | 4 | NULL | 1417 | Using where |
| 1 | PRIMARY | bo | ref | bookmakers_events | bookmakers_events | 4 | baza51_findsure.je.id | 1 | Using where |
| 1 | PRIMARY | boo | ref | bookmakers_events | bookmakers_events | 4 | baza51_findsure.je.id | 1 | Using where |
| 1 | PRIMARY | book | ref | bookmakers_events | bookmakers_events | 4 | baza51_findsure.bo.event_id | 1 | Using where |
| 7 | DEPENDENT SUBQUERY | book3 | ref | bookmakers_events | bookmakers_events | 4 | baza51_findsure.je.id | 1 | Using where |
| 8 | DEPENDENT SUBQUERY | bookmakers | ref | bookmakers_events,bookmakers_bookies | bookmakers_events | 4 | baza51_findsure.je.id | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | book2 | ref | bookmakers_events | bookmakers_events | 4 | baza51_findsure.je.id | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | bookmakers | ref | bookmakers_events,bookmakers_bookies | bookmakers_events | 4 | baza51_findsure.je.id | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | book1 | ref | bookmakers_events | bookmakers_events | 4 | baza51_findsure.je.id | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | bookmakers | ref | bookmakers_events,bookmakers_bookies | bookmakers_events | 4 | baza51_findsure.je.id | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | leagues | unique_subquery | PRIMARY,leagues_sports | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+----------------------------+-----------------+--------------------------------------+-------------------+---------+-----------------------------+------+-------------+



The query runs very slow (15 seconds on my machine). Could anyone help me with the optimization?

I have discovered that after removing conditions with insert_date - for example book3.insert_date = (select max(insert_date) from bookmakers where event_id = je.id and bookmaker_id = book3.bookmaker_id) the query runs much faster.

Regards