Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Nov 2012
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts

    mysql query solution to this problem?

    Hi,

    I have a database with two tables that I'm using to construct one large HTML table out of. I've coded a solution but it uses a loop inside a loop with some mysql_data_seek hacks and it's getting slow. I'm hoping to speed it up.

    One database table is essentially providing a 'parent title' and the other table is providing child sub parts to each title.

    For example, table parent:

    id text
    1.1.2 This is a parent row
    1.1.3 This is another parent row

    table child:

    id text
    1.1.2.a This is child text belonging to 1.1.2
    1.1.2.b This is child text belonging to 1.1.2
    1.1.2.c This is child text belonging to 1.1.2
    1.1.3.a This is another child row

    Now I *could* just combine everything into one table but that gets awkward for me elsewhere in my program.

    The code I created (and the core logic I need to replicate) needs to check that parent.id >= child.id AND parent.id < a mysql pointer I have looking ahead in child.id. This is because the table has to format with parent and child parts formatted correctly.

    I hope I've explained that OK. Can anyone offer any suggestions?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    The problem is you have no relationship enforcement, so no matter what you do you'll end up with complicated queries or magnitude increasing loops. Both of which will slow your code down.
    The best thing to do would be to simply add the parent id to the child table since you have a 1 to many relationship. Fortunately that can be done easily by slicing up the existing id, so long as the format is identical for every record a regex can be used to match the x.x.x values out of the existing id. You can then either retain the child's id as x.x.x.Y, or you can use a composite key of the parent/child with x.x.x and Y.
    Querying than becomes exceedingly easy:
    Code:
    SELECT pt.text, c.text
    FROM parent_table pt
    INNER JOIN child_table c ON c.parent_id = pt.id
    ORDER BY pt.id, c.id ASC
    Which now becomes a single loop. Formatting is then controlled simply by tracking changes in the parent. Using seeking is very slow and should be avoided in favour of ordering instead.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    happybanana (02-19-2013)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Well, to be fair, you don't really have to change the existing tables, at all.

    You will get better performance if you do. That is if you convert your child table to:
    Code:
    id     subid   text
    1.1.2   a      This is child text belonging to 1.1.2
    1.1.2   b      This is child text belonging to 1.1.2
    1.1.2   c      This is child text belonging to 1.1.2
    1.1.3   a      This is another child row
    but you *can* do that on the fly, in the query, if you have no control over the existing data.

    You could do:
    Code:
    SELECT pt.text, c.text
    FROM parent_table pt, child_table c 
    WHERE pt.id = SUBSTR( c.id, 1, LENGTH(pt.id) )
    ORDER BY pt.id, c.id
    But that SUBSTR function will mean that you won't be able to take advantage of any index on child_table.id and, of course, you pay the penalty of the extra time needed to use SUBSTR.

    It's not a good idea. It's just something you'd do if you were desperate.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    happybanana (02-19-2013)

  • #4
    New Coder
    Join Date
    Nov 2012
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for the advice gents, that's pointed me in the right direction.


  •  

    Posting Permissions

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