I can't believe there are so many members of the IHOC and nobody has mentioned hints yet.
I hate their hints. If you want any kind of performance you have to use hints. Booo! Hiss!
I can't believe there are so many members of the IHOC and nobody has mentioned hints yet.
I hate their hints. If you want any kind of performance you have to use hints. Booo! Hiss!
<FONT size=2>The most commonly used idiom for the EXISTS clause is ... EXISTS (SELECT * FROM ...). The database isn't going to retrieve ANY rows for that subquery when it sees an EXISTS clause, so SELECT * is as good as anything else.</FONT>
Good point. Made me want to dig up my Joe Celko book, but apparently an officemate has made off with it. Curse you, officemate! Curse youuu!!!
I think I can guess which part of the query is taking up the time, so I thought I'd take a crack at it:
<FONT face="Courier New" size=2></FONT>
<FONT face="Courier New" size=2>[code language="sql"]</FONT>
<FONT face="Courier New" size=2>SELECT a.iuserid AS iuserid, b.vchfirstname AS first, b.vchlastname AS last, b.vchnickname as nickname
FROM ftdb_forumusers AS a
LEFT JOIN ftdb_users AS b ON (a.iuserid = b.iuserid)
WHERE a.iforumid = #form.forum#
AND NOT EXISTS (SELECT 1 FROM ftdb_groupusers WHERE iuserid=a.iuserid AND igroupid = #form.group#)
ORDER BY b.vchlastname, b.vchfirstname
[/code]