sheardben — 2010-03-07T13:49:23-05:00 — #1
Quick question about what method would be more efficient.
I have a table photo_uploads and columns 'imageid','type','typeid','url'
'type' can be 'profile' or 'site' and 'typeid' is the id of that particular site or profile.
So if i want to select all photos of a persons profile whose userid is '7', i would have
SELECT * FROM photo_uploads WHERE type = 'profile' && typeid = '7'
I have another option which i thought of which is to have separate columns for profile_id and site_id
so in the example above i could rather have
SELECT * FROM photo_uploads WHERE profile_id = '7'
Which method is more efficient? Or am i doing this the complete wrong way?
My idea for the second option is that if a image is a site image and therefore profile_id is NULL for that row, mysql would perhaps skip that row and other similar rows when searching for all the profile images for that particular id making the query faster and more efficient.
philiptoop — 2010-03-08T07:51:01-05:00 — #2
The important thing to improve speed would be to index the fields in the first (type_id and possibly type) and in the second case (profile_id and site_id).
sheardben — 2010-03-08T07:57:56-05:00 — #3
The problem with that is that one site may have multiple photos and thus there will be duplicate rows of type='site' and typeid='someid'...
philiptoop — 2010-03-08T08:00:44-05:00 — #4
Why do you see that as a problem - indexed values do not need to be unique.
sheardben — 2010-03-08T09:00:51-05:00 — #5
Sorry - am relatively new to mysql and getting mixed up between the different meanings of indexes, primary keys, unique keys etc.. will go read up on them
So would i create an index of the combination of 'type' and 'typeid'?