3

I'm trying to write a query to search for a record using a wild card.

I have two queries below which works but I like to know which one of the is more optimise.

Query one does gives me what i'm looking for but query two gives me different results.

Which one I should be using.

  1. Using Like in my query.

    SELECT code, name
FROM countryCounty
WHERE name LIKE  '%County Down%'
AND isActive =1
AND countryISO2FK =  'GB'
LIMIT 1

  2. Then I have boolean mode (FULLTEXT) query.

    SELECT code,name, match( name )
AGAINST ( 'County Down' IN BOOLEAN MODE ) AS relevance
FROM opjb_countryCounty 
WHERE match( name ) AGAINST ( '%County Down%' IN BOOLEAN MODE ) 
    AND isActive=1 
    AND countryISO2FK='GB' 
ORDER BY relevance DESC LIMIT 1
HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133
user1967132
  • 47
  • 1
  • 1
  • 6
  • I don't know for sure, but I'm almost sure the first one is the simplest and most efficient one. You're giving it a lot less parameters to consider. Also, if you get different results with the second one, I bet it's because you didn't use % signs the same way. – Ariane Jun 07 '13 at 22:17
  • http://forums.mysql.com/read.php?24,126294,127022#msg-127022 – zod Jun 07 '13 at 22:19
  • 1
    http://stackoverflow.com/questions/792875/which-sql-query-is-better-match-against-or-like – zod Jun 07 '13 at 22:20
  • @ariane i did add % still same result for 2nd query – user1967132 Jun 07 '13 at 22:24

2 Answers2

7

There's a significant difference between the two queries.

The first query is searching for an occurrence of the single string 'County Down' within the name column.

The second query is searching for occurrences of either of the two separate words (separate strings) 'County' and 'Down' within the text. (The purpose and effect of that '%' character before 'County' in that second query is unknown to me.)

The relevance from a BOOLEAN MODE fulltext search is going to be 1.0. If you want to return only those rows that have both the words 'County' and 'Down', then you'd really want to use the '+' qualifier before each word, for example:

MATCH(name) AGAINST('+County +Down' IN BOOLEAN MODE)

Note that this predicate will also "match" to a name containing 'Some Down and out County', for example, where the first query would not.

Also, the approach used to get the result set ordered by relevance is almost right. There's a subtle problem: including IN BOOLEAN MODE modifier causes the expression return 1.0, instead of returning the weighted float as would be returned with NATURAL LANGUAGE MODE.

To answer your question: if the first query is returning the result set you need, then use that query. The downside of that query is that the LIKE predicate in that query is not sargable, that is, MySQL can't make use of a index range scan to satisfy that predicate. (An index may be used for the other predicates, but that name column on each of those rows needs to be checked.

The advantage of a query of the second form is that it can make use of a FULLTEXT INDEX, if one is created, which can improve performance.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Thank you for your answer. I was just looking to see which one will be best. I have add fulltext to my table to index keywords but not for this table. – user1967132 Jun 08 '13 at 14:39
1

Query one does gives me what i'm looking for but query two gives me different results.

WHERE name LIKE '%County Down%'

WHERE match( name ) AGAINST ( '%County Down%' IN BOOLEAN MODE )

The first query will return results for "LACounty Down" and "NYCounty Down", but the second query will not return these results. Both queries will return results like "LA County Down" and "NY County Down," though.

To make the results match and to have it return these "fuzzy match" results, change your second query to...

WHERE match( name ) AGAINST ( '*County Down*' IN BOOLEAN MODE )
Community
  • 1
  • 1
HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133
  • Your answer is a bit wrong at the end, the * doesn't prepend, it always appends. https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html – Abdul Rehman Feb 03 '23 at 20:57