MySQL Forums
Forum List  »  Optimizer & Parser

select query optimize
Posted by: Robert Koziol
Date: February 10, 2011 05:14AM

I am out of ideas how to optimize my query. I have read all documents about order by and group by optimization and many other and I am still completely stuck.

I have created two indexes to make the query look simpler:

create view myview as SELECT * from jos_bookmakers order by insert_date desc;
create view myview2 as select * from myview as x1 group by x1.event_id, x1.bookmaker_id;

The query looks like this:

select je.id as id
from jos_events je JOIN
(select * from (select * from myview2 as x2 order by event_id desc, first_value desc) as x3 group by event_id) as bo on bo.event_id = je.id
JOIN
(select * from (select * from myview2 as x2 order by event_id desc, second_value desc) as x3 group by event_id) as boo on boo.event_id = je.id
JOIN
(select * from (select * from myview2 as x2 order by event_id desc, draw_value desc) as x3 group by event_id) as book on book.event_id = je.id
group by je.id having count(je.id)>=1


The query's explain:

+----+-------------+----------------+--------+---------------+-------------------------+---------+---------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+--------+---------------+-------------------------+---------+---------------+-------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4548 | Using temporary; Using filesort |
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 4548 | Using where |
| 1 | PRIMARY | <derived6> | ALL | NULL | NULL | NULL | NULL | 4548 | Using where |
| 1 | PRIMARY | je | eq_ref | PRIMARY | PRIMARY | 4 | book.event_id | 1 | Using where; Using index |
| 6 | DERIVED | <derived7> | ALL | NULL | NULL | NULL | NULL | 9638 | Using temporary; Using filesort |
| 7 | DERIVED | <derived12> | ALL | NULL | NULL | NULL | NULL | 9638 | Using filesort |
| 12 | DERIVED | jos_bookmakers | index | NULL | bookmakers_events_index | 8 | NULL | 45438 | Using temporary; Using filesort |
| 4 | DERIVED | <derived5> | ALL | NULL | NULL | NULL | NULL | 9638 | Using temporary; Using filesort |
| 5 | DERIVED | <derived10> | ALL | NULL | NULL | NULL | NULL | 9638 | Using filesort |
| 10 | DERIVED | jos_bookmakers | index | NULL | bookmakers_events_index | 8 | NULL | 45438 | Using temporary; Using filesort |
| 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 9638 | Using temporary; Using filesort |
| 3 | DERIVED | <derived8> | ALL | NULL | NULL | NULL | NULL | 9638 | Using filesort |
| 8 | DERIVED | jos_bookmakers | index | NULL | bookmakers_events_index | 8 | NULL | 45438 | Using temporary; Using filesort |
+----+-------------+----------------+--------+---------------+-------------------------+---------+---------------+-------+---------------------------------+


Anyone could help? I have been fighting with this problem for a couple of weeks - and I have been even starting to consider trying another DB system.

Regards

Options: ReplyQuote


Subject
Views
Written By
Posted
select query optimize
3103
February 10, 2011 05:14AM
1404
February 12, 2011 06:56PM
1365
February 14, 2011 06:26AM


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.