How to replace a slow query with LIKE?

Question

Facebook

There is a not very efficient database (FileMaker via ODBC, Mac OS X Server) for 15 thousand records with a poor structure, which needs to be searched quickly (less than a second) in one field. The field is prefabricated. It stores phone numbers separated by a space, for example, "5555555 4564472 89184533335".
The search is done with a query like

SELECT name FROM table WHERE phones LIKE '%4564472%'

It works very slowly. How to save the situation?

Answer 1

Just in case, check if the field with phones is indexed; if none or index minimal, change to all. Most likely indexed, of course.
The best option is to redo it as da0c advises, only you don’t need a fixed length and you don’t need to finish off with spaces, in FM it makes no sense. Here I tested one relatively simple alteration, but it turned out to be even slower.
If the database is on the server and SQL is not important, you can request data via HTTP GET; the data will be in XML, but if desired, in versions up to 11 inclusive, you can run them right there through XSLT.

Answer 2

First normal form

Answer 3

I propose to change the structure, add a phones label that relates to table as many (from the side of phones) to one. Parse the table.phones field once and put it neatly into the phones table, so that there is one phone in one record. Moreover, make the field with the phone of a fixed length, finish short phones on the right with spaces. After all this, make an index in the phones table by this field and, in theory, the speed should increase significantly.

Answer 4

As I understand it, phone numbers are stored as a string. Then, with minimal database changes, you can try Fulltext index and search through MATCH / AGAINST.

Answer 5

Is there an opportunity to intervene in the logic? Sphinx to master for example

Answer 6

Well, for 15 thousand records, I would definitely remake the structure. Be sure to index the fields, well, consider changing the ODBC driver to something less slow, for example, OLE DB

Answer 7

You can try to cache this into a temporary table in memory.

Answer 8

Convert this fucking table to two others (see answers above) + put a VIEW that will not spoil the logic of the program.
Have you forgotten BTREE indexes? I don't know if this is possible on your system.

Answer 9

Sorry, not familiar with FileMaker. But maybe it will give you something to think about.
Oracle has the ability to create an index based on a function.
Those. look for where instr(phones, '4564472') != 0, creating an index on instr(phones, '4564472').