Php going to mysql help

This is a speed issue, I have been working with php for a few years now and am quite confident with writing code. However I have recently been trying to optimize pages to get great load times and be able to cope with heavy loads. My problem is that when it comes to the raw php calculations etc it is incredibly fast (can execute a mildly complex page in < 0.05 seconds) and when I run a mysql statement through phpMyAdmin (that I am using in my page) it takes about 0.05-0.1 seconds. Now that should add up to about .03 seconds with simple php code and 2 mysql_query statements. But it is quite regularly north of 1.5 seconds! so my question is where does all the time get lost to and how can I make it more optimized? (please no-one say get better hosting) thanks in advance

lol in all honesty you would need a better host :smiley: But to do everything you can, then you need to make sure output buffering is enabled, and use it. Try using a php cache, such as "Alternative PHP Cache ". Next you also need to properly index your database, You have to imagine that any time that is used is all used in communication between the server running the php script and mysql. So if you made your php script easy to read for mysql (optimized the queries), then the rest of the time lost would be in the time spent running the php (so need cache) and translation time(properly index the tables of mysql, and do some mysql tunning)

most of the servers I have ran across have been improperly tuned, different sites run scripts differently, so it goes without reason that the mysql needs to be tuned differently for each site!! There is no true one size fits all setting for mysql.
a couple things to read up on :
table_cache
thread_cache
query_cache_size
key_buffer_size
and read up on the difference between myisam and innodb!

Hope this helps get you started

Most likely causes:

[ul][li]Images/Animations on the page[/li]
[li]Your internet[/li]
[li]Hosting issue[/li][/ul]

As mentioned by Plintu, you can of course look into caching mysql requests, but if the query itself isn’t slow this isn’t going to cut off the seconds you need. If the slowness isn’t caused by your internet, images/animations etc on the page or your host, then it may be PHP dealing with the mysql (if you didn’t included that PHP execution in your timings) as your looping through results may be slowing things down.

Over a second for what you’ve described doesn’t sound right at all, I would be surprised if it wasn’t caused by one of the three points above to be honest.

it sounds as though Ben is not a novice and is a bit more in depth into this. From what Ben mentions about measuring raw php calculations I am gathering he is using a script similar to what I am about to list below, this would eliminate images, and internet, but not so much hosting issues, hosting can be the major key(which brings the question did they setup mysql correctly to handle Bens site.
I am assuming you are already using something similar to this Ben but
If not you can use a script similar to this:
at the start of the script put:

[php]$start = microtime(true);
[/php]

Then at the end of the script put this:

[php]$end = microtime(true);
$time= round($end - $start, 3) . " thousandths of a second";
echo $time;
[/php]
This will measure pure php computation time. Next if you want to find which queries take the longest, place the start(first part) just before each query and the end(second part) just after each query.
This will measure how long the individual queries are taking. If you have “join” queries and a bunch of "SELECT * FROM table" queries these will take the longest rather than "SELECT field FROM table.
I had to do some tuning on the mysql for a couple penny auction sites which are very intensive as in every second and half second, for every user that is online, a the database is getting queried. I found that the big bottle ups were always in join’s and the big one was correctly setting the query_cache.

Now on a shared server, then the big issue would be hosting, just as Smokey mentioned. when you are on a shared server you have no idea how many people are sharing that server with you and what type of site they are running and how much resources they are using up! So hosting is a key factor on shared servers.

Sponsor our Newsletter | Privacy Policy | Terms of Service