Oleg,
My guess is that the query runs slow because by adding
data you exceeded what your database can do in memory
and you need to do some kind of disk sort.
How about rewriting your query without the UNION and
the EXISTS to something like
SELECT * FROM media m
WHERE m.mediatype = (SELECT objectid FROM mediatype
WHERE medianame='Audio')
AND ( m.activity='347667'
OR m.objectid IN (
SELECT s.objectid
FROM intsetmedia ism, set s
WHERE ism.set = s.objectid
AND s.activity='347667'))
ORDER BY medianame ASC, status DESC
Regards,
Nikolaus Dilger
On Mon, 24 Mar 2003, Oleg Lebedev wrote:
Message
Please help me speed
up the following query. It used to run in 2-5 sec., but
now it takes 2-3
mins!
I ran VACUUM FULL
ANALYZE and REINDEX.
SELECT * FROM media
m
WHERE m.mediatype =
(SELECT objectid FROM mediatype WHERE
medianame='Audio')
AND EXISTS
(SELECT * FROM
(SELECT objectid AS mediaid
FROM media
WHERE activity='347667'
UNION
SELECT ism.media AS mediaid
FROM intsetmedia ism, set s
WHERE ism.set = s.objectid
AND s.activity='347667' ) AS a1
WHERE a1.mediaid = m.objectid
LIMIT 1)
ORDER BY medianame ASC, status DESC
Basically it tries
to find all Audios that are either explicitly attached
to the given activity, or
attached to the given activity via a many-to-many
relationship intsetmedia which
links records in table Interaction, Set, and Media.
I attached the
output of EXPLAIN and schemas and indexes on the tables
involved. Most of the
fields are not relevant to the query, but I listed them
anyways. I discarded
trigger information, though.
Thanks for your
help.
Oleg
*************************************
This email may contain privileged or confidential
material intended for the named recipient only.
If you are not the named recipient, delete this message
and all attachments.
Any review, copying, printing, disclosure or other use
is prohibited.
We reserve the right to monitor email sent through our
network.
*************************************
