3

I have the following query :

SELECT * FROM `user` 
WHERE MATCH (user_login) AGAINST ('supriya*' IN BOOLEAN MODE)

Which outputs all the records starting with 'supriya'.
Now I want something that will find all the records ending with e.g. 'abc'.
I know that * cannot be preappended and it doesn't work either and I have searched a lot but couldn't find anything regarding this.

If I give query the string priya ..it should return all records ending with priya.
How do I do this?

Johan
  • 74,508
  • 24
  • 191
  • 319
user1006923
  • 33
  • 1
  • 3

2 Answers2

12

Match doesn't work with starting wildcards, so matching with *abc* won't work. You will have to use LIKE to achieve this:

SELECT * FROM user WHERE user_login LIKE '%abc';

This will be very slow however.

If you really need to match for the ending of the string, and you have to do this often while the performance is killing you, a solution would be to create a separate column in which you reverse the strings, so you got:

user_login user_login_rev
xyzabc     cbazyx

Then, instead of looking for '%abc', you can look for 'cba%' which is much faster if the column is indexed. And you can again use MATCH if you like to search for 'cba*'. You will just have to reverse the search string as well.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • 2
    Thanks!! That sounds like a good work around but I have to implement this in an already existing database which is quite large and reversing all the existing data would be quite tedious. Bit still something to think about :) – user1006923 Oct 24 '11 at 06:52
  • 1
    If you add the column, update all rows one time, and then write a trigger to keep the reversed column synchronised, you should be done. It will take some extra space, but apart from that, it should be pretty easy to implement. – GolezTrol Oct 24 '11 at 07:18
  • Very nice solution!.. but how faster is it compared to `like %abc%` – alexanderg Mar 06 '13 at 23:38
  • Like I said in my answer, the starting wildcard makes it slower, because indexes are no longer used. The actual difference depends greatly on the number of records, length of the values, the quality of the indexes and probably probably a lot of other factors. – GolezTrol Mar 07 '13 at 08:32
  • 2
    Beware that inverting strings will not help you if you want to find stuff from the middle of the string. You won't find "Jimmy Blue Jones" by typing 'Blue%' using normal indices nor by inverting 'Blue%' using inverted inices. – PeerBr Mar 31 '15 at 15:14
0

I believe the selection of FULL-TEXT Searching isn't relevant here. If you are interested in searching some fields based on wildcards like:

  • %word% ( word anywhere in the string)
  • word% ( starting with word)
  • %word ( ending with word)

best option is to use LIKE clause as GolezTrol has mentioned.

However, if you are interested in advanced/text based searching, FULL-TEXT search is the option.

Limitations with LIKE:

There are some limitations with this clause. Let suppose you use something like '%good' (anything ending with good). It may return irrelevant results like goods, goody.

So make sure you understand what you are doing and what is required.

Ghazanfar Mir
  • 3,493
  • 2
  • 26
  • 42
  • 2
    The big limitation with '%word' is that you can't use indexes. That might kill your performance. – PeerBr Mar 31 '15 at 15:21