I am developing an application where a member of the site will have about 5 characteristics in their member profile that can be represented by numeric arrays.
i.e. $colors=(1,3,6,8) means their favorite colors are "blue", "green", "orange" and "purple".
I am trying to decide the best way to represent this information in MySQL so that a search on these characteristics is as fast as it can be, assuming a search could involve up to 10 different profile variables, 5 of which are arrays containing between 1 and 12 integer elements.
I know the proper relational way to represent this data is to create a "color" table and key it off of "member.id", but then I'm joining up to 6 tables when I run this query. Assuming 10,000 members, each with up to 12 rows in these 5 tables, that seems like it could really drag even if everything is indexed properly.
The other alternative I have considered is imploding each array into a string like "3,4,5,7" and storing it in a single field in the member profile table. So member.color = "3,4,5,7". Then in a query I would SELECT where (color LIKE '%3%' OR color LIKE '%4%'.......etc.) or perhaps use REGEXP. But I know here as well, using LIKE and REGEXP drag down the efficiency of a query.
So I'm not really sure which way will produce the fastest query. Perhaps there is another way I have missed altogether so I appreciate any advice others may have.
i.e. $colors=(1,3,6,8) means their favorite colors are "blue", "green", "orange" and "purple".
I am trying to decide the best way to represent this information in MySQL so that a search on these characteristics is as fast as it can be, assuming a search could involve up to 10 different profile variables, 5 of which are arrays containing between 1 and 12 integer elements.
I know the proper relational way to represent this data is to create a "color" table and key it off of "member.id", but then I'm joining up to 6 tables when I run this query. Assuming 10,000 members, each with up to 12 rows in these 5 tables, that seems like it could really drag even if everything is indexed properly.
The other alternative I have considered is imploding each array into a string like "3,4,5,7" and storing it in a single field in the member profile table. So member.color = "3,4,5,7". Then in a query I would SELECT where (color LIKE '%3%' OR color LIKE '%4%'.......etc.) or perhaps use REGEXP. But I know here as well, using LIKE and REGEXP drag down the efficiency of a query.
So I'm not really sure which way will produce the fastest query. Perhaps there is another way I have missed altogether so I appreciate any advice others may have.