Hello,
I am interested in utilizing full text search in a way that I have not
seen in my run across the various examples and docs.
What I'd like to do is create full text indexes on individual columns in
order to attain relevancy of the individual search criterion per column.
I'd then like to aggregate the individual scores into a cumulative
relevance score and finally return this score as the final score along
with the rows that match my "fixed" criterion.
My table follows:
CREATE TABLE products
(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
part_type varchar(10),
ext_color varchar(10),
int_color varchar(10),
lang varchar(20),
);
ALTER TABLE ... ADD FULLTEXT (part_type);
ALTER TABLE ... ADD FULLTEXT (exterior_color);
ALTER TABLE ... ADD FULLTEXT (interior_color);
ALTER TABLE ... ADD FULLTEXT (language);
Some inserts:
INSERT ... (part_type, ext_color, int_color, lang) VALUES
('case','red','blue','english');
INSERT ... (part_type, ext_color, int_color, lang) VALUES
('frame','red','red','spanish');
And so on as to create decent test base of ~500 - 1k records of varying
data...
My first cut at a select statement follows:
select *,
(IF(MATCH (int_color) AGAINST ('>red IN BOOLEAN MODE) >
1,1,MATCH (int_color) AGAINST ('>green' IN BOOLEAN MODE)) +
IF(MATCH (ext_color) AGAINST ('>blue' IN BOOLEAN MODE) >
1,1,MATCH (ext_color) AGAINST ('>grey' IN BOOLEAN MODE)) +
IF(MATCH (lang) AGAINST ('>spanish' IN BOOLEAN MODE) > 1,1,MATCH
(ethnicity) AGAINST ('>spanish' IN BOOLEAN MODE))
)/3 as score
FROM products WHERE
part_type= 'case';
What I am wondering is if this is entirely wrongheaded? I get results
that seem to make sense, but my I am sure that my calculations are off
and I could have flawed (and extremely simplistic test records).
Also is full text search is best suited to scanning for occurrences
across multiple columns where fulltext index would be something like
FULLTEXT(part_type,ext_color,int_color,lang). This suggests that my
search would scan for 1 criterion in all of those columns - not what I
am after.
The typical example seems to be searching for occurrences of
names/titles across columns like authors, titles, snippets, etc... But
I haven't run into anything that addresses capturing relevance given my
needs. If there is another strategy on how to achieve this, I'd be
grateful! Or if this strategy needs tweaking, I'd also be thankful!
I am using MySQL 4.0.13 under RH/Linux 7.4 and Windows 2000.
Thanks,
Alejandro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
