What's the maximum healthy number of calls to mysql_query?

[rain13]

Hi, I have written quite large software that is by it's functionality close to drupal or gallery 3 or other such software. Now I counted calls to mysql_query and it turned out that when I read article, it does 83 calls and when I view gallery it does 66 call to this function. I am wondering if it is ok if software is big and functional like some professional cms or should I be worried because of that great number of queries?

Edited July 2, 2015 by SoItBegins

[thescientist]

have you compared relative to those other projects? What's the overall load time for an article with your software vs that other software?

[justsomeguy]

That does sound like a lot of queries for some basic things, although if you're using a framework then they abstract things so much that it's kind of hard to get away from so many queries.

[rain13]

I figured that when I moved it from local host to real host, load time went from 0.1 sec to 1 sec for some reason although the mysql server is still located at localhost.

[Ingolme]

That is quite a lot of queries. Perhaps you need to redesign your software.

 

Look for instances where you're querying the same data twice and store the results in a variable instead.

 

Find places where you might be looping through the results of one query to perform another one and see if it can be resolved with a JOIN instead.

[Rob van der Lee]

I figured that when I moved it from local host to real host, load time went from 0.1 sec to 1 sec for some reason although the mysql server is still located at localhost.

 

I recognize myself in this. When i bought my first hosting pack at some hosting firm I experienced the same. The hosting was dirty cheap and super slow on itself but taught me that my code was very badly designed also. What do I suggest?

 

Check your code. Build in a lot of alerts or report it to some class if your using a proper framework. You need to know whats going on in your code in order for you to optimize it. I personally report back to a helper class with a message and time, this gives me the means to measure and know whats going on in that point of the loading time. When you see large gaps and huge numbers, then you know that's the area that needs work/optimization.

 

With a system like this you can keep improving and keep learning by finding what situations work in code and those who don't.

 

However, for queries all together you can also look at phpmyadmin for that. There is a tool that analyses the queries ( put EXPLAIN before your query ), you will need this when you query complex statements with joins and complex criteria. This will give you the numbers on your query so you can crunch them together to make adjustments from there.

 

I hope this helps you a little in finding what you need to improve on your code. When you trace it back to it's source, care to share? I'm intrigued now

Edited July 3, 2015 by Rob van der Lee