author: Krasimir Tsonev

Hi there, I'm . Senior front-end engineer with over 13 years of experience. I write, speak and occasionally code stuff.

MySQL vs JSON file data storing

I'm not exactly a performance perfectionist, but currently I'm working on a tool which will be used in a large system. It will deliver texts to a multi-language environment and there will be several sites/apps which will performe big amount of request to it. It is the right moment for me to choose how to speed up my code and the work of the tool overall.
The texts are stored in MySQL database. The first option is to fetch the data directly from there and serve it whenever it is needed. Two problems with that - there will be a lot of records in the database and there will be a lot of requests every second. Naturally the tool offers export of all the information in one single JSON file. So I decided to get the data from there, instead of making mysql queries all the time. I was wondering which of these two approaches is faster and will fit in my needs. I created a dummy database and fill it with more then 20000 records. The following script did the job:
function addUsers($numOfUsers) {        mysql_connect("localhost", "root", "");    mysql_select_db("krasimir_benchmark");        // adding data    $firstNames = array("Curabitur", "Fusce", "Aliquam", "Proin", "Mathew", "Morbi", "Nullam");    $lastNames = array("Proin", "Nunc", "Fusce", "Cras", "Donec", "Morbi", "Nam");    $job = array("back-end developer", "administrator", "front-end developer", "PM");    $city = array("New York", "Paris", "London", "Sofia");    $notes = "... some very long text here ...";    $date = date("Y-m-d H:i:s");    for($i=0; $i<$numOfUsers; $i++) {        mysql_query("            INSERT INTO users (firstName, lastName, job, city, notes, registerDate) VALUES (            '".$firstNames[rand(0, count($firstNames)-1)]."',              '".$lastNames[rand(0, count($lastNames)-1)]."',              '".$job[rand(0, count($job)-1)]."',              '".$city[rand(0, count($city)-1)]."',              '".$notes."',              '".$date."'            );        ");    }}
Simple method which exports the data to a file in JSON format.
function exportToJson() {        mysql_connect("localhost", "root", "");    mysql_select_db("krasimir_benchmark");              $res = mysql_query("SELECT * FROM users ORDER BY id");    $records = array();    while($obj = mysql_fetch_object($res)) {        $records []= $obj;    }    file_put_contents("data.json", json_encode($records));    }
The file became really big - around 40MB. I'm sure that I'll never reach 40MB with the tool. However, it is good for the testing purposes. Here are the two methods which test the fetching from MySQL database and from the JSON file.
function testMySQL() {    mysql_connect("localhost", "root", "");    mysql_select_db("krasimir_benchmark");    $res = mysql_query("SELECT * FROM users ORDER BY id");    echo "All records: ".mysql_num_rows($res);    while($obj = mysql_fetch_object($res)) {        printRecord($obj);    }}function testJSON() {    $records = json_decode(file_get_contents("data.json"));    $numOfRecords = count($records);    echo "All records: ".$numOfRecords;    foreach($records as $record) {        printRecord($record);    }}
printRecord is a function which just outputs the data.The results of the tests are: MySQL reading - 2.97sJSON file reading - 3.16sHowever, the above results are actually based on only one request from one user. I decided to use the Apache benchmark tool to simulate multiple requests. And it looks like MySQL approach still wins. The json.php takes less time but some of the requests fail due to Out of memory problem of PHP.
// MySQLab -n 30 -c 30 http://localhost/mqsql.phpConcurrency Level:      30Time taken for tests:   30.518 secondsComplete requests:      30Failed requests:        0Write errors:           0Total transferred:      40092780 bytesHTML transferred:       40087800 bytesRequests per second:    0.98 [#/sec] (mean)Time per request:       30517.578 [ms] (mean)Time per request:       1017.253 [ms] (mean, across all concurrent requests)Transfer rate:          1282.97 [Kbytes/sec] receivedConnection Times (ms)              min  mean[+/-sd] median   maxConnect:        0    0   0.5      0       2Processing: 24383 28657 1727.5  29306   30510Waiting:     2110 15625 6618.0  18188   23729Total:      24383 28657 1727.3  29307   30510// JSONab -n 30 -c 30 http://localhost/json.phpConcurrency Level:      30Time taken for tests:   3.384 secondsComplete requests:      30Failed requests:        11Write errors:           0Total transferred:      2683460 bytesHTML transferred:       2677892 bytesRequests per second:    8.87 [#/sec] (mean)Time per request:       3383.789 [ms] (mean)Time per request:       112.793 [ms] (mean, across all concurrent requests)Transfer rate:          774.45 [Kbytes/sec] receivedConnection Times (ms)              min  mean[+/-sd] median   maxConnect:        0    0   0.4      0       1Processing:    52  857 767.5    877    3379Waiting:       52  821 652.2    877    2850Total:         53  857 767.5    877    3379
What you think? What are your tips and tricks for such kind of situations?