sql queries slower than expected
- by neubert
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?
