0

I'm running a query that looks something like this:

SELECT * FROM `table` WHERE `field` LIKE '%567%' LIMIT 6;

As is apparent, this isn't quite the most efficient way to do this since the method isn't sargable and doesn't honor the indexing on the searched field. The inefficiency is an even bigger concern in my context since I anticipate the said table to grow indefinitely (read millions of records). I tried the below as advised by a few websites but it refuses to perform the search as expected:

SELECT * FROM `table` WHERE CONTAINS(`field`, '567') LIMIT 6;

This option only returns when it hits an "exact" match which isn't what I'm looking for.

The following seems to have the same effect:

SELECT * FROM `table` WHERE MATCH (`field`) AGAINST ('567' IN BOOLEAN MODE);

Is there ANY way to make the partial search sargable in MySQL?

TheLearner
  • 2,813
  • 5
  • 46
  • 94
  • 1
    You have very few options for searching for an arbitrary substring within a longer string field efficiently, whether in a relational database or otherwise. By "very few", I basically mean none, although depending on the exact nature of the problem, there are some tricks you can use. I should note that the expression `LIKE '567%'` *is* sargable. The problem is the wildcard at the beginning of the pattern. – Gordon Linoff Jun 06 '15 at 14:20
  • Consider http://stackoverflow.com/questions/3320698/what-is-the-best-way-to-implement-a-substring-search-in-sql – AsConfused Jun 06 '15 at 14:29
  • If your search terms are "known" and "limited", you can do a pre-match and maintain the result in another table for later searching. – Tim3880 Jun 06 '15 at 14:31
  • How might that play out with 1000 users pre-knowing they want a hotel with wifi in various cities – AsConfused Jun 06 '15 at 14:35
  • @Amit . . . You need a better data structure than storing lists of features in strings. You need a second table with the features, with one row per hotel and feature. After all, many sites are able to provide this functionality in fractions of a second, so it is possible. – Gordon Linoff Jun 06 '15 at 14:41
  • And the best of them aren't using solely mysql imo – AsConfused Jun 06 '15 at 14:43
  • What limitations _are_ you willing to live with? Words only? Initial parts of word? No misspelled words? A finite list of possible words? – Rick James Jun 10 '15 at 04:59
  • The field is not gonna contain multiple words, being as they are phone numbers, so the match is always gonna be against words. And I could easily get rid of the leading "%" if I could do with just searching against the initial parts of the field. Unfortunately, that's not what I'm trying to do. – TheLearner Jun 10 '15 at 23:59

0 Answers0