Jump to content


Photo

optimise LEFT JOIN ?


  • Please log in to reply
2 replies to this topic

#1 adhok

adhok
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 10 October 2003 - 12:55 AM

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!
adhok, dork
[br]http://antimedia.net/data

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 12 October 2003 - 07:00 PM

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 ;
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 adhok

adhok
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 16 October 2003 - 05:49 AM

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!
adhok, dork
[br]http://antimedia.net/data




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users