How store arrays in DB for faster search

scharley

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.

amcgrath

what 6 tables would you be joining?

select a.user, b.color from users a, color_grp b where a.user_id = b.user_id and b.color_id in (implode(",",$color_arr))

Table users has your user info, table color_grp has user_id, color_id and (unseen here) color table has color_id color_name). The primary key of color_grp is user_id,color_id....

amcgrath

You wrote:
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.

PLEASE PLEASE don't do this. It will only come back to haunt you if you do.

scharley

Assume along with favorite colors, there is favorite fruits, favorite vegetables, favorite spices, favorite animals, and favorite birds, each being represented by 0 to 12 numbers. In addition, there will be about 20 other single valued things like age, weight, height, likes_chocolate, etc.

So if I wanted to find members with favorite colors = red or blue AND favorite fruits = apples or oranges AND favorite vegetables = carrot or cabbage or cucumber........AND age < 22 AND height > 6 AND likes_chocolate = TRUE......etc, I would have to join member, color_grp, fruit_grp, veg_group, spice_grp, animal_grp, and bird_grp. Each *_grp table will probably have between 30,000 and 120,000 rows.

I don't have a lot of experience with actual queries like this against the scale of database I'm developing for, so maybe it's no big deal to run a query like that in MySQL as long as I create the proper indexes. Or perhaps I should not allow members to do such a complicated query in the first place, but I'm trying to allow as much search functionality as possible since that is a key benefit of this site.

Anon

How about doing it with two tables? One table to store the member information, then a second table to store their favorites. For example:

create table member(
id number,
surname varchar2(30),
givenname varchar2(30),
emailaddr varchar2(40),
...etc...);

create table favorites(
member_id number,
favorite_type varchar2(10),
favorite_is varchar2(40));

The to find out everyone who likes cats or has a favorite color of blue or black, you would do:

select *
from member, favorites
where id = member_id
and ((favorite_type = 'COLOR' and favorite_is = 'BLUE')
or (favorite_type = 'COLOR' and favorite_is = 'BLACK')
or (favorite_type = 'ANIMAL' and favorite_is = 'CAT'));

or equivalently:

select
from member, favorites
where id = member_id
and favorite_type = 'COLOR'
and favorite_is = 'BLUE'
union
select
from member, favorites
where id = member_id
and favorite_type = 'COLOR'
and favorite_is = 'BLACK'
union
select *
from member, favorites
where id = member_id
and favorite_type = 'ANIMAL'
and favorite_is = 'CAT';

With the proper indexes on member(id) and favorites(member_id, favorite_type) retrieval would be fast even for large numbers of members with lots of favorites and lots of search criteria. To do 'A and B' instead of 'A or B' would just be changing the 'or'/'union' structuring, but still would be extremely efficient.

Of course to further normalize you would use a foreign key in place of favorite_type.

-- Michael
Darkstreak Computing & Innovations
www.darkstreak.com

scharley

I think that approach makes a lot of sense. I'll give it a try and see what happens. Thank you very much for the help.