We can't search on a field using a "contains" search

0 votes

I want to search for some text inside a field. This works great on other tables but it seems to work inconsistently.

I can search a 160,000 record table using the following SQL and it returns 35 records.

select * from MyTable where comments like '%doc%'

But in dbFront, it only returns 1 record. It looks like it can only find the text at the beginning of a field.

NOTE: I am not using the Full-Text search functionality.

in How To by (7.0k points)
edited by

1 Answer

0 votes
Best answer

The reason is that the table has more then 100,000 records. Regular LIKE searches that include '%' before and after the search term can't use database indexes and so they can cause significant performance issues.

To guard against that, dbFront will automatically revert to only a single following '%', which will allow the database server to use indexes.

  • Users can control this by manually surrounding the text with '%' themselves.
  • As an administrator you can also change the breakpoint from 100,000 to something else.
  • Or you can implement Full Text Search at the server level.

For more details please see: Using Default Wildcard Searches

by (64.3k points)
selected by
Welcome to the dbFront Q&A site, where you can ask questions and receive answers from other members of the community.
 | Minimalist Answer Theme by Digitizor Media
Powered by Question2Answer