Чем заменить медленный запрос с LIKE?

Question

[ChemAli]

Есть не очень расторопная БД (FileMaker через ODBC, Mac OS X Server) на 15 тыс. записей с плохой структурой, по которой нужно быстро (менее секунды) делать поиск по одному полю. Поле сборное. В нем хранятся номера телефонов через пробел, например, «5555555 4564472 89184533335».


Поиск делается запросом типа

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

Работает это очень медленно. Как спасать ситуацию?

Comments

[strib]

А база то какая?

[quantum]

Переделать структуру не вариант?

[ChemAli]

Переделать — возможно. Но ищем «быстрое решение», так как переделки потребуют непрогнозируемо длительного процесса.

Answer 1

[MikhailEdoshin]

На всякий случай проверьте, проиндексировано ли поле с телефонами; если нет или index minimal, переставьте на all. Скорее всего, проиндексировано, конечно.

Оптимальный вариант — переделать как советует da0c, только фиксированной длины не надо и пробелами не надо добивать, в FM это бессмысленно. Я тут потестировал одну относительно несложную переделку, но она еще медленнее оказалась.

Если база на сервере и SQL не принципиален, можно через HTTP GET данные запрашивать; данные будут в XML, но при желании в версии до 11 включительно можно их прямо там через XSLT прогнать.

Comments

[ChemAli]

Не подскажете как через HTTP GET это сделать? Что-то я не нахожу такого в справке.

[MikhailEdoshin]

Этого в справке нет, это т. н. XML publishing interface, вот руководство (PDF, к 12-й версии; в 11-й и ранее, думаю, то же самое, но еще и XSLT был на сервере). Кстати, есть и PHP обертка к нему, но я в PHP плохо разбираюсь, поэтому ничего умного не скажу.

Вам нужно убедится, что XML интерфейс присутствует (в контрольной панели сервера это должно быть наглядно видно), разрешить доступ к вашей БД через XML (в настройках учетных записей и привилегий создайте подходящую запись — для тестирования удобно guest — назначьте ей, скажем, встроенный набор привилегий только для чтения ([Read-Only Access]) и в его настройках отметьте Access via XML Web Publishing, после чего попробуйте послать GET-запрос вида:

http://<имя или IP сервера>/fmi/xml/fmresultset.xml?-dbnames

Должен вернуть список доступных БД в XML. Если работает, то поиск телефонов будет примерно такой:

http://<имя или IP сервера>/fmi/xml/fmresultset.xml?-db=<имя файла без  .fp7>&-lay=<имя макета>&phones=<номер телефона>&-find

Там есть еще операторы поиска, но в вашем случае операторы не нужны.

[MikhailEdoshin]

Да — поле должно быть на макете, иначе не найдет; вернет данные из всех полей на макете, так что оставьте только нужные.

[ChemAli]

FileMaker API для PHP мы пробовали, но он тоже не отличается расторопностью. Поэтому пытались найти более быстрый вариант работы через ODBC драйвер.

Спасибо вам за промывку мозгов!

[ChemAli]

Это работает относительно нормально даже без разбиения на отдельные поля. Запрос отнимает 0,4-0,6 с. Сочли приемлемым на данный момент.

Еще раз огромное Вам спасибо, Михаил!

[MikhailEdoshin]

Не за что :) Если что, вот русский форум по FM.

Answer 2

[Stdit]

Первая нормальная форма

Answer 3

[da0c]

Предлагаю поменять структуру, добавить табличку phones которая относится с table как многие(со стороны phones) к одному. Поле table.phones один раз распарсить и положить аккуратно в таблицу phones, так, чтобы в одной записи лежал один телефон. Причем поле с телефоном сделать фиксированной длины, короткие телефоны добивать справа пробелами. После всего этого сделать индекс в таблице phones по этому полю и, по идее, скорость должна в разы увеличиться.

Answer 4

[Николай Васильчук]

Я так понимаю, номера телефонов хранятся строкой. Тогда с минимальными изменениями БД можно попробовать Fulltext индекс и поиск через MATCH/AGAINST.

Answer 5

[Dzen_Marketing]

Есть ли возможность вмешается в логику? Сфинкс примастырить например

Answer 6

[ipadm]

Ну я бы для 15 тысяч записей точно переделал-бы структуру. Обязательно проиндексируй поля, ну и рассмотри вопрос смены драйвера ODBC на что-то менее тормознутое, к примеру OLE DB

Comments

[da0c]

OLE DB обычно врапер к ODBC, если не ошибаюсь

[ChemAli]

Поля проиндексированы средствами БД, драйвер делает то же самое. Облегчения это не принесло, увы,

[MARDEN]

Если используется LIKE %%, то всегда будет фулскан.

[da0c]

По поводу LIKE %% и фулскана очень не уверен, имхо, почти любую СУБД можно заставить юзать индекс

[MARDEN]

Не знаю насчет других СУБД, но касательно MySQL это так.
Вот цитата из книги High Perfomance MySQL:
MySQL can’t perform the LIKE operation in the index. This is a limitation of the
low-level storage engine API, which allows only simple comparisons in index
operations. MySQL can perform prefix-match LIKE patterns in the index because
it can convert them to simple comparisons, but the leading wildcard in the query
makes it impossible for the storage engine to evaluate the match. Thus, the
MySQL server itself will have to fetch and match on the row’s values, not the
index’s values.

[shagguboy]

>По поводу LIKE %% и фулскана очень не уверен, имхо, почти любую СУБД можно заставить юзать индекс
это простите как?

Answer 7

[vaniaPooh]

Можно попробовать закэшировать это во временную таблицу в памяти.

Answer 8

[Константин]

Переделать эту грёбаную таблицу на две других (см. ответы выше) + поставить VIEW который не испортит логику программы.
Вы BTREE индексы не забыли? Не знаю насколько это возможно в вашей системе.

Answer 9

[dmgorsky]

Извините, не знаком с FileMaker. Но, возможно, наведёт на размышления.
В Oracle есть возможность создать индекс на основе функции.
Т.е. искать where instr(phones, '4564472') != 0, создав индекс по instr(phones, '4564472').

Comments

[MikhailEdoshin]

В FM такое как раз возможно (называется «вычисляемое поле»), но так можно будет искать только один номер — а другие?