0

Well i'm running 2 queries that should show me the same result,

First query:

SELECT count( id ) AS cv FROM table_name WHERE field_name LIKE '%êêê01, word02, word03%'

Second query:

SELECT count( id ) AS cv FROM table_name WHERE match(field_name) against('êêê01, word02, word03')

but the first show more rows than the second, someone could tell me why?

I'm using fulltext index on this field,

Thanks.

user3466310
  • 7
  • 4

1 Answers1

0

I did a quick research and the following quote should answer your question:

One problem with MATCH on MySQL is that it seems to only match against whole words so a search for 'bla' won't match a column with a value of 'blah'.

It's also described in the documentation for match

By default, the MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to AGAINST(). For each row in the table, MATCH() returns a relevance value; that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.

Meanwhile like is more "powerful" as it can look upon individuals characters:

Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:

Which explains why like returns more results than match.

Community
  • 1
  • 1
Jonast92
  • 4,964
  • 1
  • 18
  • 32
  • is there a way to fix it? – user3466310 Apr 18 '14 at 02:17
  • @user3466310 No. If you want your query to be able to find any strings that are somewhat related to your index string then you must stick with `like`. `match` is simply a completely different operation which allows you to match against a finite set of strings. It's hard to say what you mean by "fixing" since they are both doing it's job, their jobs are simply not the same. – Jonast92 Apr 18 '14 at 02:19
  • hum understand, well i'm using match cuz it rly more faster than like cuz i got a table with 28 millions of rows, do u know some solution to search in a table like it? – user3466310 Apr 18 '14 at 02:26
  • @user3466310 If you really need to be able to search by individual characters then no, I'm afraid not (at least it's not in my field of knowldge). You could, however, possibly limit a request that would query, let's say, once a day/hour and store the results in a look-up table which you can then check into, it varies on what your requirements are (how accurate it has to be and how fresh the data has to be). But please mark this as the accepted answer if it answered your original question and feel free to post a new question where your new question can be answered. – Jonast92 Apr 18 '14 at 02:59
  • @user3466310 I'd like to address that the problem with database speed is usually database optimization (using a properly atomic database and indexes) rather than the functions themselves that are being used. – Jonast92 Apr 18 '14 at 03:05