Query Loads Faster in phpMyAmin than on site

PHP
1

Hello,

I was wondering if there is anybody here that could help me understand a problem I’m dealing with.
Im trying to optimize my pages because some of them take forever to load (like 14-30 seconds).

I have been running the queries in phpMyAdmin and using EXPLAIN, and the queries are fine and load in about 0.03 to 0.001 seconds in phpMyAdmin, but when using PHP to call the query and display the results in a table using a while loop, it takes forever for the page to be displayed.

I usually use

$sql = “SELECT … WHERE field = ‘whatever’”;
$result = mysql_query($sql);

while(still have results)
{
print
}

Is there something i am doing that is dramatically slowing down the calls?

Thank You

2

How many results does the query return?

3

It sounds as if something else on the page is causing the bottleneck, are you collecting something else on that script, from an external API, say?

To double check your sql is being constructed correctly try these things:

echo $sql ; temporarily onto the page, copy that and paste it into PMA.

Check your mysql log file when you test manually, and test on the live webpage.

4

The SQL is returning anywhere from 100-200 rows.
All the information is coming from the database on the page, comes earlier from an API but not per-view.

I have done the echo $sql and thats how I verify that the time it takes in PMA is much faster than on the site.

–

I have pretty much removed all bottlenecks (besides the server i guess) as I have setup a blank white page that all it does it produces a dull table with the results printed out in it. Still much slower.

5

If all those rows are displayed in a single HTML table, IIRC some browsers need to wait until the entire table has been downloaded before they can contemplate displaying anything to the user…

I suppose you could display the output as <br />s inside <p></p>s instead to test this.

But TBH that does not sound like the problem, are you talking about a public facing url that we could test for you?

6

You might need to use a profiling tool such as xdebug to see where the time is actually going.
Also if your query cache is enabled and your script is slow each time (back to back executions) it’d seem the query isn’t the problem.

Are other queries from PHP also slow?