Re: Problem With FulltText Index and VarChar
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 [EMAIL PROTECTED] 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
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ 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/[EMAIL PROTECTED]
.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
/ /|_/ / // /\ \/ /_/
Re: Problem With FulltText Index and VarChar
On 12/30/05, Gleb Paharenko [EMAIL PROTECTED] wrote:
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...
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 |
++---++
Thanks so much for your help. Everything makes sense. One this I did
come across though is that if you are using a multi-column index you
have to include all the columns in the index in your select statement.
e.g. MATCH (Comment, heading, ...)
Thanks,
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem With FulltText Index and VarChar
On 12/28/05, Gleb Paharenko [EMAIL PROTECTED] 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`,`Country`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=101131 ;
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 MySQL 4.=
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=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ 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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem With FulltText Index and VarChar
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. Michael Stearne wrote: I am trying to do a fulltext search with a multi-field index using MySQL 4.= 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=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ 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/[EMAIL PROTECTED]
Problem With FulltText Index and VarChar
I am trying to do a fulltext search with a multi-field index using MySQL 4.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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
