mysql query solution to this problem?

[happybanana]

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?

[Fou-Lu]

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:

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.

[Old Pedant]

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:

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:

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.

[happybanana]

Thanks for the advice gents, that's pointed me in the right direction.