0

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

Rick James
  • 135,179
  • 13
  • 127
  • 222
kpjVideo
  • 413
  • 2
  • 6
  • 21

1 Answers1

1

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');
Calebj
  • 142
  • 9
  • Ah I see, I'm not sure about limiting it as the page requires all the data inside the table. I've also tried setting the memory limits without luck :(. What would you recommend for keeping the query in "the same direction" so that the data is still usable as an array later on? The first point sounds the most possible – kpjVideo Aug 04 '17 at 00:27
  • Only 55 rows, so most of the items in this Answer cannot explain 16 seconds for the query. – Rick James Aug 14 '17 at 20:00
  • Mixing ASC and DESC on `ORDER BY` only prevents the use of an `INDEX`; it does not otherwise slow it down. – Rick James Aug 14 '17 at 20:02