sqlnaive (5/9/2013)
ChrisM@Work (5/9/2013)
kapil_kk (5/9/2013)
IgorMi (5/9/2013)
Hi,What does its execution plan say? Are there any missing indexes? How big are the tables? Check the statistics on them.
You can replace the IN with EXISTS and NOT EXISTS.
Regards
IgorMi
INdexes are there on the columns.. for less data it works fine but when data gets increased it takes more time to execute
Post the ACTUAL execution plan as a .sqlplan attachment so folks can analyse it.
Here's a close equivalent written in a more conventional style for your perusal:
SELECT y.id, y.english_nameFROM ys_movements y
INNER JOIN (
SELECT DISTINCT p.object_id
FROM profiles_useritems p
INNER JOIN django_content_type d
ON d.id = p.content_type_id
INNER JOIN follow_follow f
ON f.target_user_id = d.user_id
AND f.user_id = 8
WHERE d.name = 'movements'
AND d.app_label = 'yoga')
AND NOT EXISTS (
SELECT 1
FROM auth_user a
WHERE a.is_staff = 1
AND a.id = d.user_id)
) f
ON f.object_id = y.id
WHERE y.is_private =0
Kapil, as IgorMi and Chris suggested, EXISTS and NOT EXISTS works way faster that IN and NOT IN. The above query is perfect example of how it should be used. Eventually it gets better as the number of rows grow. cheers. 🙂
Not suggested by me. The performance of EXISTS and IN are very similar. Here's evidence [/url]and an explanation.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden