Welcome to the MacNN Forums.

SirCastor · Feb 15, 2006, 12:38 AM

Hey all,

I've been experimenting with MySQL a lot recently. It's been a lot of fun, but one of my experiments has been calling my navigation bar from a db. The only thing that I ever worry about is simply wasting resources. The page is sending a call to the DB everytime it's requested.

Should I worry? Obviously the data is not changed so frequently that it needs to be called. But I guess my question is "What is too heavy a load?" Should I worry?

mduell · Feb 15, 2006, 01:00 AM

It is a waste of resources, but if it's a low volume site, it's neat trick/learning exercise and you might as well leave it.
I'd never do it on my site, but I'm looking at serving 12 pages/second.

SirCastor · Feb 15, 2006, 01:15 AM

That's the funny thing. I'm doing about 12 hits a day, and they're mostly from me. It's just a private site I'm using for experimentation.

The next step I think would be to cache it somewhere every-so-often to create a static file. Then when the site is called, I could simply include that file rather than put a request to the database. Not instant, but on the other hand, not as harsh.

Love Calm Quiet · Feb 15, 2006, 05:08 AM

Wasting resources? I'm doing the same thing with Nav bar...
What's to worry about? In *all* my pages there are at least *some* other MySQL calls... since they're dynamic news pages.
And there *is* caching going on by the server. Isn't that as it should be? The call to nav is an awfully small call compared to loading entire page content built on-the-fly.

Will I need 1000s or 10,000s of hits/day for this to be an issue?

moodymonster · Feb 15, 2006, 05:09 AM

Originally Posted by mduell

It is a waste of resources, but if it's a low volume site, it's neat trick/learning exercise and you might as well leave it.
I'd never do it on my site, but I'm looking at serving 12 pages/second.

that's like over 30m page views/month. What site is it?

moodymonster · Feb 15, 2006, 05:16 AM

http://www.mobilegamefaqs.com/gamesarchive.php

^this gets >1200 visits a day, the main list is dynamically drawn from MySQL, the side lists are also drawn from MySQL. The list of genres and handsets (on the left) is also dynamically created. If you choose a handset it then checks against a list over >75,000 combinations and returns the results.

The contents of the main list is linked across tables ie there is a developer table, genre table etc - this is all put together when requested.

If the site got really busy I would look at collating that data into one table when an entry is created rather than referencing across a range of tables.

But for the time being it's fine at handling it, does ~12 queries/sec and average server load of .6

Millennium · Feb 15, 2006, 05:50 AM

Originally Posted by SirCastor

That's the funny thing. I'm doing about 12 hits a day, and they're mostly from me. It's just a private site I'm using for experimentation.

The next step I think would be to cache it somewhere every-so-often to create a static file. Then when the site is called, I could simply include that file rather than put a request to the database. Not instant, but on the other hand, not as harsh.

Another technique you can use is to have your scripts either include the menu file or regenerate it if it can't be found. Then have whatever script modifies the database delete the file when it's done. The next person to hit the site will cause the file to be automatically regenerated, thus applying your changes.

mduell · Feb 15, 2006, 11:00 AM

Originally Posted by moodymonster

that's like over 30m page views/month. What site is it?

flightaware.com

moodymonster · Feb 15, 2006, 11:08 AM

Originally Posted by mduell

flightaware.com

cool

Millennium · Feb 16, 2006, 05:26 AM

By the way, if you're only getting a few hits a day, then you're not wasting resources at all, because the resources used to generate the menu would otherwise go unused. If your site were to suddenly grow popular and get thousands of hits a day, then there might be something to worry about, but what it means to "waste resources" is different for low-volume and high-volume sites.

moodymonster · Feb 16, 2006, 09:57 AM

Also - slighly unrelated to the issue in hand but anyway - DB setups vary in how much they are used. One DB setup may perform fast for low volume usage but need a different setup for high volume. But, the high volume setup would lag if used for low volume usage.

At least that's what I get from my various 'travels' across MySQL sites etc

El Gato · Feb 16, 2006, 10:42 AM

Originally Posted by Millennium

Another technique you can use is to have your scripts either include the menu file or regenerate it if it can't be found.

This, in my opinion, is the proper way to create a menu if you have concerns that the content will need to be changed regularly/fairly often. It's much simpler to update a single flat file than a db when you need to make changes - it'll also help to cut down on your page weight.

If you're just starting to learn PHP/MySQL this is a good exercise to work through, but you should also be keeping an eye out for ways to design your sites and db's so as to minimize the amount of code used and calls to the db.