The problem is in the very long execution of php, a JSON object is created in the file only, and it takes 30 seconds for a table of 7000 rows. Tell me what's wrong with the query creation format? I attach screenshots of mysql and JSON output

<?php header("Content-type: text/json"); $con = mysql_connect("localhost","root","root"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("test", $con); $data = mysql_query("SELECT * FROM sensor") or die ("Connection error"); while($row = mysql_fetch_array($data)) { $temp[]= array((int)strtotime($row['time']) * 1000,(float)$row['value']); $temp1[]= array((int)strtotime($row['time']) * 1000,(float)$row['value1']); $temp2[]= array((int)strtotime($row['time']) * 1000,(int)$row['value2']); $temp0= array($temp,$temp1,$temp2); } echo json_encode($temp0); mysql_close($con); ?>

enter image description here enter image description here

  • in the loop, you overwrite $temp0 over and over again - perhaps it eats memory and time. The result at the output suits you? - Sergiks
  • And it is worth trying to profile. There is a feeling that json_encode can eat a lot of CPU time for large data structures - Dmitriy Simushev
  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

2 answers 2

Try this:

 <?php $con = mysql_connect("localhost","root","root"); if (!$con) die('Could not connect: ' . mysql_error()); mysql_select_db("test", $con); $data = mysql_query("SELECT 1000 * UNIX_TIMESTAMP(`time`) AS ts, value, value1, value2 FROM sensor") or die ("Query error"); header("Content-type: text/json"); $V0 = $V1 = $V2 = array(); while($row = mysql_fetch_array($data)) { $V0[]= array( $row['ts'], (float) $row['value']); $V1[]= array( $row['ts'], (float) $row['value1']); $V2[]= array( $row['ts'], (int) $row['value2']); } mysql_close($con); echo json_encode( array( $V0, $V1, $V2), JSON_NUMERIC_CHECK);

If everything is just as slow, then, as suggested by @Dmitry_Simushev, it probably slows down the coding in JSON, and then you can try to circumvent it by directly collecting the text string.

The second option - you can try to collect JSON "manually":

 <?php $con = mysql_connect("localhost","root","root"); if (!$con) die('Could not connect: ' . mysql_error()); mysql_select_db("test", $con); $data = mysql_query("SELECT 1000 * UNIX_TIMESTAMP(`time`) AS ts, value, value1, value2 FROM sensor") or die ("Query error"); $V0 = $V1 = $V2 = ''; while($row = mysql_fetch_array($data, MYSQL_ASSOC)) { $V0 .= sprintf( '[%d,%f],', $row['ts'], $row['value']); $V1 .= sprintf( '[%d,%f],', $row['ts'], $row['value1']); $V2 .= sprintf( '[%d,%d],', $row['ts'], $row['value2']); } mysql_close($con); header("Content-type: text/json"); echo printf( '[%s,%s,%s]', rtrim($V0,','), rtrim($V1,','), rtrim($V2,','));
  • one
    I am extremely happy, thank you very much to you and Dmitry_Simushev for your advice. Everything works fine, the first way in 40ms, and the second in 20ms. - D. Sakulin

Here it is necessary to perform all the request. Because you get the data and cycle through EVERY line. Because There are a lot of lines, and the script execution time is big.

  • And it is possible in more detail, what exactly do you offer? - D. Sakulin
  • Is it possible to perform a trace. actions in the request? $temp[]= array((int)strtotime($row['time']) * 1000,(float)$row['value']); $temp1[]= array((int)strtotime($row['time']) * 1000,(float)$row['value1']); $temp2[]= array((int)strtotime($row['time']) * 1000,(int)$row['value2']); $temp0= array($temp,$temp1,$temp2); - GONG