Hello.
> I guess my question is, how do I make sure the full text search is
> being done against the CommentsIDX and not just against the individual
> fields...
If I've got your question right, you shouldn't worry about
it. As far as I know, the search is done against the union of all
columns in the FULLTEXT index, not against individual columns. Even
more, when performing a language natural search (as you do), the row
will be in the result set if at least one field has one word from the
list, however it will have a small relevance. Examples below should
prove my opinion:
mysql> select * from ab;
+----------------+---------------------+
| a | b |
+----------------+---------------------+
| sf sfs | sf s sdfss |
| sss sss | 3435 |
| search | words |
| sdf sdf 34345 | 35 35435 34535 iwur |
| cool search | NULL |
+----------------+---------------------+
mysql> select a, b, match(a,b) against('search words') from ab where
match(a,b) against('search words');
+-------------+-------+------------------------------------+
| a | b | match(a,b) against('search words') |
+-------------+-------+------------------------------------+
| search | words | 1.7514755725861 |
| cool search | NULL | 0.39634910225868 |
+-------------+-------+------------------------------------+
This shows that the search is done against concatenation of the fields:
mysql> select a, b, match(a,b) against('search words') from ab where
match(a,b) against('+search +words' in boolean mode);
+--------+-------+------------------------------------+
| a | b | match(a,b) against('search words') |
+--------+-------+------------------------------------+
| search | words | 1.7514755725861 |
+--------+-------+------------------------------------+
Michael Stearne wrote:
> On 12/28/05, Gleb Paharenko <gleb.paharenko@stripped> wrote:
>
>>Hello.
>>
>>
>>>#1054 - Unknown column 'CommentsIDX' in 'where clause'
>>>My table structure contains: FULLTEXT KEY `CommentsIDX` >(`Comments`=
>
> )
>
>>You should use column names not index names in your queries. Please,
>>provide CREATE statement for your tables and problematic queries. With
>>this information it'll be easier to help you.
>>
>
>
> Thanks this is the a snippet of the table structure:
> CREATE TABLE `properties` (
> `id` int(11) unsigned NOT NULL auto_increment,
> `UserID` int(11) unsigned NOT NULL default '0',
> `Type` enum('Commercial','Residential') NOT NULL default 'Residential',
> `Subtype` varchar(64) NOT NULL default '0',
> `Zip` varchar(10) NOT NULL default '',
> `Heading` varchar(84) NOT NULL default '',
> `Address1` varchar(32) NOT NULL default '',
> `Address2` varchar(32) default NULL,
> .....
> KEY `TypeSubType` (`Type`,`Subtype`),
> KEY `CityHood` (`City`,`Neighborhood`),
> FULLTEXT KEY `CommentsIDX`
> (`Comments`,`Subtype`,`Heading`,`Zip`,`Address1`,`Neighborhood`,`City`,`Cou=
> ntry`)
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 AUTO_INCREMENT=3D101131 ;
>
> I guess my question is, how do I make sure the full text search is
> being done against the CommentsIDX and not just against the individual
> fields...
>
> SELECT * FROM properties WHERE MATCH
> (Comments,Subtype,Heading,Zip,Address1,Neighborhood,City,Country)
> AGAINST (
> "search words" WITH QUERY EXPANSION)
>
> Thanks,
> Michael
>
>
>>
>>Michael Stearne wrote:
>>
>>>I am trying to do a fulltext search with a multi-field index using MySQ=
>
> L 4.=3D
>
>>>1.15.
>>>
>>>When I create a full text index of my Comments field which is of type
>>>TEXT. I can do a fulltext search fine.
>>>
>>>But when I add another field (like a varchar or even Text) to that
>>>index or change the name of the index I get and error like:
>>>
>>>SELECT * FROM properties WHERE MATCH (CommentsIDX)
>>>AGAINST (
>>>"item1 OR item2 AND (item3 AND item4 NOT (item5 OR item6))"
>>>WITH QUERY EXPANSION
>>>)
>>>
>>>MySQL said: Documentation
>>>#1054 - Unknown column 'CommentsIDX' in 'where clause'
>>>
>>>My table structure contains: FULLTEXT KEY `CommentsIDX` (`Comments`)
>>>
>>>Any ideas?
>>>
>>>Thanks
>>>
>>
>>
>>--
>>For technical support contracts, goto https://order.mysql.com/?ref=3Densi=
>
> ta
>
>>This email is sponsored by Ensita.NET http://www.ensita.net/
>> __ ___ ___ ____ __
>> / |/ /_ __/ __/ __ \/ / Gleb Paharenko
>> / /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
>>/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
>> <___/ www.mysql.com
>>
>>
>>
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
> .com
>
>>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
