fulltext searching and query order question

2003-06-13 Thread H M Kunzmann 

Hi all.

I have a fulltext index on a table.

If I have the following fields:

field1,field2,field3,field4

Field4 being the fulltext field.

I have the following indices: 

index1-field1,field2,field3
index2-fulltext field4

If I do a select:
select * from table where match(index2) 
against ('word1 word2' in boolean mode);

I get a very fast result.

Essentially I want to do the following:

If I do a select field1,field2,field3 from table
  where field1='something'
  and field2='something_else'
  and field3='something_more'
  and match(field4) against ('word1 word2' in boolean mode)
  order by field1,field2,field3

I can't seem to get it right that the query can return
quickly, as it does a table scan to sort the table,
which takes forever.

How do I get a fulltext search to be able to sort according
to a different field ???

Thanks for all assistance :-)

Regards,
Herbert



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: fulltext searching and query order question

2003-06-13 Thread Mike Hillyer 
Have you tried adding force index on your fulltext index?

Something similar to this:

select field1,field2,field3 from table FORCE INDEX(index2)
  where field1='something'
  and field2='something_else'
  and field3='something_more'
  and match(field4) against ('word1 word2' in boolean mode)
  order by field1,field2,field3

Regards,
Mike Hillyer
www.vbmysql.com


-Original Message-
From: H M Kunzmann [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 13, 2003 10:33 AM
To: [EMAIL PROTECTED]
Subject: fulltext searching and query order question



Hi all.

I have a fulltext index on a table.

If I have the following fields:

field1,field2,field3,field4

Field4 being the fulltext field.

I have the following indices: 

index1-field1,field2,field3
index2-fulltext field4

If I do a select:
select * from table where match(index2) 
against ('word1 word2' in boolean mode);

I get a very fast result.

Essentially I want to do the following:

If I do a select field1,field2,field3 from table
  where field1='something'
  and field2='something_else'
  and field3='something_more'
  and match(field4) against ('word1 word2' in boolean mode)
  order by field1,field2,field3

I can't seem to get it right that the query can return
quickly, as it does a table scan to sort the table,
which takes forever.

How do I get a fulltext search to be able to sort according
to a different field ???

Thanks for all assistance :-)

Regards,
Herbert



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: fulltext searching and query order question

2003-06-13 Thread H M Kunzmann 
I think I need to clarify :

The fulltext indexing  searching here works great.
The search completes in good time, but then I want it in a different
order, as described by index1 down below. This resorting step is the one
that takes forever, not the fulltext search.

So essentially, a FORCE INDEX(index1) is more appropriate, but if I do
that, then the order is fast, but the fulltext search takes forever !




On Fri, 2003-06-13 at 18:32, Mike Hillyer wrote:
 Have you tried adding force index on your fulltext index?
 
 Something similar to this:
 
 select field1,field2,field3 from table FORCE INDEX(index2)
   where field1='something'
   and field2='something_else'
   and field3='something_more'
   and match(field4) against ('word1 word2' in boolean mode)
   order by field1,field2,field3
 
 Regards,
 Mike Hillyer
 www.vbmysql.com
 
 
 -Original Message-
 From: H M Kunzmann [mailto:[EMAIL PROTECTED] 
 Sent: Friday, June 13, 2003 10:33 AM
 To: [EMAIL PROTECTED]
 Subject: fulltext searching and query order question
 
 
 
 Hi all.
 
 I have a fulltext index on a table.
 
 If I have the following fields:
 
 field1,field2,field3,field4
 
 Field4 being the fulltext field.
 
 I have the following indices: 
 
 index1-field1,field2,field3
 index2-fulltext field4
 
 If I do a select:
 select * from table where match(index2) 
   against ('word1 word2' in boolean mode);
 
 I get a very fast result.
 
 Essentially I want to do the following:
 
 If I do a select field1,field2,field3 from table
   where field1='something'
   and field2='something_else'
   and field3='something_more'
   and match(field4) against ('word1 word2' in boolean mode)
   order by field1,field2,field3
 
 I can't seem to get it right that the query can return
 quickly, as it does a table scan to sort the table,
 which takes forever.
 
 How do I get a fulltext search to be able to sort according
 to a different field ???
 
 Thanks for all assistance :-)
 
 Regards,
 Herbert
 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]