Abdul Razzak Fallaha Abdul Razzak Fallaha - 1 year ago 43
MySQL Question

MySQL MATCH AGAINST doesn't return all fitting results

I have a table of, to simplify, photo ids and tag ids. And I noticed recently that when I use the MATCH AGAINST command it doesn't return all the suitable matches. Even though sometimes two rows contain the exact same text that's been tested against, but still one gets selected and the other doesn't.

Here's the table called photos

id | type | size | tag |
+----------------+------+---------+----------------------------------------------------------------------+
| IMG_1485197299 | jpg | 1400000 | ImgTg_0000000000 |
| IMG_1485429476 | jpg | 0 | ImgTg_1485195510 ImgTg_1485195592 ImgTg_1485195621 ImgTg_1486234725 |
| IMG_1485429627 | jpg | 0 | ImgTg_1485195510 ImgTg_1485195621 ImgTg_1485195592 |
| IMG_1485429660 | jpg | 0 | ImgTg_1485195510 ImgTg_1485195621 ImgTg_1485195592 |
| IMG_1485429669 | jpg | 0 | ImgTg_1485195510 ImgTg_1485195621 ImgTg_1485195592 |
| IMG_1485429681 | jpg | 0 | ImgTg_1485195592 |
| IMG_1485429687 | jpg | 0 | ImgTg_1486234725 |
| IMG_1485429695 | jpg | 0 | ImgTg_1485195621 ImgTg_1485195510 |
| IMG_1485429704 | jpg | 0 | ImgTg_0000000000 ImgTg_1486234725 |
| IMG_1485429710 | jpg | 0 | ImgTg_1485195510 |
| IMG_1485429718 | jpg | 0 | ImgTg_1485195510 |
| IMG_1485433986 | jpg | 0 | ImgTg_1485195510 ImgTg_1485195592 |
| IMG_1485433993 | jpg | 0 | ImgTg_1485195510 ImgTg_1485195592 |
| IMG_1486234762 | png | 143306 | ImgTg_1486234725 |


This is the command I'm using:

SELECT * FROM photos WHERE MATCH(tag) AGAINST('+ImgTg_1486234725 ' IN BOOLEAN MODE);


and its working fine with most of the table but for some reason it just doesn't work especially on the last row. Returning:

+----------------+------+------+-------------------+
| id | type | size | tag |
+----------------+------+------+-------------------+
| IMG_1485429687 | jpg | 0 | ImgTg_1486234725 |
+----------------+------+------+-------------------+


NOTE: If I try and change the
tag
to a different
id
it works! But some
ids
just don't work

What should I do?

Answer Source

Thank you all for trying to help I've been trying to solve this riddle since yesterday, but I just found the mistake I made after I posted the question :D

I was using some kind of latincollation and I changed it to utf8_bin and now it works perfectly!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download