On Mar 9, 1:06 am, Dennis Lee Bieber <wlfr...@ix.netcom.com> wrote:
> On Sun, 8 Mar 2009 19:07:08 -0700 (PDT), odeits <ode...@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...@ix.netcom.com wulfr...@bestiaria.com
> HTTP://wlfraed.home.netcom.com/
> (Bestiaria Support Staff: web-a...@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.
--
http://mail.python.org/mailman/listinfo/python-list
