Jump to content

optimise LEFT JOIN ?


adhok

Recommended Posts

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!

Link to comment
Share on other sites

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 ;

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.