MySQL Practice: Prefix Matching

Question

I have a table with codes and another table with prefixes. I need to match the (longest) prefix for each code.

There is also a secondary area in which I must restrict prefixes (this is due to the involvement of other tables). I don't think that would be important in most cases, but here's a simplified (normalized) scheme (I have to set item.prefix_id ):

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

It is true to cache the length of the prefix in the new field and index it. Most likely, cache group_id in the prefix table (although groups are pretty small tables, in most cases I don't think that performance gains will be achieved). the element table contains several hundred thousand entries; the prefix contains no more than 500.

Edit:

Sorry if the question has not been determined sufficiently. When using the word "prefix" I actually mean it, so codes should start with the actual prefix.

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

Expected result for the prefix column: (item.id, item.prefix_id):

(1, 2) Since: subgroup 1, 2 and 3 belong to the group 1, the code abc123begins with the prefix aand prefix abcand abcis logsterom two, so we take the id abc, which is equal to 2, and put it in item.prefix_id.

(2, 2) : {4} ( abcdef) , ( 4) 2, 1, 1, 2, 3 abc .

(3, 1) : a - .

(4, NULL) : 4 2, 2 abcdef, abc123 ( abc123 abcdef).

, , - . - . ( - , - " " ).

- :

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 KEY code3 (segment_id, group_id, code(3)). - 1, 2, 3 4 . , (4 ). - , , 4.

, .

Answer 1

group_id .

, group_id . , , , .

, :

ALTER TABLE `prefix` ADD INDEX `c_index` (
    `group_id` ASC, 
    `prefix` ASC
);

UPDATE:

UPDATE item i
SET 
    prefix_id = (
        SELECT p.id
        FROM prefix p USE INDEX (`c_index`)
        WHERE 
            p.group_id = i.group_id AND 
            p.prefix IN (
                LEFT(i.code, 4), 
                LEFT(i.code, 3), 
                LEFT(i.code, 2), 
                LEFT(i.code, 1)
            )                
        ORDER BY LENGTH(p.prefix) DESC
        LIMIT 1        
    )

, {1,4}. IN LIKE, c_index.

Answer 2

, , ... , ( , )

select
      PreQuery.Code,
      P2.ID,
      P2.SubGroup_ID,
      P2.Prefix 
   From
      ( select
              i.code,
              max( length( trim( p.Prefix ))) as LongestPrefix
           from
              item i
                 join prefix p
                    on i.prefix_id = p.id
           group by
              i.code ) PreQuery
      Join item i2
         on PreQuery.Code = i2.Code
         Join Prefix P2
            on i2.Prefix_ID = P2.ID
            AND PreQuery.LongestPrefix = length( trim( P2.Prefix )))

, - , , , .

Answer 3

, , . ... "PreQuery" ... , , , "", , , , , . , , , .

At the same time (and I can’t specifically test it now), if it is updated only on the basis of the FIRST record found for this identifier, just place the order in the DESCENDING order of the prefix length.

    update Item,
           ( SELECT 
                   I.ID, 
                   P.ID Prefix_ID, 
                   P.Prefix, 
                   I.Code, 
                   LENGTH( TRIM( P.Prefix )) as PrefixLen 
                FROM 
                   Item I 
                      JOIN SubGroup SG 
                         ON I.Group_ID = SG.Group_ID 
                            JOIN Prefix P 
                               ON SG.ID = P.SubGroup_ID 
                              AND LEFT( P.Prefix, LENGTH( TRIM( P.Prefix ))) 
                                = LEFT( I.Code, LENGTH( TRIM( P.Prefix ))) 
                ORDER BY 
                   I.ID,
                   LENGTH( TRIM( P.Prefix ))  ) PreQuery
      set 
         Prefix_ID = PreQuery.Prefix_ID
      where 
         ID = PreQuery.ID