MySQL Database Optimization Test Relational vs Serialized (Data As Strings)

General Discussion
1

I am trying to decide whether to store array data in MySQL as a serialized string or as a relational table.

So here is the test:
I will make 2 tables.
One will have the serialized data
The other one will relate to the table with the array data in rows.
Each array will hold 17 items of data

Here is the setup.

Creating the tables:
The serialized data table:

-- Table to store food lists users create
CREATE TABLE `user_food_lists` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `UID` varchar(100) COMMENT 'User ID from AWS Cognito',
  `food_list_type` varchar(300) NOT NULL COMMENT 'pantry, breakfast, recipes, lunch, dinner, snack, smoothies, etc...',
  `food_list_title` varchar(300) NULL COMMENT 'title of food list',
  `selectedFoods` text NULL COMMENT 'stringified array of selected foods',
  `selectedServings` text NULL COMMENT 'stringified array of selected quantities',
  `qty` text NULL COMMENT 'stringified array of selected servings',
  `date_created` datetime NOT NULL,
  `last_modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Creating the relational table with a row for each value in the array:

-- Table to store foods from food lists, if you go relational
CREATE TABLE `user_foods` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `selectedFood` text NULL COMMENT 'single food id',
  `selectedServing` text NULL COMMENT 'single serving',
  `qty` text NULL COMMENT 'single qty',
  `user_food_lists_id` INT,
  FOREIGN KEY(`user_food_lists_id`) REFERENCES user_food_lists(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Inserting 300,000 rows into the table with serialized (string) data
PHP Code

<?php
/*Connections string*/

$sql = 'INSERT INTO user_food_lists (
        `UID`, 
        `food_list_type`, 
        `food_list_title`,
        `selectedFoods`,
        `selectedServings`,
        `qty`,
        `date_created`
          ) 
        VALUES (
        "Test User", 
        "Breakfast", 
        "Test List",
        "171287-169926-170457-170383-170562-168389-168482-169251-169986-175139-173946-171705-169249-168462-168431-171140-169124-174831",
        "wt1-wt1-oz-oz-wt9-oz-100g-oz-oz-wt4-wt1-100g-wt1-wt1-wt1-oz-100g-wt1",
        "1-1-4-4-1-4-2-4-4-1-1-0.75-1-1-1-9-3.5-1",
        "2021-05-04"
        )';


$i=1;
while ($i < 300000)
{
 $rows = mysqli_query($db,$sql);
$i++;
}

Inserting the array data as rows PHP code

<?php
/* Connection String*/
//Arrays

$food_id_array=[171287,169926,170457,170383,170562,168389,168482,169251,169986,175139,173946,171705,169249,168462,168431,171140,169124,174831];

$serv_array=["wt1","wt1","oz","oz","wt9","oz","100g","oz","oz","wt4","wt1","100g","wt1","wt1","wt1","oz","100g","wt1"];

$qty_array=[1,1,4,4,1,4,2,4,4,1,1,0.75,1,1,1,9,3.5,1];

$i=1;

while ($i < 300000)

{

$j=0;

foreach ($food_id_array as $food_id)

{

$sql="INSERT INTO user_foods (

`selectedFood`,

`selectedServing`,

`qty`,

`user_food_lists_id`

) VALUES (

$food_id,

'$serv_array[$j]',

$qty_array[$j],

$i

)";

$rows = mysqli_query($db,$sql);

$j++;

}

$i++;

}

?>

The table with the string arrays has 300,000 rows.

The relational table has 5,399,982 rows.

Next will come query tests!
Running this code to query the strings from the single table and explode them to an array and echo back the first value is as such:

<?php
/*First Test: PHP query of the string data which gets changed (Exploded) to an array */

/*Connection String*/

$sql = "SELECT 
        `UID`, `food_list_type`, `selectedFoods`,`selectedServings`, `qty`
        FROM `user_food_lists`
        WHERE id=150000;
        ";

$rows = mysqli_query($db,$sql);

$row = mysqli_fetch_array($rows,MYSQLI_ASSOC); 

$foods=explode("-",$row["selectedFoods"]);
$serv=explode("-",$row["selectedServings"]);
$qty=explode("-",$row["qty"]);

echo $row['UID'];
echo $foods[0];
echo $serv[0];
echo $qty[0];

?>

Using the time function in Linux command line this PHP file takes around 0.061s to run on average.
time php sql-perf-testing.php
Result

real	0m0.061s
user	0m0.030s
sys	0m0.029s

Next for the join.

<?php 
/*Connection String */
$sql = "SELECT 
        `user_food_lists`.`UID`,
        `user_food_lists`.`food_list_type`,
        `user_foods`.`selectedFood`,
        `user_foods`.`selectedServing`,
        `user_foods`.`qty`
        FROM `user_food_lists`
        JOIN `user_foods`
        ON `user_foods`.`user_food_lists_id`=`user_food_lists`.`id`
        WHERE `user_food_lists`.`id`=150000;
        ";
$rows = mysqli_query($db,$sql);

$row = mysqli_fetch_array($rows,MYSQLI_ASSOC); 

$foods=explode("-",$row["selectedFood"]);
$serv=explode("-",$row["selectedServing"]);
$qty=explode("-",$row["qty"]);

echo $row['UID'];
echo $foods[0];
echo $serv[0];
echo $qty[0];

?>

Result:
It takes basically the same time!

real	0m0.061s
user	0m0.036s
sys	0m0.024s
2

Running this select 5 records at a time. It is all the same speed! :slight_smile:

1 Like
3

I have a feeling that avoiding IDs as strings will protect you from other problems down the road. :slight_smile:

If there’s a food ID here, should it be a foreign key?

`selectedFood` text NULL COMMENT 'single food id',

If this is choice from a limited number of options, would it be easier as an enum or related table?

`selectedServing` text NULL COMMENT 'single serving',
1 Like
4

Hmmm yeah, in that table, I could make both those values foreign keys.
I may also make the “meal type” a relational table too. I see the value in coding text values as numbers or set values.

1 Like