MySQL queries - How many is too many?

[KMxRetro]

Hi everyone once again,
Quick query regarding queries...

I'm creating a Yahoo! style directory (only a couple of hundered links) as a section of my site. I intend to put this month's and last month's "Site of the Month", two adverts (including my main advertising banner), the top ten links from the directory (based on clicks) and obviously the directory itself.

All of these things are pulled by a MySQL db. I just ran through the script and found that it has around 15 db calls in total. Is this far too many or does it really matter a great deal?

I'm not expecting hundereds of thousands of users to be using the page at the same time but I would like it to be able to handle around 200 simultaneous hits (or somewhere in that region).

Exactly how many is too many?

Thanks very much for your help in advance,

[petesmc]

Hi,

When I was creating a site for me i was told from somebody (Freddydoesphp) that you should keep to a minimum.

He also recommended to me that around 2 queries is a max you should use really unless you want to create slower loading pages and a higher server load so I stuck to this.

I think you could limit this down to 3 queries at least.

Peter

[freakysid]

The other thing to ask yourself is how dynamic are the this month and last month's "site of the month"? They sound fairly static to me (if they only change each month). Same with the banner links - how often do these change. If you can manually update these through an include file then that's going to save a couple of database hits.

[KMxRetro]

OK,
I was thinking of putting the sites of the month and advert into an include but the ad is served from my ad software so that I can monitor the stats, this could be put into an include, I know, but I was led to believe that includes were more server processor heavy than DB calls. Is this the case?

For the directory, I obviously have categories and sub categories. Currently, the 4 main categories on display are queried from the DB (one query) and put into an array, then, the 3 subcategories of each main category are queried (1 x 4 main categories = 4 queries) and output to the screen.

Is there anyway that I could pull the 4 main categories and 3 subcategories (for each main one) into an array and output it?

Currently, my categories table has a "CatID" which is the unique category ID for each category and a "ParentCat" column which indicates which subcategory belongs where.

ie:-

Main Category A (CatID = 1, ParentCat = 0)
SubCategory of A (CatID = 2, ParentCat = 1)
SubCategory of A (CatID = 3, ParentCat = 1)
SubCategory of SubCategory of A (CatID = 4, ParentCat = 3)

...so on and soforth.

Any tips?

[arpith]

Hello,

The directory I developed simply updates all link pages once every week. Its pointless querying the database for links every time a user accesses a category page, since the link info is not really going to change that often.

The static HTML also is a plus point when it comes to search engines.

My table of categories is similar to yours:

Parent ID Category ID (key) Sort_Ident
0         1         1
1         2         2
0         3         3


Basically when I retrieve the categories, I go:
SELECT parent, category FROM category ORDER BY sort_ident ASC

That should retrieve everything, and in the correct order too.

When I add a new category, the "sort_ident" column is re-calculated to reflect the changes.

Example:

Parent ID Category ID (key) Sort_Ident
0         1         1
1         2         2

1         4         3
^ new entry

0         3         4


Hope that helps.

[padders]

it is defenitely a good plan to reduce the number of sql calls but so you don't worry, vbulletin uses about 15 sql calls on forumdisplay

[tubedogg]

Yes but vBulletin is also pulling a lot more info than just a URL and a site title...

Not to minimize your project or anything, just putting it in perspective.

[kentaurus]

Depends on your machine, but I would recommend not worrying a lot about the number of queries if you are minimizing them the most you can. 5 queries should be a good number to pull a lot of info in a front page.

If you check Vbulletin it makes a lot of queries per page, and the loading time isn't bad.

[nguip]

Hmmm...

This is interesting. So, basically we would be worrying about how many sql query in one page is it ?

I see vbulletin (sitepoint) sometime show the msg:-

The server is busy now. Please try again later.

Is this the problem caused by too many sql query in one page ?

[tubedogg]

One of the features of vBulletin is that it allows the admin to set a limit at which vBulletin will start turning away visitors, to keep a server from overloading. This also may be the server itself turning away visitors.

But in any case, no it is not caused by too many SQL queries on one page.

[padders]

Originally posted by tubedogg
One of the features of vBulletin is that it allows the admin to set a limit at which vBulletin will start turning away visitors, to keep a server from overloading. This also may be the server itself turning away visitors.

But in any case, no it is not caused by too many SQL queries on one page.

well.... it is when the server load reaches a certain amount, extra SQL queries create extra server load so it is part of the process. As sitepointforums are on a dedicated server (i think) the only thing causing server loads really are going to be httpd process, mysql processes and perhaps a bit of email sending and ad scripts running so yes extra mysql calls are going to be a large constituant of why people get turned away.

[mmj]

I was told that vBulletin can handle a lot more simultaneous users than it does at sitepointforums, I'm not sure but way over 4 times the traffic, and SPF admins have imposed a conservative limit, to be on the safe side.

[tubedogg]

It can. It can handle 400 or 500 users pretty comfortably, depending on the server and how well it's optimized, and whether you're using a dedicated DB server and a separate server for the web server.

[tubedogg]

Originally posted by padders
so yes extra mysql calls are going to be a large constituant of why people get turned away.

Yes that is true. But the question was "Is this the problem caused by too many sql query in one page?" and I would say the answer to that is no. As long as the number of calls doesn't get too exorbitant, you're going to have to be turning people away regardless if there are 10 calls on a page or 20, if the server load gets too high.

[James]

Originally posted by mmj
I was told that vBulletin can handle a lot more simultaneous users than it does at sitepointforums, I'm not sure but way over 4 times the traffic, and SPF admins have imposed a conservative limit, to be on the safe side.

vB can handle it but this particular server can't - if vB was placed on the right server then yes 400 concurrent users shouldn't give it a major problem.