28 messages in com.mysql.lists.mysqlRe: Strange queries
FromSent OnAttachments
jo...@chaffee.com09 Apr 2001 12:34 
Andrey Kotrekhov09 Apr 2001 12:54 
Andrew Schmidt09 Apr 2001 13:02 
Brad Barnett09 Apr 2001 13:43 
Eric Fitzgerald09 Apr 2001 14:23 
Lars Andersson09 Apr 2001 14:26 
Tim Bunce09 Apr 2001 14:30 
Quentin Bennett09 Apr 2001 14:43 
bbar...@L8R.net09 Apr 2001 15:35 
Andy Sharp09 Apr 2001 15:37 
Johan Andersson10 Apr 2001 01:20 
Sinisa Milivojevic10 Apr 2001 04:25 
Heikki Tuuri10 Apr 2001 05:48 
Jeremy Zawodny10 Apr 2001 07:35 
Tim Bunce11 Apr 2001 02:31 
Sinisa Milivojevic11 Apr 2001 04:21 
Ken Menzel11 Apr 2001 06:49 
Sinisa Milivojevic11 Apr 2001 06:58 
Andrey Kotrekhov11 Apr 2001 07:34 
Lars Andersson11 Apr 2001 07:48 
Ken Menzel11 Apr 2001 09:33.txt
Jeremy Zawodny11 Apr 2001 10:05 
Tim Bunce11 Apr 2001 16:24 
Tim Bunce11 Apr 2001 16:26 
Dan Nelson11 Apr 2001 20:22 
Sinisa Milivojevic12 Apr 2001 04:37 
Ken Menzel12 Apr 2001 05:54 
Mark Worsdall12 Apr 2001 09:07 
Subject:Re: Strange queries
From:bbar...@L8R.net (bbar@L8R.net)
Date:04/09/2001 03:35:59 PM
List:com.mysql.lists.mysql

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"