select query optimize

[Robert Koziol]

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