| 7:10 pm on Mar 4, 2009 (gmt 0)|
I have found memory_get_usage() [us3.php.net] and memory_get_peak_usage() [us3.php.net] helpful in checking your script's memory optimization.
Another way is to add a start time variable/array at the top of the script and drop markers throughout the script, noting time from start time and presenting a summary at script's end.
| 7:42 pm on Mar 4, 2009 (gmt 0)|
I dropped some time markers that displayed the seconds, and the biggest hold up is during that select statement I was using earlier...so I guess that is the problem. Back to the drawing board!
| 7:52 pm on Mar 4, 2009 (gmt 0)|
Look into PHP profiling applications. Use EXPLAIN on mysql statements to see how they are searching and if they are using the proper indexes. I have seen SQL queries not use the correct index. By forcing the correct index, you can sometimes prevent expensive row by row searches.
| 8:18 pm on Mar 17, 2009 (gmt 0)|
When I use EXPLAIN, I get this:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE _images ALL NULL NULL NULL NULL 290 Using temporary; Using filesort
1 SIMPLE _ads eq_ref PRIMARY PRIMARY 4 hometow_fas._images.adId 1 Using where
1 SIMPLE _zipcodes ALL PRIMARY NULL NULL NULL 32236 Using where
But I have no idea what that stuff means.
| 9:50 am on Mar 18, 2009 (gmt 0)|
This breaks it down for you:
Optimizing Queries with EXPLAIN [dev.mysql.com]
| 1:39 pm on Mar 18, 2009 (gmt 0)|
I still can't see how that explains why the query runs in 0.16 seconds through straight SQL, but takes 14.0 seconds to run through my PHP script. None of my other queries are like this.
| 1:43 pm on Mar 18, 2009 (gmt 0)|
Are you certain it is the query that is taking so long to run within the PHP script? Or is it something in the underlying PHP code that is causing your performance issue?
| 1:55 pm on Mar 18, 2009 (gmt 0)|
I set up little time markers before and after the query. So, the time markers list the time immediately before and immediately after the query is performed via PHP. The difference between the markers is always about 14 seconds.
| 3:36 pm on Mar 18, 2009 (gmt 0)|
OK, so for certain, it is this one query itself, not your connection or any other query. Because if your connection through the PHP script is other than localhost you may discover a latency issue there.
|the query runs in 0.16 seconds through straight SQL |
What exactly do you mean by this statement, "straight SQL"?
| 3:44 pm on Mar 18, 2009 (gmt 0)|
It does connect through another connection besides localhost, but so do all of my other queries.
By 'straight through SQL', I mean that there is no lag when I run the query through phpMyAdmin.
| 3:55 pm on Mar 18, 2009 (gmt 0)|
OK then, everything is running through the same connection, and through PHP scripts. What type of connection are you making?
| 4:07 pm on Mar 18, 2009 (gmt 0)|
I'm not sure what to call it, but I use mysql_pconnect().
| 4:56 pm on Mar 18, 2009 (gmt 0)|
That's a persistent connection. Try mysql_connect instead.
| 6:02 pm on Mar 18, 2009 (gmt 0)|
Hmmm, I tried that. Still no improvement. I'm banging my head against the wall here.
| 12:40 am on Mar 23, 2009 (gmt 0)|
How much data is in this particular table, i found with a user tracking system i setup that when it became over populated with entries that the home page to the cms slowed in loading as it took a few seconds to pull the user tracked data for display.
| 5:09 am on Mar 23, 2009 (gmt 0)|
If you are using mysql_pconnect() in all your scripts, then there's probably a huge buildup of 'Sleeping' connections from previous sessions. I've found that mysql_pconnect() usually generates a new connection (instead of using the old one) when different IPs/Ports call a script.
You should consider flushing all the connections and using mysql_connect() in all scripts.
| 11:19 am on Mar 23, 2009 (gmt 0)|
|I've found that mysql_pconnect() usually generates a new connection (instead of using the old one) when different IPs/Ports call a script. |
Yep, that's the nature of HTTP, it is a stateless protocol.