SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)

    Hanging on a join query

    What could possibly cause MySQL to hang and not resolve on a query like so.

    SELECT * FROM personal LEFT JOIN addresses ON ( personal.recordid = addresses.parentid )

    I am well and truly stumped and angry - I've not in 6 years seen MySQL hang on something this trivial, and I've been using it a long time. I know it's something stupid but I can't seem to find it.

    The tables do not have the same number of rows, (nor should they, there'd be no point to using separate tables if they do). Hmm...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try a REPAIR on the tables

    do an EXPLAIN on the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Tried that - didn't work (repaired ok). Emptied the tables (they only have imported test data) and tried the query, worked. Repopulated, put a limit of 10, worked.

    Limit 100 and much slower, but worked. Hangs on any higher number of records or any attempt to add an order by clause. I'm stumped.

    Next I'm considering dropping and rebuilding the tables, see if that works.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    hangs because it's doing table scans -- the more rows, the slower it gets

    i'll bet you haven't created any indexes

    could you please do a SHOW CREATE TABLE for both tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    AH. Got it.

    recordid is my primary key, but wasn't set to be an index. Both the recordid and parentid fields of every table need to be indexed.

    Overlooked this because I'm writing a PHP system that autocreates the tables and hadn't wrote in the index setting as part of that code.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you declare a column (or columns) as PRIMARY KEY, you should ~not~ create an index on it, because PKs always get indexed anyway

    so do FOREIGN KEYs in mysql (after version something-or-other)

    is addresses.parentid a FOREIGN KEY or just an ordinary column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    In my scheme parentid is always a foreign key to a row on the parent table of the current table. It's an abstraction for the PHP table creation system.

    For example, a table called companies has a child table employees. The parent id of the employees table refers here to the row holding the specific company.

    Now in a non-abstract system the field would be companyid most likely.

    Tables can have multiple parents. The parent tables for addresses are companies and persons. Hence a "parent" column indicates which table holds the row in the unusual event that I need to join one (or both) of those tables back to the addresses table (because of the structure of the program it's almost always going to be the other way around).

    Anyway, thanks for your help - I'll remove the indexing call on primary keys but I need my code to index the parent and parentid fields when they get created (When the user declares that the table has one or more parents)

    This particular error was frustrating because I've done this a long while now, and yet this rather simple error caught me off guard and cost me a day of work.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you store a table name inside a column? that's a bad sign...

    anyhow, make sure that your "foreign key" is actually declared with FOREIGN KEY syntax -- otherwise you will have to declare the index for it manually

    have you done the EXPLAIN on your query? spotted anything of concern?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you store a table name inside a column? that's a bad sign...
    Of?

    The data of the table in question can be a child of one of three tables - companies, personal, claims. The only alternative is to create three tables with the exact same fields or use a master indexing schema which is a headache of another sort.

    anyhow, make sure that your "foreign key" is actually declared with FOREIGN KEY syntax -- otherwise you will have to declare the index for it manually
    Last time I checked MyISAM doesn't support foreign key declaration. And I find backing up large Innodb tables to be such a royal PITA it isn't worth it. As I said earlier I'm not new at this.

    have you done the EXPLAIN on your query? spotted anything of concern?
    That's how I discovered it wasn't indexing.

    I guess asking a n00b question gets one treated like a n00b. Thanks, but not interested in being talked down to.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, pardon me for trying

    you must understand that thousands of people post similar questions here, and those of us who go out of our way to try to help them have a few things we ask about that often pinpoint the problem

    and hey, you're the one who started things off by saying "it's obviously something stupid"

    and that crack about myisam not accepting foreign keys, you conveniently overlooked mentioning that these were myisam tables, and yet you lambaste me for talking about foreign keys?

    dude, if you feel slighted, then i'm sorry, but you're obviously in the wrong forum

    good luck with your issue
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    p.s. storing a table name in a column (meta-data) is a sign of poor design skills

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Baiting someone is a sign of a poor human being, or was there another point to that bit of snark?

    Knowing what the rules are is only the first step - knowing when they do and do not apply is the next and one it seems you haven't learned yet.

  13. #13
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,077
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    In my scheme parentid is always a foreign key to a row on the parent table of the current table. It's an abstraction for the PHP table creation system.

    For example, a table called companies has a child table employees. The parent id of the employees table refers here to the row holding the specific company.

    Now in a non-abstract system the field would be companyid most likely.

    Tables can have multiple parents. The parent tables for addresses are companies and persons. Hence a "parent" column indicates which table holds the row in the unusual event that I need to join one (or both) of those tables back to the addresses table (because of the structure of the program it's almost always going to be the other way around).

    Anyway, thanks for your help - I'll remove the indexing call on primary keys but I need my code to index the parent and parentid fields when they get created (When the user declares that the table has one or more parents)

    This particular error was frustrating because I've done this a long while now, and yet this rather simple error caught me off guard and cost me a day of work.
    Can you show a "CREATE TABLE" for the queries? What do you mean by:

    It's an abstraction for the PHP table creation system
    Are you trying to use MVC?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


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
  •