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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

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

Post 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?
Last edited by voltrader on Fri Jan 28, 2005 6:12 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

order by reference_number?
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post 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)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post 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.
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post 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?
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post 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!
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post 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)";
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

what about just adding the new fields to the existing fields used for the first full-text? (total guess)
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post by voltrader »

woops, my fault -- the keyword in the second mysql clause is different than the first...
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post 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)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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'))
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post 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))
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

Post 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))
User avatar
voltrader
Forum Contributor
Posts: 223
Joined: Wed Jul 07, 2004 12:44 pm
Location: SF Bay Area

[solved]

Post 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]
Post Reply