I am planning to have an InnoDB MySql table with tens of millions of rows.
Each row represents a record that can belong to AT LEAST one of 200+ categories. Most of the records will belong to 2 or more categories. All the categories represented by a numeric id, from 1 to 200+.
What is the most efficient way to store these multiple categories within each record to get the fastest look up speed when querying to return all the records belonging to one of the categories?
I think the fastest way will be to have a separate column for each of the categories and then just query that column. But the problem is I will have to make 200+ columns and will be new categories added in the future. Also, I don’t think having 200+ indexes will do any good for my server.
Then I thought of creating a separate record for each of categories, so there would be only one column for categories and the index will work just fine. The problem with this approach is that the table is already in tens of millions and it will keep on growing over time. If I will throw in duplicate records for each of the current multi-category records, the table size will quickly become unmanageable plus it will affect the performance.
The other approach I can think of will be to store all the categories related to a record in one column separated by comma or any other separator and then do a LIKE %% search. But If I understand correctly, this method doesn’t use an index and will result in the slow lookups.
So what is the correct way to approach it? I was even thinking of creating a separate table for each category, but that would be unacceptable when I will need to do some other types of queries across whole data.