使用MYSQL存储索引列表？

Question

I have a MySQL/PHP performance related question.

I need to store an index list associated with each record in a table. Each list contains 1000 indices. I need to be able to quickly access any index value in the list associated to a given record. I am not sure about the best way to go. I've thought of the following ways and would like your input on them:

1. Store the list in a string as a comma separated value list or using JSON. Probably terrible performance since I need to extract the whole list out of the DB to PHP only to retrieve a single value. Parsing the string won't exactly be fast either... I can store a number of expanded lists in a Least Rencently Used cache on the PHP side to reduce load.

2. Make a list table with 1001 columns that will store the list and its primary key. I'm not sure how costly this is regarding storage? This also feels like abusing the system. And then, what if I need to store 100000 indices?

3. Only store with SQL the name of the binary file containing my indices and perform a fopen(); fseek(); fread(); fclose() cycle for each access? Not sure how the system filesystem cache will react to that. If it goes badly then there are many solutions available to adress the issues... but that's sounds a bit overkill no?

What do you think of that?

Answer 1

What about a good old one-to-many relationship?

records
-------
id     int
record ...

indices
-------
record_id int
index     varchar

Then:

    SELECT *
      FROM records
 LEFT JOIN indices
        ON records.id = indices.record_id
     WHERE indices.index = 'foo'