Professional Web Applications Themes

Most Efficient Join - MySQL

Hi all, I have a user table and an object table (for project and properties at the moment). I can get a list of all my Project Objects with the user that created them by doing this... select * from tbl_object join tbl_user on tbl_user.user_id = tbl_object.created_by where object_type = 1 #1 being project type Lovely. Now, technically a User is also an object (as is just about everything else in our application), but how efficient is it to have 'everything' in the one table and keep joining that to itself? select * from tbl_object tbl_object_project join tbl_object tbl_object_user on ...

  1. #1

    Default Most Efficient Join

    Hi all,

    I have a user table and an object table (for project and properties at
    the moment). I can get a list of all my Project Objects with the user
    that created them by doing this...

    select * from tbl_object join tbl_user on tbl_user.user_id =
    tbl_object.created_by where object_type = 1 #1 being project type

    Lovely.

    Now, technically a User is also an object (as is just about everything
    else in our application), but how efficient is it to have 'everything'
    in the one table and keep joining that to itself?

    select * from tbl_object tbl_object_project
    join tbl_object tbl_object_user on tbl_object_user.value =
    tbl_object_project.created_by
    and tbl_object_user.object_type = 5 #user type
    where tbl_object_project = 1 #1 being project type

    Part of me thinks that a user is an object and should be in the object
    table, and another part thinks it is just too clunky to keep joining
    tbl_object to tbl_object.

    What are you thoughts? Which is considered "technically" correct? Has
    anyone been down this road before? And, probably the most important
    question - which method is the most efficient?

    Eventually there will be a lot of data in the db, so efficiency will
    become more important.

    Many thanks,

    Amy

    amykimber@gmail.com Guest

  2. #2

    Default Re: Most Efficient Join

    com wrote: 

    Amy,

    Well, when you get right down to it, everything in the world is an
    "object". But that doesn't mean you should keep automobiles in the same
    table as tuna. IOW, you can abstract something too much. You need to
    look at the attributes of each object.

    Without more information on what your "objects" are, it's difficult to
    tell whether things should be in the same table or not.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. Working Efficient with ID CS
    By Harold_Lentink@adobeforums.com in forum Adobe Indesign Windows
    Replies: 2
    Last Post: June 17th, 07:30 AM
  3. Replies: 1
    Last Post: October 1st, 06:05 AM
  4. Looking for the most EFFICIENT way to do the following...
    By Sugapablo in forum PHP Development
    Replies: 0
    Last Post: August 25th, 06:50 PM
  5. Efficient structure
    By Info@Best-It in forum PHP Development
    Replies: 1
    Last Post: August 17th, 10:46 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139