What is faster in MySQL:
I want to execute a query and find all profiles which have 1 or interests from interests table

Important notes:
- all interests are originally checkboxes in internet explorer and are saves in the database.
- table profile has many attributes and has no foreign key to table interests
- table interests will almost never change (maybe a few times a year)
- there will be about 3000 profiles

[interest]
id (smallint, primary key, indexed)
desc (varchar 127, not indexed, not null)


A.
- select all interest id's from table interests which match 1 or more criteria
--> select id as interests_ids from interests where id in (1,2,3,9,7);
- and then select * from table profiles where xx = yy and where profile.id in (interests_ids)

possible disadvantage: execute 2 queries (the less you have to go to mysql, the best it is for performance)


B.
imagine profiles has a column called interest where values in field interests are stored as "~1~2~3~4~5~25". This way, multiple values can be stored in just 1 field

- select * from profiles where xx and where
profiles.interest like '%~1'
or profiles.interest like '%~2'
or profiles.interest like '%~3'
or profiles.interest like '%~9'
or profiles.interest like '%~7'

only 1 query, but like can be very slow and the query is longer in terms of characters (more bytes are to be send to the database).
Also, the interest field is a varchar, which is slower than a smallint.


C.
select * from profiles as p, interests as i where p.xx = xxx and where i.id in (1,2,3,9,7)

only one query, but will create a link/join table -> so maybe not performant when many persons making requests?

if I first check i.id in (1,2,3,9,7) and then where p.xx = xxx, will it maybe be faster? I think not because a join table will always be generated. If I first p.xx = xxx, then I will narrow the profile table and the join will be smaller.

What is the best solution in terms of performance, maintaince?
I have been thinking about this question several days before asking in this forum. I hope someone can help me.

Thanks
JohnQM

Interests should not be stored the way they are now... They should be in their own table

So, if I understand well, this should be a adequate table definition?

[interest]
id (smallint, primary key, indexed)
desc (varchar 127, not indexed, not null)

[interest_profile]
interestId (smallint, primary key, indexed)
profileId (smallint not indexed)

[profile]
id (smallint, primary key, indexed)
some attributes

Which query would probably be the fastest, A or C? (B should not be used anymore, right?)

Thanks
JohnQM

What is faster in MySQL:
I want to execute a query and find all profiles which have 1 or interests from interests table

Important notes:
- all interests are originally checkboxes in internet explorer and are saves in the database.
- table profile has many attributes and has no foreign key to table interests
- table interests will almost never change (maybe a few times a year)
- there will be about 3000 profiles

[interest]
id (smallint, primary key, indexed)
desc (varchar 127, not indexed, not null)

SELECT profiles.*
FROM profiles p, interests i, interest_profile ip
WHERE ip.profileId=p.id AND
p.interestId=ip.interestId AND
i.id IN (1, 2, 3, 7, 9);

Add indexes to:
interest: id
interest_profile: (profileId, interedtId)
profile: id