optimise LEFT JOIN ?

[adhok]

I\'m modifying some code for a community news website, so that the main news wire shows every story posted - except for one category of stories. Not every story is categorised. So I have to SELECT all categorised stories which aren\'t from $bad_category_id, and I have also SELECT all un-categorised stories.

 

I\'ve found a way to do it but it\'s very slow. The news is in a table called webcast, the categories are linked to by a link table called catlink. catlink.id is the link to webcast.id, and catlink.catid is a link to another table category.id. I only found out about JOINS last night ... so maybe there\'s a better way.

 

This is the (simplified) SQL:

 

SELECT w.id FROM webcast w LEFT JOIN catlink l ON w.id = l.id  

WHERE (l.id IS NOT NULL AND l.catid != $bad_category_id) OR  l.id IS NULL ORDER BY w.id DESC LIMIT 10;

 

It\'s too slow. The opposite query (select only the bad category) is easy and fast:

 

SELECT w.id FROM webcast w, catlink l

WHERE l.id=w.id and l.catid=$bad_category_id

ORDER BY w.id DESC limit 10;

 

I tried this:

 

SELECT w.id FROM webcast w, catlink l

WHERE !(l.id=w.id and l.catid=$bad_category_id)

ORDER BY w.id DESC limit 10;

 

But that caused a disk space error! Oops. I think it\'s wrong anyway...

 

Yo thanks!

[Barand]

So I have to SELECT all categorised stories which aren\'t from $bad_category_id, and I have also SELECT all un-categorised stories

 

So you want all stories except those in bad_category?

 

SELECT w.id FROM webcast w LEFT JOIN catlink l

ON l.id=w.id WHERE l.catid <> $bad_category_id

ORDER BY w.id DESC ;

[adhok]

thanks barand. I didn\'t think of your way. but your way also takes a long time (25 sec on my server). In the end I found another way to do this, by adding a new field to the db for the bad category. it\'s a bit redundant, but a lot faster!