Mimic Quick Find in SQL

[Scott]

Hello.

I have inherited a FileMaker 12 in-house application. I am an Oracle and MSSQL DBA, so SQL is not foreign to me. The FM implementation of SQL is a little odd, but totally usable.

One of the things the in-house app has is a Quick Find field that is able to seach nearly instantly for a substring within the database. I would like to mimic this from the SQL side for some web presentation of data, but all of my attempts are taking 20-30 seconds. The search is against a text field that has space delimited data of serial numbers. The query is to find all records that contain the substring.

SELECT id FROM SerialNumber_Record where serials like '%SN750001%'

Wildcard % is needed on both sides because the SN750001 may appear anywhere in the serials field. Serials is of type text and is marked as indexed.

This works, eventually, but the time it takes to execute seems silly when the FM Quick Find can do it in less than 1 second. Is there any SQL way to use the indexing or some better query without refactoring the database?

[Kris M]

When you say "web presentation of data" are you implying you are using Web Direct?

Are you using ExecuteSQL() in FileMaker?

need more info to answer your question i think.

Edited June 17, 2015 by Kris M