Page 1 of 1

[SOLVED] re: fulltext search on index and subsequent operati

Posted: Mon Jan 24, 2005 9:51 pm
by voltrader
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?

Posted: Mon Jan 24, 2005 9:56 pm
by feyd
order by reference_number?

Posted: Mon Jan 24, 2005 10:10 pm
by voltrader
Hi feyd -- good to hear from a familiar question-answerer again :D

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)

Posted: Mon Jan 24, 2005 10:14 pm
by feyd
what about using a JOIN, since the id marks are the same across, are they not? You can join the two together, and then order by relevance score.

Posted: Mon Jan 24, 2005 10:32 pm
by voltrader
thanks! yes, id marks are the same in both tables. i'll look it up and give it a try. The help is much appreciated.

Posted: Mon Jan 24, 2005 10:44 pm
by voltrader

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)
Since the ref_numbers are the same in both tables, should I include the supporting WHERE clause at the end as so?

Posted: Mon Jan 24, 2005 10:54 pm
by voltrader
I think I've got it...

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)";
Messy, but seems to work... thanks!

Posted: Tue Jan 25, 2005 10:24 am
by voltrader
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:

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)";

Posted: Tue Jan 25, 2005 10:28 am
by feyd
what about just adding the new fields to the existing fields used for the first full-text? (total guess)

Posted: Tue Jan 25, 2005 10:34 am
by voltrader
woops, my fault -- the keyword in the second mysql clause is different than the first...

Posted: Tue Jan 25, 2005 10:37 am
by voltrader
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)

Posted: Tue Jan 25, 2005 10:43 am
by feyd
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'))

Posted: Tue Jan 25, 2005 10:49 am
by voltrader
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))

Posted: Wed Jan 26, 2005 12:32 pm
by voltrader
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))

[solved]

Posted: Wed Jan 26, 2005 12:35 pm
by voltrader
writing and talking myself through that helped... figured it out...

I simply ORDER BY score+loc_score.

Thanks for listening :D

Consider it [solved]