Optimizing database calls while handling custom URLs

[eluser]Ian Beck[/eluser]
I'm currently coding a site using CodeIgniter that only uses CodeIgniter's default routing methods for a couple of sections (like the admin pages).

Instead, the site's navigation is laid out in a database table with a few key variables (id, parent id, and type of page being the main ones), and further details about any given page are stored in a different table (based on what type of page it is).

This is all fine and dandy, but I'm having trouble designing the model that handles the navigation table. My problem:

I don't actually have any idea how expensive database calls can get, so I'm having trouble optimizing the code (current project is small, so it probably doesn't matter, but I need to be able to use this code on much larger projects).

Does anyone know of any resources that can help me figure out whether to do lots of very specific database calls, or a few generic database calls? Alternatively, are there any clever ways that I can move the logic into SQL? For instance, SQL that could replace a loop that descends through the URL segments using parent IDs and the segment text to find the info for each page.

Sorry for these very generic questions, but I'm having trouble finding info.

[eluser]easymind[/eluser]
Well, you could always loop the thing you want to do a couple of hundread times and time it. Then do the other thing that maybe is faster and time that. But it my experience all things go fast anyway .

This is my generic answere.

[eluser]Ian Beck[/eluser]
The issue is actually less how fast they run in this scenario (since the site is small enough that either way will be very low on system resources). I just need to be able to reuse this code for other projects, so I'd like to design it in a way that at least theoretically will scale well to very complex navigation trees.

[eluser]easymind[/eluser]
Well, using trees and parentids requires looped functions and that takes time. And if you are speaking about humonguos trees maybe you should consider using ajax so you can load the big bad tree one time in the beginning and avoid regenerating it on every page refresh.

By the way, if you are talking about site navigation you can always limit the depth I think. Most menus don't go unlimited deep.

I think you want a looped SQL statment and try that out, but cannot find it.... let me look....

[eluser]easymind[/eluser]
http://dev.mysql.com/tech-resources/arti...-data.html

[eluser]Ian Beck[/eluser]
Thanks for the link! JOINs have always confused me, so I'll go through that and see if I can't create some tricky SQL queries.

[eluser]easymind[/eluser]
Good luck. I read that by using joins your depth will be limited to the amount of times you put in a JOIN statement. He is suggesting an alternative way of storing your data so you can retrieve limitless in depth. But your data is not stored like that and it will be a real commitment if you do it his way. But if you like it try it out and let us now if it works nice.

Normally I also use parent_ids so a recursive fucntion like this retrieves the full tree

Code:

function getTree($parent_id=0)
{
   $tree = array();
   foreach($this->pages->getFromParent($parent_id) as $page)
   {
      $page['children'] = $this->getTree($page['id']);
      $tree[] = $page;
   }
   return $tree;
}

I think this works...

[eluser]HdotNET[/eluser]
build code that uses any parent / child method, but recursively cache handy things like the directory /path/to/page, id path to page, breadcrumb data, etc in other fields/tables when admin updates are made.

that way you can just do a simple select on the REQUEST_URI to get the current page and a large amount of the required data.

if speed is an issue you can just cache any SELECT results, and add a delete cache function to your admin.

[eluser]frenzal[/eluser]
I have a similar structure and have a recursive function to generate a menu and make breadcrumbs, so i'll just cache that stuff and that seems good enough to me