MySQL full text search result problem

full-text-searchMySQL

I have this query that does not return any results:

SELECT
    MATCH (e.firstname, e.lastname, e.tc_identity) AGAINST ('Baha') AS Score
    , e.*
FROM
    employees AS e
WHERE
    MATCH (e.firstname, e.lastname, e.tc_identity) AGAINST ('Baha' IN BOOLEAN MODE)
ORDER BY `Score` DESC;

firstname, lastname and tc_identity are of type varchar.

I have data in firstname like 'Bahadir', 'Bahadır', etc…

When I try to match against 'Baha', I get no results.

When I try to match against 'Bahadir' or 'Bahadır', I get no results.

What's the problem?

Best Answer

Your query is looking for the token = 'Baha'. You need a wildcard search for 'Baha*'.

Please try the following

SELECT
MATCH (`e`.`firstname`, `e`.`lastname`, `e`.`tc_identity`) AGAINST ('Baha*') AS `Score`,
`e`.*

FROM
`employees` AS `e`

WHERE
MATCH (`e`.`firstname`, `e`.`lastname`, `e`.`tc_identity`) AGAINST ('Baha*' IN BOOLEAN MODE)

ORDER BY `Score` DESC

Related Solutions

Mysql – Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE

Perhaps you could record the following from the three(3) tables

table name
the column from the table name
FULLTEXT index on the column

Here is the code:

DROP TABLE IF EXISTS combined_data;
CREATE TABLE combined_data
(
    source_table VARCHAR(64),
    source_id INT NOT NULL,
    data TEXT NOT NULL,
    FULLTEXT (data)
) ENGINE=MyISAM;
--
ALTER TABLE combined_data DISABLE KEYS;
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table1',id,`result` FROM table1 WHERE MATCH(`result`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table2',id,`content` FROM table1 WHERE MATCH(`content`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table3',id,`text` FROM table1 WHERE MATCH(`text`) AGAINST('keyword');
--
ALTER TABLE combined_data ENABLE KEYS;

Now you can run a single query against the one table

SELECT *,MATCH(`data`) AGAINST('keyword') as `good_score`
FROM combined_data
ORDER BY `good_score` DESC;

Give it a Try !!!

Sql-server – Full Text Search Term Casing Affects Results

This is documented although I couldn't find a reference in Books Online:

The rules for characters followed by nonalphanumeric characters are somewhat convoluted (at least in English). The English word breaker accepts the token C# and returns C#. The lowercase token c#, however, is indexed as c with the # character stripped off. The uppercase token C++ and lowercase token c++, on the other hand, are both indexed as c++.

"Pro Full-Text Search in SQL Server 2008" - Michael Coles, Hilary Cotter, p106

You can check the behaviour of your search phrase using the sys.dm_fts_parser DMV, eg

SELECT * FROM sys.dm_fts_parser ( 'microsoft AND c#', 1033, 0, 0 )
SELECT * FROM sys.dm_fts_parser ( 'microsoft AND C#', 1033, 0, 0 )

As a workaround, you could force all strings through as upper-case, use some kind of mapping table for common search terms, or design a strategy to ensure non-alphanumeric characters are never stored or queried as part of the full-text index.

Note the behaviour is more consistent in SQL 2012 (where both lower and upper case will return only 'Microsoft C#').

Best Answer

Related Solutions

Mysql – Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE

Sql-server – Full Text Search Term Casing Affects Results

Related Question