sql queries slower than expected

Posted by neubert on Stack Overflow See other posts from Stack Overflow or by neubert
Published on 2012-12-07T16:55:59Z Indexed on 2012/12/07 17:05 UTC
Read the original article Hit count: 188

Filed under:
|
|

Before I show the query here are the relevant table definitions:

CREATE TABLE phpbb_posts (
    topic_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    poster_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
    KEY topic_id (topic_id),
    KEY poster_id (poster_id),
);


CREATE TABLE phpbb_topics (
    topic_id mediumint(8) UNSIGNED NOT NULL auto_increment
);

Here's the query I'm trying to do:

SELECT p.topic_id, p.poster_id 
FROM phpbb_topics AS t 
LEFT JOIN phpbb_posts AS p 
   ON p.topic_id = t.topic_id 
      AND p.poster_id <> ... 
WHERE p.poster_id IS NULL;

Basically, the query is an attempt to find all topics where the number of times someone other than the target user has posted in is zero. In other words, the topics where the only person who has posted is the target user.

Problem is that query is taking a super long time.

My general assumption when it comes to SQL is that JOINs of any are super fast and can be done in no time at all assuming all relevant columns are primary or foreign keys (which in this case they are).

I tried out a few other queries:

SELECT COUNT(1) 
    FROM phpbb_topics AS t 
    JOIN phpbb_posts AS p 
        ON p.topic_id = t.topic_id;

That returns 353340 pretty quickly.

I then do these:

SELECT COUNT(1) 
    FROM phpbb_topics AS t 
    JOIN phpbb_posts AS p 
        ON p.topic_id = t.topic_id
            AND p.poster_id <> 77198;

SELECT COUNT(1) 
    FROM phpbb_topics AS t 
    JOIN phpbb_posts AS p 
        ON p.topic_id = t.topic_id
    WHERE p.poster_id <> 77198;

And both of those take quite a while (between 15-30 seconds). If I change the <> to a = it takes no time at all.

Am I making some incorrect assumptions? Maybe my DB is just foobar'd?

© Stack Overflow or respective owner

Related posts about sql

Related posts about left-join