On 09-Apr-2001 Eric Fitzgerald wrote:
> Since MATCH is evaluated AFTER the where, you need to use HAVING
> SELECT
> url,newwebsites.description,newwebsites.title,newwebsites.catid,category.ful
> lname,MATCH
> newwebsites.description AGAINST ('aliens') as GOO from newwebsites,category
> LEFT
> JOIN userrestrictions ON userrestrictions.name REGEXP
> '[[:<:]]username|GLOBALAUTHADMIN[[:>:]]' AND
> newwebsites.catid=userrestrictions.catid where
> category.catid=newwebsites.catid
> AND userrestrictions.catid IS NULL AND category.groupid<='1' ORDER BY
> newwebsites.groupid DESC limit 10 HAVING GOO > 0;
>
Cool.. thanks. This works, if I put the limit and such after the having, but
there's a problem. If I used (as I did before attempting this) :
SELECT
url,newwebsites.description,newwebsites.title,newwebsites.catid,category.fullnam
e from newwebsites,category LEFT JOIN userrestrictions ON userrestrictions.name
REGEXP '[[:<:]]mfeteam99|GLOBALAUTHADMIN[[:>:]]' AND
newwebsites.catid=userrestrictions.catid where category.catid=newwebsites.catid
AND userrestrictions.catid IS NULL AND category.groupid<='3' AND MATCH
newwebsites.description AGAINST ('dogs') ORDER BY newwebsites.groupid DESC
limit 10;
10 rows in set (2.11 sec)
I get relatively fast responses... probably because the records searched are
limited by restrictions that occur before the MATCH. The same query written as:
SELECT
url,newwebsites.description,newwebsites.title,newwebsites.catid,category.fullnam
e, MATCH newwebsites.description AGAINST ('big dogs')from newwebsites,category
LEFT JOIN userrestrictions ON userrestrictions.name REGEXP
'[[:<:]]mfeteam99|GLOBALAUTHADMIN[[:>:]]' AND
newwebsites.catid=userrestrictions.catid where category.catid=newwebsites.catid
AND userrestrictions.catid IS NULL AND category.groupid<='3' ORDER BY
newwebsites.groupid DESC limit 10;
It takes well over a minute to respond. I'm guessing this is because its
finding all the matches, and then sorting them due to relevance. Any way to
speed this up? Any way to sort by relevance yet keep the query speed down?
---
Dynamic Hosting
HTTP://www.L8R.net/
"We Provide Static Hostnames for Dynamic IP's"
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
