Лучшая практика MySQL: сопоставление префиксов

У меня есть таблица с кодами и другая таблица с префиксами. Мне нужно сопоставить (самый длинный) префикс для каждого кода.

Существует также дополнительная область действия, в которой я должен ограничивать префиксы (это включает в себя добавление i n другие таблицы). Я не думаю, что в большинстве случаев это будет иметь значение,но вот упрощенная (нормализованная) схема (я должен установить item.prefix_id ):

group (id)
subgroup (id, group_id)
prefix (id, subgroup_id, prefix)
item (id, group_id, code, prefix_id)

Можно кэшировать длину префикса в новом поле и индексировать его. Можно кэшировать group_id в таблице префиксов (хотя группы представляют собой довольно маленькие таблицы, в большинстве случаев я не думаю, что можно получить какое-либо увеличение производительности). таблица содержит несколько сотен тысяч записей, префикс содержит не более 500.

Редактировать:

Извините, если вопрос не был определен достаточно. Когда я использую слово «префикс», я имею в виду именно его, поэтому коды должны начинаться с фактического префикса.

subgroup
id   group_id
-------------
1    1
2    1
3    1
4    2

prefix
id   subgroup_id  prefix
------------------------
1    1            a
2    2            abc
3    2            123
4    4            abcdef

item
id   group_id     code    prefix_id
-----------------------------------
1    1            abc123  NULL
2    1            abcdef  NULL
3    1            a123    NULL
4    2            abc123  NULL

Ожидаемый результат для столбца префикса: (item.id, item.prefix_id):

(1, 2) Поскольку: подгруппы 1, 2, 3 относятся к группе 1, код abc123 начинается с префикса a и префикса abc и abc является самым логичным из двух, поэтому мы берем идентификатор abc , равный 2, и помещаем его в item.prefix_id .

(2, 2) Потому что: хотя префикс {4} (который равен abcdef ) является префиксом соответствия logest, его подгруппа (которая равна 4) находится в группе 2, но элемент находится в группе 1, поэтому мы можем выбирать из подгрупп 1, 2, 3, и все же abc является самым логичным совпадением из трех возможных префиксов.

(3, 1) Потому что: a является логестом совпадение.

(4, NULL) Потому что: элемент 4 находится в группе 2, а единственный префикс - в группа 2 - это abcdef , которая не соответствует abc123 (потому что abc123 не начинается с abcdef ).

Но как I сказал, что все нащупывание не является существенной частью вопроса . Моя основная задача - сопоставить таблицу с возможными префиксами с таблицей строк и как это сделать лучшим способом. (Лучшее означает оптимальный компромисс между удобочитаемостью, ремонтопригодностью и производительностью - отсюда «лучшая точность» в названии).

В настоящее время я делаю что-то вроде:

UPDATE item USE INDEX (code3)
    LEFT JOIN prefix ON prefix.length=3 AND LEFT(item.code,3)=prefix.prefix
    LEFT JOIN subgroup ON subgroup.id=prefix.subgroup_id
WHERE subgroup.group_id == item.group_id AND
    item.segment_id IS NULL

Где code3 - это ] КЛЮЧ code3 (segment_id, group_id, code (3)) . - И та же самая логика повторяется с 1, 2, 3 и 4 в качестве длины. Вроде довольно эффективно, но мне не нравится наличие в нем дублирования (4 запроса на одну операцию). - конечно, это в случае, когда максимальная длина префиксов равна 4.

Спасибо всем за то, что поделились своими идеями.