[SOLVED] re: fulltext search on index and subsequent operati
Moderator: General Moderators
[SOLVED] re: fulltext search on index and subsequent operati
Hi folks.
I'm creating a new search function for my website and have decided to go with mysql's fulltext index search. Previously I had used a less efficient method, which was fine when the db was small.
I have two tables
1. data, which is where full information is stored
2. data_index, which is an mysql index of 3 fields from table data
The idea is to search data_index using mysql fulltext search, then grabbing the returned pk reference numbers, and pulling the full information from table data.
This is the mysql statement I'm using to retrieve reference numbers from table data_index, which are ordered by relevance, as indicated by the statement 'score'.
їcode]
SELECT ref_number, MATCH (field1,field2,field3) AGAINST
('$keywords') AS score
FROM data_index WHERE MATCH (field1,field2,field3) AGAINST
('$keywords')
ї/code]
I then use those the reference numbers returned to retrieve full-information from table data.
їcode]
SELECT * FROM data WHERE ref_number in (1,2,3,4,5,6)
ї/code]
Problem I find is that full-information is not ordered in the manner that it is requested (i.e. 1,2,3,4,5,6). It seems random.
I could loop through the arrays and match reference numbers, but that seems terribly inefficient.
Any MySQL gurus able to shed light on this?
I'm creating a new search function for my website and have decided to go with mysql's fulltext index search. Previously I had used a less efficient method, which was fine when the db was small.
I have two tables
1. data, which is where full information is stored
2. data_index, which is an mysql index of 3 fields from table data
The idea is to search data_index using mysql fulltext search, then grabbing the returned pk reference numbers, and pulling the full information from table data.
This is the mysql statement I'm using to retrieve reference numbers from table data_index, which are ordered by relevance, as indicated by the statement 'score'.
їcode]
SELECT ref_number, MATCH (field1,field2,field3) AGAINST
('$keywords') AS score
FROM data_index WHERE MATCH (field1,field2,field3) AGAINST
('$keywords')
ї/code]
I then use those the reference numbers returned to retrieve full-information from table data.
їcode]
SELECT * FROM data WHERE ref_number in (1,2,3,4,5,6)
ї/code]
Problem I find is that full-information is not ordered in the manner that it is requested (i.e. 1,2,3,4,5,6). It seems random.
I could loop through the arrays and match reference numbers, but that seems terribly inefficient.
Any MySQL gurus able to shed light on this?
Last edited by voltrader on Fri Jan 28, 2005 6:12 pm, edited 1 time in total.
Hi feyd -- good to hear from a familiar question-answerer again
I probably need to be more specific. It works like this:
user searches for "sashimi" and my script looks through the index table using the above fulltext mysql statement. It picks up the reference numbers from the index table according to relevance. So, for instance, it could return reference numbers 8, 1, 3 in that order, according to score.
The full data for records 8,1,3 are in another table and I'd like to retrieve them in this order for display. However, when I do a select with the statement below, the records are retrieved as 1,3,8 for example. This is a problem because I want to be able to display the relevance score in the previously returned array in the same loop to save cycles.
I probably need to be more specific. It works like this:
user searches for "sashimi" and my script looks through the index table using the above fulltext mysql statement. It picks up the reference numbers from the index table according to relevance. So, for instance, it could return reference numbers 8, 1, 3 in that order, according to score.
The full data for records 8,1,3 are in another table and I'd like to retrieve them in this order for display. However, when I do a select with the statement below, the records are retrieved as 1,3,8 for example. This is a problem because I want to be able to display the relevance score in the previously returned array in the same loop to save cycles.
Code: Select all
SELECT * FROM data WHERE ref_number IN (8,1,3)Code: Select all
SELECT ref_number, data.name, data.type, data.cat_1, MATCH (name,type,cat_1) AGAINST
('$keywords') AS score
FROM search_index WHERE MATCH (name,type,cat_1) AGAINST
('$keywords') AND (ref_number=data.ref_number)I think I've got it...
Messy, but seems to work... thanks!
Code: Select all
$mysql = "SELECT data.ref_number, data.name, data.type, data.address_1, data.city, MATCH (data_index.name,data_index.type,data_index.cat_1) AGAINST
('$keywords') AS score
FROM data_index, data WHERE MATCH (data_index.name,data_index.type,data_index.cat_1) AGAINST
('$keywords')
AND (data_index.ref_number=data.ref_number)";The above works fine for searching for the keyword within 3 data_index fields. If I have another keyword_1 that I would like to search within a couple of other data_index fields, how would I create the JOIN so that only unique results are returned?
In other words, I'd like to merge the two statements below to return one set of results:
In other words, I'd like to merge the two statements below to return one set of results:
Code: Select all
$mysql = "SELECT data.ref_number, data.name, data.type, data.address_1, data.city, MATCH (data_index.name,data_index.type,data_index.cat_1) AGAINST
('$keywords') AS score
FROM data_index, data WHERE MATCH (data_index.name,data_index.type,data_index.cat_1) AGAINST
('$keywords')
AND (data_index.ref_number=data.ref_number)";
$mysql = "SELECT data.ref_number, data.A, data.B, MATCH (data_index.A, data_index.B) AGAINST
('$keywords') AS score
FROM data_index, data WHERE MATCH (data_index.A,data_index.B) AGAINST
('$keywords')
AND (data_index.ref_number=data.ref_number)";This did not work
, but I think I'm close...
Code: Select all
$mysql = "SELECT data.ref_number, data.name, data.type, data.address_1, data.city,
MATCH (data_index.name,data_index.type,data_index.cat_1) AGAINST
('$keywords') AS score,
MATCH (data_index.A, data_index.B) AGAINST ('$xtra_keyword') AS score,
FROM data_index, data WHERE MATCH (data_index.name,data_index.type,data_index.cat_1) AGAINST
('$keywords')
AND MATCH (data_index.A, data_index.B) AGAINST ('$xtra_keyword)
AND (data_index.ref_number=data.ref_number)- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
how about...
Code: Select all
SELECT
data.ref_number, data.name, data.type, data.address_1, data.city,
MATCH (data_index.name,data_index.type,data_index.cat_1) AGAINST (('$keywords') AS score,
MATCH (data_index.A, data_index.B) AGAINST ('$xtra_keyword') AS score2,
FROM
data_index, data
WHERE
(data_index.ref_number=data.ref_number)
AND
(MATCH (data_index.name,data_index.type,data_index.cat_1) AGAINST ('$keywords')
OR
MATCH (data_index.A, data_index.B) AGAINST ('$xtra_keyword'))Thanks again Feyd... I'll try that, but I did find something that does work:
Code: Select all
(SELECT data.ref_number, data.name, data.type, data.address_1, data.city, MATCH (data_index.name,data_index.type,data_index.cat_1) AGAINST
('$keywords') AS score
FROM data_index, data WHERE MATCH (data_index.name,data_index.type,data_index.cat_1) AGAINST
('$keywords')
AND (data_index.ref_number=data.ref_number))
UNION
(SELECT data.ref_number, data.A, data.B, MATCH (data_index.A, data_index.B) AGAINST
('$keywords') AS score
FROM data_index, data WHERE MATCH (data_index.A,data_index.B) AGAINST
('$keywords')
AND (data_index.ref_number=data.ref_number))I think I know what to do... I need to retrieve scores from both matches in each SELECT, then add them to produce a total score within the SELECT, which I then sort by outside the overall clause. I get a syntax error, however... can anyone tell me where I'm going wrong here?
Code: Select all
SELECT *,
MATCH (data_index.A,data_index.B,data_index.C) AGAINST
('$keywords') AS score,
MATCH (data_index.D,data_index.E,data_index.F) AGAINST
('$xtra_keywords') AS score_2,
tot_score=score+score_2
FROM data_index, data WHERE MATCH (data_index.A,data_index.B,data_index.C) AGAINST
('$keywords')
AND (data_index.ref_number=data.ref_number))
UNION
SELECT *,
MATCH (data_index.A,data_index.B,data_index.C) AGAINST
('$keywords') AS score,
MATCH (data_index.D,data_index.E,data_index.F) AGAINST
('$xtra_keywords') AS score_2,
tot_score=score+score_2
FROM data_index, data WHERE MATCH (data_index.D,data_index.E,data_index.F) AGAINST
('$xtra_keywords')
AND (data_index.ref_number=data.ref_number))