CODEWITHSUNDEEP
mysqlfull-text-search
Raythe
Raythe

Reputation: 472

Full-Text Determine matched field

I have a table setup with a full-text index on ID, StreetNumber, StreetName, StreetType for a live form search. Sample data would look like:

       ID        StreetNumber   StreetName   StreetType
   -----------------------------------------------------
   | 141099  |        1411   |  Elm       |      ST    |
   -----------------------------------------------------
   | 141100  |        2901   |  Maple     |      LN    |
   -----------------------------------------------------

If I query using:

SELECT ID, StreetName, StreetNumber, StreetType
FROM Locations
WHERE MATCH(ID, StreetName, StreetNumber, StreetType)
      AGAINST('1411*' IN BOOLEAN MODE)

It will return both records of course... However, I'm having trouble finding an easy way to determine what field it matched on.

I would like to format my live search results to something like:

ID: 141100 (2901 Maple Lane)
Address: 1411 Elm St

...sorted by what they matched on. Formatting in PHP is easy, it's finding what the results matched on is the problem. However, I can't find a way to do this without having to examine it in PHP (not very efficient). Ideas?

Upvotes: 2

Views: 79

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562691

You could do the search efficiently with a fulltext index, and then use another solution to test the columns the select-list (which runs only against rows that pass the filter in the WHERE clause):

SELECT ID, StreetName, StreetNumber, StreetType,
  (StreetName like '%1411%') AS `StreetName_matches`, 
  (StreetNumber like '%1411%') AS `StreetNumber_matches`,
  (StreetType like '%1411%') AS `StreetType_matches`
FROM Locations 
WHERE MATCH(ID, StreetName, StreetNumber, StreetType) 
      AGAINST('1411*' IN BOOLEAN MODE)

Or you could create four individual fulltext indexes on the columns, and search each of them in turn:

SELECT ID, StreetName, StreetNumber, StreetType,
  MAX(`ID_Matches`) AS `ID_Matches`,
  MAX(`StreetName_Matches`) AS `StreetName_Matches`,
  MAX(`StreetNumber_Matches`) AS `StreetNumber_Matches`,
  MAX(`StreetType_Matches`) AS `StreetType_Matches`
FROM (
  SELECT SELECT ID, StreetName, StreetNumber, StreetType,
    1 AS `ID_Matches`,
    NULL AS `StreetName_Matches`,
    NULL AS `StreetNumber_Matches`,
    NULL AS `StreetType_Matches`
  FROM Locations 
  WHERE MATCH(ID) AGAINST('1411*' IN BOOLEAN MODE)
  UNION ALL
  SELECT SELECT ID, StreetName, StreetNumber, StreetType,
    NULL AS `ID_Matches`,
    1 AS `StreetName_Matches`,
    NULL AS `StreetNumber_Matches`,
    NULL AS `StreetType_Matches`,
  FROM Locations 
  WHERE MATCH(StreetName) AGAINST('1411*' IN BOOLEAN MODE)
  UNION ALL
  SELECT SELECT ID, StreetName, StreetNumber, StreetType,
    NULL AS `ID_Matches`,
    NULL AS `StreetName_Matches`,
    1 AS `StreetNumber_Matches`,
    NULL AS `StreetType_Matches`,
  FROM Locations 
  WHERE MATCH(StreetNumber) AGAINST('1411*' IN BOOLEAN MODE)
  UNION ALL
  SELECT SELECT ID, StreetName, StreetNumber, StreetType,
    NULL AS `ID_Matches`,
    NULL AS `StreetName_Matches`,
    NULL AS `StreetNumber_Matches`,
    1 AS `StreetType_Matches`,
  FROM Locations 
  WHERE MATCH(StreetTYPE) AGAINST('1411*' IN BOOLEAN MODE)
) AS t
GROUP BY t.ID;

You may also want to look into a more fully-featured full text search technology, like Sphinx Search or Apache Solr.

See for example the answer to How to return column that matched the query in Solr..?

Upvotes: 1

Related Questions