How many calls to mysql is ok

mysql max calls php

[eltreno]

Hi I'm building a php/mysql CMS and as most things including section names, chapter names and page name + gif names etc etc all sit in mysql on some pages their will be 8 to 10 calls to get the required info.

Most of the tables will be under 50 rows and indexed well. The biggest table will be the main content table which itself shouldn't be more then 200 rows. So as i also don't expect there to be a massive user base as well should this be ok, using 8 calls per page.

Is there somewhere i can go to see load statistics ie how many calls per page is good for what size a site is etc...

Or can someone give me an idea of if what i'm doing would be ok or not

Thanks
Trent

[jpjones]

8 calls per page isn't completely outrageous - though you may be able to shave a few calls by performing table joins or something?

Best practice would say that you should make as few calls to the database as necessary, as each call could potentially slow down your script. (Imagine the scenario where you have lots of visitors, and some of your DB calls are database updates. The tables would be locked whilst the update takes place, and this would slow down your script execution / page rendering).

As, however, you say you have only 200 rows, and a small user base, performance shouldn't be hit that bad.

You can get an idea of performance by timing your scripts.
Google for "php execution time" for some ideas.
Time your current code, modify it, and test again.

HTH,
JP

[eltreno]

Thanks
I'll test this and will re-think my normalisation a bit as well...can test the time difference now anyway on different ways of doing these calls :)

Cheers
Trent

[coopster]

Welcome to WebmasterWorld, eltreno.

Good advice from jpjones here. You can speed up your queries by using indexes on your MySQL tables as well.

[mincklerstraat]

Some very good remarks here. 8-10 isn't really so bad - there are lots of scripts out there that will do 5 times this many queries just for a single pageview. But cutting down your queries to the bare minimum is the way to go if the script's purpose isn't trivial.

You can also cut down on queries by doubling up info in some tables (against strict db normalization, but the mysql folks encourage this for web apps) as long as your admin logic takes account of this and is water-tight - can save you a tiny bit of speed. Once you're already getting info from a db row, just getting a few more fields of info is trivial. But what's even better is if you can get your system to use some kind of naming convention. Then, if there's a chapter called 'lonely goldfish', you always have this use lonely_goldfish.gif, etc, and when your user uploads the gif, you just rename it on the spot. Some kind of 1 to 1 correspondance between the info you already have, and the info that needs to be 'attached.'

[eltreno]

Thanks Thats good to hear

I didn't realise mysql recommend this in some cases, i'm going to go with my 8 calls at this point and see how it goes then look into some changes once she's up but am just personally against doubling content (as we all are) but I also think it shall be a good learning experiance to see how she runs and then make changes if I need

at this point my page is loading in between 0.00239491462708 and 0.00870299339294 seconds (not sure how that compares?), granted it's very lack of content and not too many hits on a dev server ;) but it's great to get this feedback

Thanks
Trent

[tomda]

I think you need to loop the content to be able to compare the time (at least 50 times). But I really don't know how to loop the content.

[mincklerstraat]

That does indeed provide good info in most cases, Tomda. You can loop with just a normal 'for' type loop:

for($i = 0; $i < 50; $i++){

at the top and:

}

at the bottom

[be2.php.net...]

[eltreno]

I'm going to take all my calls and php functions and remove all echo and plain html so nothing is written to the header then at the bottom recall php_self with a count parameter++ until it reaches 50 or 100 and also send the original loop time then get the last loop time. Then print them in the end.

I think this will give an idea or how long to call the page 50 times or so...

Any other ideas would be appreciated as this is just something i have thought of but have never done testing like this before!

Trent

[henry0]

Coopster might remember where is that post
A senior member posted a few months ago an excellent post on how measuring query speed
sorry cannot remember where the thread is

regards

Henry

[slade7]

This post made me curious, so I took a look at the two scripts I happened to have open in my editor... one has 17 calls and the other has 14. Some of the queries are 10+ lines long - joins, etc. I have 31 tables, the largest of which has around 50,000 rows. Pretty good traffic. All works fine.

I do find that strict normalization is not always the best route. Storing a little more in a table sometimes pays off in less complicated queries. I did feel kinda guilty about this till I read this post!