CODEWITHSUNDEEP
phpmysqlarraysperformancefetchall
Mason Ticehurst
Mason Ticehurst

Reputation: 474

PHP/SQL - Query to Array Load Time

for a little while now I've had this problem that causes my PHP to take upwards of 20 seconds to load (even on the host machine containing the Apache + SQL Server).

Google Chrome Network Report

enter image description here

I've done a bit of digging around and it seems like the following lines which convert all the data into an array is the culprit.

 $sql = "SELECT  `date`, `type`, `author`, `commit_file`,
                 `description`, `commit_num`
    FROM  ".$tablename."
    ORDER BY  date, commit_num, type DESC";

define('MYSQL_BOTH',MYSQLI_BOTH);
define('MYSQL_NUM',MYSQLI_NUM);
define('MYSQL_ASSOC',MYSQLI_ASSOC);

define('personaname', 'personaname');
define('players', 'players');
define('response', 'response');
define('avatarmedium', 'avatarmedium');

$result = $conn->query($sql);

$retrievedData = $result->fetch_all(MYSQLI_ASSOC);

The last like (fetch_all specifically) cause the page to take signifigantly long to load. I've tried running the query directly in SQL and it only takes 0.078 seconds (Only 55 entries so that makes sense).

Is there anyway to optimize this so it doesn't halt the page for so long?

Thanks

Upvotes: 0

Views: 214

Answers (1)

Caleb
Caleb

Reputation: 141

If you are running into performance issues it would be best to isolate exactly what the problem is using debugging or a logging mechanism to trace down exactly what is taking a long time.

A couple inefficiencies that immediately stand out are:

  • Your query is sorting in two different directions which can cause full table scans if not indexed correctly.
  • It is generally bad practice to not have a limit on your queries. This table could get really big really fast causing resource allocation issues.
  • Finally, if you are running into something taking this long it is likely that your memory is full and is being swapped to disk. ini_set('memory_limit','16M');

Upvotes: 1

Related Questions