MySQL Forums
Forum List  »  Optimizer & Parser

optimize select query
Posted by: Robert Koziol
Date: December 30, 2010 10:59AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
optimize select query
3110
December 30, 2010 10:59AM
1338
December 31, 2010 08:22PM
1512
January 03, 2011 01:02PM
1449
January 04, 2011 04:07AM
1655
January 06, 2011 09:37PM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

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.