querying id's according to an id's array

Question

my website includes a mysql db with two tables. one table has a field called 'id_array', and it contains a string look like this: '1,3,7,78,89,102'. it represents an array of id's in the second table.

what is the best strategy for retrieving the id's in the second table? i want the fastest way.

currently i am using 'SELECT * FROM first_table WHERE id IN (id_array)'.

i used to query one by one, but i assume it is a lot slower.

if someone has a better structure to offer me for the db, that would be great. this structure is the best i came up with, but i'm pretty sure it is not so efficiant. i need a fast and convenient way to find all the id's that belong to the id from the first table.

help would be appreciated.

Answer 1

A comma separated value means the data is denormalized -- the IN clause won't work for you, because it works on distinct values.

Short Term Solution

Use MySQL's FIND_IN_SET function, like this:

SELECT * 
  FROM first_table 
 WHERE FIND_IN_SET(id, id_array)

Long Term Solution

...is not to store values in this manner, which means having a table to store the distinct values and a table to join that table of distinct values to the original table. IE:

FIRST_TABLE

• first_table_id (primary key)

FIRST_TABLE_TYPE_CODES

• type_code_id (primary key)

FIRST_TABLE_TYPE_MAP

• first_table_id (primary key)
• type_code_id (primary key)