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.