SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast the-webber's Avatar
    Join Date
    Mar 2009
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post optimizing multi table join

    Getting more comfortable with mysql but this seems to be the best place to go when i'm stumped... so here i am. Below is the code for a multi table join that I'm trying to optimize

    Code SQL:
    SELECT  pu.USER, mgs.countyName,  mgs.stateAbbr, mgs.cityName,mgs.latitude, mgs.longitude , mu.userType, mu.privacy   , mt.certification, mt.availability, mt.wage  FROM 
    			morris_geoSecondaryData  mgs
    			INNER JOIN phpfox_user pu
     			ON pu.id = mgs.id 
    			INNER JOIN
    			morris_users  mu
    			ON mu.id = mgs.id 
    			INNER JOIN 
    	  		morris_teachers mt 
    			ON mu.id = mt.id WHERE   mgs.latitude >= 40 AND mgs.latitude <=41 AND  mgs.longitude >= -74 AND mgs.longitude <= -73 AND  mu.userType=1

    all tables have a primary key on `id`
    except for `morris_geoSecondaryData` mgs which has a primary key on id,latitude,longitude (some people have 2 zip codes so `id` alone isn't unique)

    when I analyze this query with Explain I see that the entire table morris_users mu is scanned.

    I've tried tweaking indices in various ways... but to no avail. Any suggestions?

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by the-webber
    except for `morris_geoSecondaryData` mgs which has a primary key on id,latitude,longitude (some people have 2 zip codes so `id` alone isn't unique)
    If the id is meant to represent the primary key then it shouldn't matter that two people have more then one zip. In that case the field representing the person should not be the primary key. Instead the first thing I would recommend doing is making the id the primary key. Then make the field representing the person,latitude and longitude a unique key.

    With that said, what are the results you would like back from the posted query? If you could go a little more into the relationships between tables and their purpose that would probably also be helpful.

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    If what you say about id field consistently representing the primary key the the below is incorrect. It appears the below joins two tables on two unrelated primary key fields. This doesn't make any sense. The relationship should be primary key to foreign key. primary keys shouldn't share a relation between tables. Primary keys represent unique row data on a individual table basis. Seems as if the morris_teachers table would need to a foreign key column to morris_users in this instance. However, without seeing the intended relationships that's only a speculation. However, if that's true (agina a speculation) why not just has a user_ types table and relate a morris_user to a user_type through a look-up table which represents many to many relationship. I would recommend a many to many because a teacher could also be a parent for instance.

    Code SQL:
       INNER 
        JOIN
          morris_teachers mt
          ON 
          mu.id = mt.id ) // PRIMARY KEY => PRIMARY KEY

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Seems like the below would surely make more sense?

    morris_users
    • id (primary key)
    • name


    morris_user_types_toc
    • id (primary key)
    • morris_user_id
    • morris_user_type
    • unique key(morris_user_id,morris_user_type)


    morris_user_types
    • id (primary key)
    • name


    morris_geoSecondaryData
    • id (primary key)
    • morris_user_id (references morris_users(id))
    • countyName
    • stateAbbr
    • cityName
    • latitude
    • longitude
    • unique key(morris_user_id,latitude,longitude)


    I don't really understand the purpose of phpfox_user though.

    Another question though – Is this only for a individual school or will it need to be for several schools? In the case that the system could hold data for several schools it would probably be best to factor out and abstract everything even more.

    users
    • id (primary key)
    • school_id (references schools(id))
    • name


    schools
    • id (primary key)
    • name


    user_types_toc
    • id (primary key)
    • user_id
    • user_type
    • unique key(user_id,user_type)


    user_types
    • id (primary key)
    • name


    geoSecondaryData
    • id (primary key)
    • user_id (references users(id))
    • countyName
    • stateAbbr
    • cityName
    • latitude
    • longitude
    • unique key(user_id,latitude,longitude)

  5. #5
    SitePoint Enthusiast the-webber's Avatar
    Join Date
    Mar 2009
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, thanks for the input. I realized from your feedback that i should provide some more feedback. I've attached a screen shot that should be quite helpful.


    I'm trying to get the set of all users of a specified userType from morris_users that are within a certain latitude and longitude from morris_geoSecondaryData in the latter table, users have 1 or 2 zipcodes (therefore have 1 or 2 pairs of latitudes and longitudes) . I'm also trying to get relevent information from morris_teachers mt .

    I'll look at my query again.
    Attached Images Attached Images

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    There is no relationship between a user and a teacher? A user is separate from a teacher?

    The given table morris_geoSecondaryData doesn't have any way to relate back to a teacher or user. Shouldn't there be a foreign key column?

    I think your problem lies in your table structures. Have a look at my previous post.

    Your teachers and geoData tables seem to using the the primary key as foreign key also which would be wrong.

  7. #7
    SitePoint Enthusiast the-webber's Avatar
    Join Date
    Mar 2009
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is no relationship between a user and a teacher?
    No 'user' is the actual username (sorry if that was unclear)

    i'm using myIsam and linking the tables with 'id' which uniquely associates each 'user' (ie username) which a 'id' . Every table that I am using has an 'id' column...i fully admit that I might be 'missing' something here about keys and linking tables


    phpfox_user ...is a separate table with lots of other information based on phpfox (a social network script).

    i'm allowing users to be either teachers or non teachers--hence the 'morris_users.usertype' column.

    morris_users contains the most general information about users
    *id : (used by all tables...just a unique id for each user)
    *userType : whether or not they are a teacher
    *privacy : privacy settings

    phpfox_user
    *id
    *user (user name)

    morris_teachers cotains information that only pertains to teachers
    * id
    * wage (what they charge)
    * certification (are they certified)
    *availability

    morris_geoSecondaryData geographic information for all users (teachers and non teachers)
    * id
    *zipcode (teachers can supply up to 2 zip codes...so not unique)
    *latitude (associated with 1 particular zipcode)
    *logintude (associated with a 1 particular zipcode)
    * other fields not relevant to my current problem

    as far as the foreign key issue, although i'm using myisam i'm trying to link the tables together with 'id' .. what i don't understand is why mysql is seeing that a 'possible' key for morris_users is 'id' but is not using the key and is doing a full table scan


    i'll reexamine your previous post.
    Last edited by the-webber; May 10, 2009 at 11:55. Reason: added more info

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Yeah, I recommend you take a look at my long post with the lists.

  9. #9
    SitePoint Enthusiast the-webber's Avatar
    Join Date
    Mar 2009
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ONe question
    can you explain to me why the user_types_toc table in your lilst has a user_id and an id column.. how would these two columns differ?

    user_types_toc
    id (primary key)
    user_id

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,052
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    The id may not be equal to the user_id. That isn't the purpose of the primary key. The primary keys responsibility is to distinguish individual rows in the table. You could remove the id through and make the unique key a primary key instead. The user to type relationship is unique. You wouldn't want a user to be related to the same type multiple times.

  11. #11
    SitePoint Enthusiast the-webber's Avatar
    Join Date
    Mar 2009
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks! i see now that i need to rewrite the db schema. i really appreciate this --as this thread was a major light bulb for me. i was not using primary keys as they were intended to be used

  12. #12
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Imagine table a and table b.

    Imagine both have a primary key and you need to be able to relate one or more records in table b, with one record in table a. if so, table b needs to have a foreign key column. The foreign key needs to be the primary key for the record in table a, that it relates to.

    so you could create the two tables like this

    Code MySQL:
    create table A
    ( id int not null auto_increment
    , name varchar (32) not null
    ) engine=innodb default charset=latin1;
     
    create table 2
    ( id int not null auto_increment
    , address varchar(32)
    , email varchar(99)
    , phone varchar (24)
    , user_id int not null
    , constraint two_one_fk
        foreign key (user_id)
          references A(id) ON DELETE CASCADE
    ) engine ......

    Before you set on delete cascade, look up the alternatives, which may set the id to null if the parent table record is deleted or you can prevent the parent from deletion by not setting a delete attribute. (default is restrict).
    look up on update cascade too.

    Also, if you have two columns in a table which when paired, are unqiue to that record, you might be better to use them as a 'composite key' (search term). Then in your second (child) table, you would have a column for each of those with the constraint set as you want (see above). Then (if it is set to 'on delete cascade on update cascade', for example), if you update the parent table, the child one updates immediately. I love it that it does that coz it makes maintenance of 'referential integrity' (search term), much simpler.

    I hope I have explained that correctly, an such that it is easily understood.

    bazz


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
  •