PHP MySQL to JSON performance optimization

Question

I'm collecting sensor data, storing them in a MySQL DB and loading the data as a JSON object with a PHP application in Highcharts for graphical representation.

Now I have around 200k entries in my table. I recognised that it takes about 3.5 seconds to generate the JSON. I guess with more entries I will become too slow.

Is there is a way to optimize the processing of the JSON?

Following the PHP code that generates the JSON:

$sql = "SELECT time, value1, value2 from ". $feedID;
$result = mysqli_query($con, $sql);

while ($row = mysqli_fetch_array($result)) {
    if ( $row[2] == null ) {
        $response[data][] = array( (strtotime($row['time']))*1000, (float) $row[1]);
    } else {
        $response[data][] = array( (strtotime($row['time']))*1000, (float) $row[1], (float) $row[2]);
    }
}

 $return = json_encode($response);
 header('Content-type: text/json');  
 header('Content-Length: '.strlen($return)."\r\n"); 
 header('Accept-Ranges: bytes'."\r\n");
 echo $return;

the only way to optimalisation I see is to cache the json file — Raymond Nijland
– Raymond Nijland, Commented May 10, 2017 at 15:04
use memcached/Redis and serve cached file, or generate json_encoded string on backend and store it in database/disk while refreshing it with a cronjob every 5-10 minutes if needed. — Dimi
– Dimi, Commented May 10, 2017 at 15:06
I'm not sure but, if data in $response[data][] is not a constant, using "data" with quotes could be faster, because maybe PHP are writing to a log file the error for using a constant as a string because is not defined. Then, you are casting columns to float in (float) $row[1], I think that if the column has the desired value you don't need to cast because you are then encoding to json, which is basically a string. I'm not sure if this would be a valid response, so I left it in a comment. — Alex Ruiz
– Alex Ruiz, Commented May 10, 2017 at 15:07
Thank you Alex. $response[data][] was my bad. Fixed but without performance improvement. Regarding the float cast, If I don't cast the value as float will be in the json with brackets. Like "235". Other wise its just 235. — Rene
– Rene, Commented May 10, 2017 at 15:18
I doubt that highcharts would really need all 200k measurements. You can always aggregate your data to some sensible threshold - a minute, or a hour, or a day. So you can greatly reduce the size of the array. — Your Common Sense
– Your Common Sense, Commented May 10, 2017 at 15:38

Nemoden · Accepted Answer · 2017-05-11 12:52:00Z

2

I guess you can optimize the query first:

SELECT unix_timestamp(time)*1000 as time, value1, value2 from ". $feedID." 
WHERE value2 is not null";

Next, you probably don't need so much data on a chart anyways. Try to reduce amount on data shown on a highchart at once.

For example, google maps don't load whole world when you are looking how to get to a suburb in your city. Rendering 200k rows on a chart would be extremely slow so even if you will tweak back-end, front-end will stuck anyways. For a whole period of time you might opt to render data for days/months, for example, e.g.:

SELECT
   month(`time`) as mo,
   year(`time`) as `year`,
   avg(value1) as value1,
   avg(value2) as value2
FROM table
WHERE
    value2 is not null
GROUP by year(`time`), month(`time`)

Given the query above you might want to alter the table you have so you wouldn't need to calculate month and year, but you'd rather store it on writes. When you write to database, you might also want to have aggregated data in another table/storage so you can access it easily.

So, common answer would be: try to reduce amount of data. I can't even imagine a real world case when 200k records need to be rendered on a chart.

edited May 11, 2017 at 12:52

answered May 11, 2017 at 11:57

Nemoden

9,0866 gold badges45 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rene Over a year ago

Hi Nemoden, your proposed query optimization reduces the time to retrieve the table from 1.8s to 0.5. That is a huge improvement. Thank you! Longterm I need to finde a way to reduce the data amount.

Nemoden Over a year ago

Glad to help you, @Rene

symcbean · Accepted Answer · 2017-05-11 12:13:46Z

1

it takes about 3.5 seconds to generate the JSON

Rather implies that json_encode($response); takes 3.5 seconds - is that really the case? There's no timing in your script. If you mean that it takes 3.5 seconds for the data to get to the client, then you need to seperate out:

the time to retrieve the data from the database and build the aray
the time for json_encode()
the time to output the data
the time for the data to traverse the network
the time for the data to be parsed at the client

Assuming the cost is in json_encode() (or in building the very large array - which is going to cause you memory problems) then you should incrementally build the output....

header('Content-type: text/json');
$sql = "SELECT time, value1, value2 from ". $feedID;
$result = mysqli_query($con, $sql);

print "{ "data" : ";
$join="[\n";
while ($row = mysqli_fetch_assoc($result)) {
    print $join;
    $response = array( 
        (strtotime($row['time']))*1000
        , (float) $row['value1']
        );
    if ($row['value2']!==null) {
        $response[]=(float) $row['value2'];
    }
    print json_encode($response);
    $join=",\n";
}   
print "]\n}\n";

I have around 200k entries in my table

How many pixels do you have on your monitor? What kind of chart are you plotting?

Your biggest win will come from handling less data.

BTW: What do you think header('Accept-Ranges: bytes'."\r\n"); is doing? Your code does not accept ranges.

answered May 11, 2017 at 12:13

symcbean

48.4k6 gold badges63 silver badges99 bronze badges

2 Comments

Rene Over a year ago

Thank you symcbean for you replay. Following the timings:

Rene Over a year ago

Retrieve table 1.8s, building json 0.23s, output 0.007s, total is about 2sec. today. Data transfer is 500ms (gzip). I'm using Highcharts Stock link to compare for example temperature and humidity . Data exploration is really nice and I can zoom in to an intervall of 30sec.

Plamen Nikolov · Accepted Answer · 2017-05-10 15:13:54Z

-1

I can suggest the following options:

You can skip generating JSON by storing your data directly into JSON format using MySQL 5.7 https://dev.mysql.com/doc/refman/5.7/en/json.html
Try paginating the query and make async loading of the data with query LIMIT and OFFSET https://www.w3schools.com/php/php_mysql_select_limit.asp
Try experimentation with optimizing json_encode with additional flags, such as JSON_UNESCAPED_UNICODE (available in PHP 5.4+) - json_encode(array(), JSON_UNESCAPED_UNICODE) - this will leave UTF-8 characters untouched, but it depends on your data and you will get a slight performance increase. See all json_encode options http://php.net/manual/en/function.json-encode.php

answered May 10, 2017 at 15:13

Plamen Nikolov

3,0651 gold badge16 silver badges29 bronze badges

2 Comments

Your Common Sense Over a year ago

first two are apparently irrelevant.

Rene Over a year ago

I tryed the 'JSON_UNESCAPED_UNICODE' flag but doesn't have a measurable effect.

Collectives™ on Stack Overflow

PHP MySQL to JSON performance optimization

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related