should I use LIKE to query tables with 4 million rows

Question

I am designing a search form, and I am wondering whether should I give the possibility to search by using LIKE %search_string% for a table that is going to have up to 4 million rows

Answer 1

In general, I would say no. This is a good candidate for full-text indexing. The leading % in your search string is going to eliminate the possibility of using any indexes.

There may be cases where the wait is acceptable and/or you do not want the additional administrative overhead of maintaining full-text indexes, in which case you might opt for LIKE.

Answer 2

The search with LIKE %search_string% is very slow even on indexed columns. Worstcase the search does a full table scan.

If a search LIKE search_string% is enough I'd just provide this possibility.

Answer 3

It depends - without knowing how responsive the search has to be, it could either be fine or completely no go. You'll only really know if you profile your search with likely data patterns and search criteria.

And as RedFilter points out, you might want to consider Full Text Search, if plain search isn't performing well

Answer 4

No, you should really only use LIKE '%...%' when your tables are relatively small or you don't care about the performance of your own or other peoples' queries on your database.

There are other ways to achieve this capability which scale much better, full text indexing or, if that's unavailable or not flexible enough, using insert/update triggers to extract non-noise words for querying later.

I mention that last possibility since you may not want a full text index. In other words, do you really care about words like "is", "or" and "but" (these are the noise-words I was alluding to before).

You can separate the field into words and place the relevant ones in another table and use blindingly fast queries on that table to find the actual rows.