Help cleaning up some code

[odeits]

On Mar 9, 1:06 am, Dennis Lee Bieber <wlfr... at ix.netcom.com> wrote:
> On Sun, 8 Mar 2009 19:07:08 -0700 (PDT), odeits <ode... at gmail.com>
> declaimed the following in gmane.comp.python.general:
>
>
>
> > i get this error when running that query:
>
> > sqlite3.OperationalError: LIMIT clause should come after UNION not
> > before
>
>         Well, I did generate that as a bit of off-the-cuff...
>
>         Apparently SQL parses the UNION as a higher precedence than LIMIT --
> wanting it to apply to the final results of everything.
>
>         And since I'm trying to reduce the number of selects overall, I sure
> don't want to suggest making them subselects...
>
> select first long mess
> ...
> union
> select * from
>         (select second long mess
>                 ...
>                 limit 0, ?)
> union
> select * from
>         (select third long mess
>                 ...
>                 limit 0, ?)
>
>         Putting the limit last would have been okay if it weren't for the
> lack of a limit on the "first long mess" -- since, as I recall, you have
> the same limit for "second" and "third".
> --
>         Wulfraed        Dennis Lee Bieber               KD6MOG
>         wlfr... at ix.netcom.com              wulfr... at bestiaria.com
>                 HTTP://wlfraed.home.netcom.com/
>         (Bestiaria Support Staff:               web-a... at bestiaria.com)
>                 HTTP://www.bestiaria.com/

Doing all of this work in the query made me realize that all the
filtering can be done just on the ADS table, so i modified the query
you provided to this :

select adid, rundateid,priority, rundate, ni,city,state
	FROM ads NATURAL JOIN rundates NATURAL JOIN newspapers WHERE adid in

                        (
                            SELECT * from (
                                SELECT adid  from ads
                                    where status in (1, 3) and user
= :USER LIMIT 0, :STACK
                                                    )

                            UNION
                            SELECT * from (
                                SELECT adid from ads
                                    where status = 1 and time <
datetime("now", "-%d minutes") LIMIT 0,:STACK
                                                    )


                            UNION
                            SELECT * from (
                                SELECT adid from ads
                                    where status in (0, 2) and
priority in
                                            (
                                                select priority from
users natural join groups where user = :USER
                                            ) limit 0,:STACK
                                                    )
                        )

                            order by status desc, priority, time, adid
                            limit 0, :STACK

and i have achieved a 4x improvement in speed!!! thanks so much.