-2

I have a MySQL query :

SELECT p.* FROM posts p
WHERE 
(
    p.userid in 
    (
        select blogid from abos where userid=11
    )
    OR p.userid = 11
) 
AND NOT EXISTS
(
    SELECT null FROM posts_denied r 
    WHERE r.post_id = p.id AND r.userid = 11
)
order by p.id DESC limit 5

I would like to remove the "where in" clause... How can I find the best performance query syntax please?

Joe Enos
  • 39,478
  • 11
  • 80
  • 136
Recif
  • 311
  • 2
  • 8
  • 19

1 Answers1

0
SELECT DISTINCT p.*
FROM posts p
JOIN abos a
ON p.userid = a.blogid OR p.userid = 11
LEFT JOIN posts_denied r
ON r.post_id = p.id AND r.userid = 11
WHERE (a.userid = 11 OR p.userid = 11)
  AND r.post_id IS NULL
ORDER BY p.id DESC
LIMIT 5
Steve Chambers
  • 37,270
  • 24
  • 156
  • 208
  • Thanks Steve. Is the "distinct" clause for performances? The tables will be huge... – Recif Aug 09 '13 at 20:18
  • `DISTINCT` is in case the `JOIN`s bring up multiple matches - using `EXISTS` or `NOT EXISTS` doesn't suffer from this problem. – Steve Chambers Aug 09 '13 at 20:21
  • And this is not possible to build it with not exists or exists? Will it have better performances? – Recif Aug 09 '13 at 20:27
  • Not sure how the performance will compare really - could you just try both and see? – Steve Chambers Aug 09 '13 at 20:32
  • Unfortunately the tables are not filled yet :-( Is there an online tool which can simulate that?... – Recif Aug 09 '13 at 20:34
  • You mean a tool to generate test data? See http://stackoverflow.com/questions/410653/test-user-data-fake-data#answer-410663 . I used http://sqlfiddle.com/#!2/bde25/14 for a bit of bare-minimum testing of this answer. – Steve Chambers Aug 09 '13 at 20:40
  • Ok, I'll see what I can do with this... Thanks! – Recif Aug 09 '13 at 21:01