I'm an art director at a directory publisher which also has our directories online. I'm not at all knowledgable about MySQL databases, so I figured I could ask a question here and get a quick answer. Our new proposed website redesign loads horribly slow, about a minute almost any link you click. The web guy in charge of our website says we need to upgrade our server, but I suspect it's the programing that is causing the slow down.

This is the server we have:
2.5 GHz Celeron
512 MB RAM
120GB HD

There are about roughly 30,000 listings

The site doesn't have any large images, it's mostly text and some small banners. Shouldn't this server be able to handle it? Your answers are greatly appreciated, thanks!

That server should be able to handle 30,000 entries depending on how many people are online at any given time. Do you have accurate statistics on how many people access your site at a time?

Of course it's hard to say without knowing what kind of script you need and how the data needs to be processed. For a simple script that just lets a user browse through categories and pages of listings, then it shouldn't be too server intensive. But each additional "feature" will increase the resources the script needs. And the number of entries might not matter since not all data from the entire database is needed on every part of the site.

Is it the script itself that is going slow? Are you sure it isn't network latency (ie. time it takes for data to go from the webserver down through the internet to your PC)? Ask your programmer to add a timer to the script to time how long it takes for the script to go start to finish. You might also want to ask him to add a feature that will show which queries are being run on a page, and track the before/after times so you can see if any particular query is slowing everything down. MySQL should also keep a log of "slow queries", which you might want to look in to.

It largely depends on your situation.

As another thought you mentioned the images not being large. I'm assuming the images are being stored in the database. Is it possible that the images in the database are large and just being displayed smaller on the page? If that's the full image would still be loading even if it's being resized in the browser.

Hey Chroder, thanks for those suggestions, I think those are good ideas. I don't have any stats on the traffic unfortunately. It's a yellow page online directory for North Idaho and Eastern Washington mainly, I can't imagine too many people hitting it at once. The index page has listings A-Z, each letter as a link, and under each letter two or three main categories of that letter, also links. Click on a letter and you get all the categories in that letter, which is just like the yellow pages. It doesn't seem like it should be that complicated. The content is just organized differently, supposed to be more user friendly than the current site.Vangogh, the new site I believe has all the same images as the current one, which runs fine. I don't know where the images are stored though, that's something to look at.

I wish I knew more about this stuff, but I spend all my time in design for print. Thanks for your responses, I have a better idea of what to do now. Cheers!

To give you an idea: This webmaster-talk forum (stats about posts/threads/users at the bottom of the home page) takes about 0.2 seconds to process (on a good day, we're actually looking to optimize right now) the script. This includes querying the database, rendering templates etc. A thread page such as this one runs about 20 queries.

Obviously this site and yours are totally different applications, but the numbers should give you a rough idea of what is acceptable. If your script takes 10 seconds to parse, and you run 200 queries per pageload, then you probably need a new programmer

If I had to guess, I'd say that either your new script is some how reading too much data into memory. For example, if you have 1000 categories, perhaps the script is fetching all 1000 categories into memory regardless of weather the current page actually needs data from all of them. Or perhaps there is a query being run in a loop (usually rule #1 programmers learn when working with databases is never run queries in a loop! ). I realize you're not a programmer, so I'll give you an example of what I mean. Imagine that on your homepage you show 15 random categories and 3 random listings from those categories. A query in a loop means that for each of those categories, a query is run to fetch the 3 listings. That means 15x3 or 45 queries are run just to fetch that data. The more queries or the more complex the queries are, the longer it will take.

So hopefully you can get a query count and a query timer to see where the problems are. But from what you describe, I really don't think the site should take 60 seconds per page load. Even a badly programmed site doesn't take that long

Chroder, thanks man. Your info is very helpful. In the near future I'm going to try making my personal design site to help get my freelance business going. I'll be sticking around here for sure, there's good stuff in these forums. Much appreciated!