SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: quick question

  1. #1
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    quick question

    Hi

    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.

    Please advise!

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'...

  4. #4
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why do you see that as a problem - indexed values do not need to be unique.

  5. #5
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'?

    thanks!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •