0

I am querying for a unicode word in join. When I use LIKE in the where condition, it gives more number of results as compared to using MATCH in BOOLEAN MODE. But when I am trying to find the differences between the queries, it is giving null results. This happens only when the search word is unicode.

This is the query for finding differences having the queries -

select * FROM (select tw.*,us.description, us.location from toys tw join 
users us on tw.user_id=us.user_id WHERE toys_text LIKE '%गोपाला%') AS table1 
WHERE NOT EXISTS (
select tw.*,us.description, us.location from toys tw join users us on 
tw.user_id=us.user_id WHERE MATCH (toys_text) AGAINST ('गोपाला*' IN BOOLEAN 
MODE)
)

Edit - Example - गोपाला* does not match with राम_गोपाला or गोपाले but like search - %गोपाला% finds both of them

So, why is there difference between results of the like and match queries when search word is unicode ? And why the sql query for finding the difference between the 2 queries gives null results ?

user20152015
  • 344
  • 2
  • 23
  • the `LIKE` version has a leading `%` while `MATCH` version has only one `*`. I suspect the leading `*` is not allowed for `MATCH` but if you are comparing both versions you need to remove the leading `%` from the `LIKE` query. – andrews Jan 28 '18 at 19:09
  • @andrews Thanks for your response. On removing the leading `%` from the `LIKE` version of the query, it gives very much less results - one thousandth of that with the leading `%` present. – user20152015 Jan 28 '18 at 19:17
  • yes, of course, but now `LIKE` and `MATCH` queries are the same. Isn't it what you were asking about, i.e. why there are different results? – andrews Jan 28 '18 at 19:23
  • @andrews Removing the leading `%` in `LIKE` query still gives different results. My question is regarding why they give different results. Leading `%` does not make any difference if search word is not unicode. – user20152015 Jan 28 '18 at 19:25
  • you are comparing the results of 2 queries in the wrong way. `EXISTS` whill always be true if your inner query returns at least one row. Therefore, `NOT EXISTS` is always false for you. To run the proper comparison you need to `JOIN` 2 query results using primary keys. – andrews Jan 28 '18 at 19:28
  • @andrews I want the difference between the 2 queries. `Inner join` just returns the common rows which in this case gives the MATCH query results as it has lesser results. While LEFT JOIN and RIGHT JOIN also do not serve the purpose. – user20152015 Jan 28 '18 at 19:57
  • to get only those records from 1st `LIKE` query which are not returned by the `MATCH` query, do `LEFT JOIN` to the result of the second query and put a condition WHERE MATCH-query PK IS NULL. – andrews Jan 28 '18 at 20:10
  • @andrews That left join with where condition query works. But still, question remains for unicode words, how do we search with match to get high performance and at same time, get results similar to LIKE . In case of unicode, proposed solutions like having a column like reversed letters do not work since structure of unicode word is different. – user20152015 Jan 29 '18 at 03:24
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/164067/discussion-between-andrews-and-user20152015). – andrews Jan 29 '18 at 07:58
  • Please give a complete test case that demonstrates the problem -- include `CREATE TABLE` and `INSERTs` to set up the demo. – Rick James Jan 29 '18 at 22:09
  • @RickJames Example - `गोपाला*` does not `match` with `राम_गोपाला` or `गोपाले` but `like` search - `%गोपाला%` works for both of them – user20152015 Jan 31 '18 at 06:26
  • @user20152015 - I don't know whether `FULLTEXT` works with non-English text. – Rick James Feb 01 '18 at 04:56
  • @RickJames Fulltext in boolean mode works with unicode only partially – user20152015 Feb 01 '18 at 05:00

0 Answers0