mysql - Efficiency question - Selecting numeric data from one field -
i have pair of tables , need search numeric values in table1 match associated ids on table2. example:
table1
id | item 1 cat 3 frog 9 dog 11 horse
table2
category | contains group 1 1 group 2 3|9 group 3 3|9|11
originally thinking work, if searched "1", i'd end matching "11". looked sets, mysql docs state maximum number of elements 64 , have on 200 rows of items in table1. wrap each item id character (e.g. "|1|") doesn't seem efficient. each group have unique items (e.g., there won't 2 cats in same group).
i found similar topic problem , 1 of answers suggested making table, don't understand how work. new table containing what, exactly?
the other option have split contains 6 separate columns, since there's never going more 6 items in group, i'm not sure how search 6 columns without relying on 6 or queries:
category | c1 | c2 | c3 | c4 (etc) group 1 1 null null null group 2 3 9 null null group 3 3 9 11 null select * table2 c1 = '1' or c2 = '1' or c3 = '1' etc.
i'm not sure efficient way of handling is. use advice more experience normalizing kind of data please. thank you.
that "new" table contain 1 row each category animal belongs to.
create table animal( animal_id ,name ,primary key(animal_id) ) create table category( category_id ,name ,primary key(category_id) ) create table animal_categories( animal_id ,category_id ,primary key(animal_id, category_id) )
for example data, animal_categories table contain:
category_id | animal_id +-----------+------------+ | 1 | 1 | | 2 | 3 | | 2 | 9 | | 3 | 3 | | 3 | 9 | | 3 | 11 | +-----------+------------+
Comments
Post a Comment