mysql query solution to this problem?
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:
1.1.2 This is a parent row
1.1.3 This is another parent row
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?
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:
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:
You could do:
It's not a good idea. It's just something you'd do if you were desperate.
Thanks for the advice gents, that's pointed me in the right direction.
|All times are GMT +1. The time now is 06:22 AM.|
Powered by vBulletin®
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.