MySQL Forums
Forum List  »  Optimizer & Parser

Re: select query optimize
Posted by: Rick James
Date: February 12, 2011 06:56PM

I can't figure out what this is supposed to do:
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

For starters, these are redundant:
group by je.id
having count(je.id)>=1
After the GROUP BY, there will be exactly 1 row for each je.id that survived the JOINs.

Are you looking for "events" where one row has the max first_value, max last_value, and draw_value?

Consider UNIONing the 3 subqueries and check for HAVING count(*) = 3.

Options: ReplyQuote


Subject
Views
Written By
Posted
3103
February 10, 2011 05:14AM
Re: select query optimize
1403
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.