Skip to content

Instantly share code, notes, and snippets.

@bulton-fr

bulton-fr/bench_mysql_data_formats.md

Created November 21, 2015 15:51
Show Gist options
  • Save bulton-fr/67b854d35d1c08a775f8 to your computer and use it in GitHub Desktop.
Save bulton-fr/67b854d35d1c08a775f8 to your computer and use it in GitHub Desktop.
Download ZIP
Bench mysql 1 column vs multi columns
Raw
bench_mysql_data_formats.md 
mysql  Ver 14.14 Distrib 5.6.25, for debian-linux-gnu (x86_64) using  EditLine wrapper

CREATE TABLE bench_columns (
 id int(11) NOT NULL AUTO_INCREMENT,
 gender varchar(6) NOT NULL,
 title varchar(4) NOT NULL,
 first varchar(255) NOT NULL,
 last varchar(255) NOT NULL,
 street varchar(255) NOT NULL,
 city varchar(255) NOT NULL,
 state varchar(255) NOT NULL,
 zip tinyint(3) UNSIGNED NOT NULL,
 email varchar(255) NOT NULL,
 username varchar(255) NOT NULL,
 password varchar(255) NOT NULL,
 salt varchar(255) NOT NULL,
 md5 varchar(255) NOT NULL,
 sha1 varchar(255) NOT NULL,
 sha256 varchar(255) NOT NULL,
 register int(11) UNSIGNED NOT NULL,
 dob int(11) UNSIGNED NOT NULL,
 phone varchar(14) NOT NULL,
 cell varchar(14) NOT NULL,
 INSEE varchar(16) NOT NULL,
 picture_large varchar(255) NOT NULL,
 picture_medium varchar(255) NOT NULL,
 picture_thumbnail varchar(255) NOT NULL,
 
 PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE TABLE bench_text (
 id int(11) NOT NULL AUTO_INCREMENT,
 infos text NOT NULL,
 
 PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE TABLE bench_serialize (
 id int(11) NOT NULL AUTO_INCREMENT,
 infos text NOT NULL,
 
 PRIMARY KEY (id)
) ENGINE=InnoDB;

Call bench_mysql.php for generate sql requests.

Insert json format in text column : Add 500 lines

mysqlslap -p -c10 -i 50 -q 'INSERT INTO bench_text SET `infos`="{\"gender\":\"female\",\"name\":{\"title\":\"miss\",\"first\":\"albane\",\"last\":\"lefevre\"},\"location\":{\"street\":\"9643 rue louis-garrand\",\"city\":\"strasbourg\",\"state\":\"seine-et-marne\",\"zip\":78018},\"email\":\"[email protected]\",\"username\":\"brownpeacock729\",\"password\":\"dublin\",\"salt\":\"lbcWrItu\",\"md5\":\"8945dae9635a94c05e80ab110ba2e25e\",\"sha1\":\"4ca52032ec4560b4ebbc7de32fb1f12c520843c1\",\"sha256\":\"99ee6d1aa97acfa621c2648cfd6ec0938df2dccb5f954ec53684583cdf83db81\",\"registered\":1009850287,\"dob\":733573810,\"phone\":\"04-11-09-31-91\",\"cell\":\"06-41-88-62-02\",\"INSEE\":\"2930391197087 21\",\"picture\":{\"large\":\"https:\\/\\/randomuser.me\\/api\\/portraits\\/women\\/86.jpg\",\"medium\":\"https:\\/\\/randomuser.me\\/api\\/portraits\\/med\\/women\\/86.jpg\",\"thumbnail\":\"https:\\/\\/randomuser.me\\/api\\/portraits\\/thumb\\/women\\/86.jpg\"}}";'
Enter password:
Benchmark
        Average number of seconds to run all queries: 0.036 seconds
        Minimum number of seconds to run all queries: 0.021 seconds
        Maximum number of seconds to run all queries: 0.095 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

SHOW TABLE STATUS LIKE 'bench_text'\G
*************************** 1. row ***************************
           Name: bench_text
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 478
 Avg_row_length: 1028
    Data_length: 491520 #480 KB
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 501
    Create_time: 2015-11-21 15:50:52
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

Insert serialize format in text column : Add 500 lines

mysqlslap -p -c10 -i 50 -q 'INSERT INTO bench_serialize SET `infos`="O:8:\"stdClass\":16:{s:6:\"gender\";s:6:\"female\";s:4:\"name\";O:8:\"stdClass\":3:{s:5:\"title\";s:4:\"miss\";s:5:\"first\";s:6:\"albane\";s:4:\"last\";s:7:\"lefevre\";}s:8:\"location\";O:8:\"stdClass\":4:{s:6:\"street\";s:22:\"9643 rue louis-garrand\";s:4:\"city\";s:10:\"strasbourg\";s:5:\"state\";s:14:\"seine-et-marne\";s:3:\"zip\";i:78018;}s:5:\"email\";s:26:\"[email protected]\";s:8:\"username\";s:15:\"brownpeacock729\";s:8:\"password\";s:6:\"dublin\";s:4:\"salt\";s:8:\"lbcWrItu\";s:3:\"md5\";s:32:\"8945dae9635a94c05e80ab110ba2e25e\";s:4:\"sha1\";s:40:\"4ca52032ec4560b4ebbc7de32fb1f12c520843c1\";s:6:\"sha256\";s:64:\"99ee6d1aa97acfa621c2648cfd6ec0938df2dccb5f954ec53684583cdf83db81\";s:10:\"registered\";i:1009850287;s:3:\"dob\";i:733573810;s:5:\"phone\";s:14:\"04-11-09-31-91\";s:4:\"cell\";s:14:\"06-41-88-62-02\";s:5:\"INSEE\";s:16:\"2930391197087 21\";s:7:\"picture\";O:8:\"stdClass\":3:{s:5:\"large\";s:48:\"https://randomuser.me/api/portraits/women/86.jpg\";s:6:\"medium\";s:52:\"https://randomuser.me/api/portraits/med/women/86.jpg\";s:9:\"thumbnail\";s:54:\"https://randomuser.me/api/portraits/thumb/women/86.jpg\";}}";'
Enter password:
Benchmark
        Average number of seconds to run all queries: 0.046 seconds
        Minimum number of seconds to run all queries: 0.016 seconds
        Maximum number of seconds to run all queries: 0.263 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

SHOW TABLE STATUS LIKE 'bench_serialize'\G
*************************** 1. row ***************************
           Name: bench_serialize
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 464
 Avg_row_length: 3425
    Data_length: 1589248 #1.5 MB
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 501
    Create_time: 2015-11-21 15:50:53
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

Insert in multi column : Add 500 lines

mysqlslap -p -c10 -i 50 -q 'INSERT INTO bench_columns SET `gender`="female", `title`="miss", `first`="albane", `last`="lefevre", `street`="9643 rue louis-garrand", `city`="strasbourg", `state`="seine-et-marne", `zip`=78018, `email`="[email protected]", `username`="brownpeacock729", `password`="dublin", `salt`="lbcWrItu", `md5`="8945dae9635a94c05e80ab110ba2e25e", `sha1`="4ca52032ec4560b4ebbc7de32fb1f12c520843c1", `sha256`="99ee6d1aa97acfa621c2648cfd6ec0938df2dccb5f954ec53684583cdf83db81", `register`=1009850287, `dob`=733573810, `phone`="04-11-09-31-91", `cell`="06-41-88-62-02", `INSEE`="2930391197087 21", `picture_large`="https://randomuser.me/api/portraits/women/86.jpg", `picture_medium`="https://randomuser.me/api/portraits/med/women/86.jpg", `picture_thumbnail`="https://randomuser.me/api/portraits/thumb/women/86.jpg";'
Enter password:
Benchmark
        Average number of seconds to run all queries: 0.033 seconds
        Minimum number of seconds to run all queries: 0.020 seconds
        Maximum number of seconds to run all queries: 0.157 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

SHOW TABLE STATUS LIKE 'bench_columns'\G
*************************** 1. row ***************************
           Name: bench_columns
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 500
 Avg_row_length: 622
    Data_length: 311296 #304 KB
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 501
    Create_time: 2015-11-21 15:50:52
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

With 3000 lines identicly

SHOW TABLE STATUS LIKE 'bench_text'\G
*************************** 1. row ***************************
           Name: bench_text
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2631
 Avg_row_length: 1002
    Data_length: 2637824 #2.5 MB
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 3001
    Create_time: 2015-11-21 16:29:23
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

 SHOW TABLE STATUS LIKE 'bench_serialize'\G
*************************** 1. row ***************************
           Name: bench_serialize
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2832
 Avg_row_length: 1671
    Data_length: 4734976 #4.5 MB
Max_data_length: 0
   Index_length: 0
      Data_free: 5242880
 Auto_increment: 3001
    Create_time: 2015-11-21 16:29:11
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

SHOW TABLE STATUS LIKE 'bench_columns'\G
*************************** 1. row ***************************
           Name: bench_columns
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2840
 Avg_row_length: 928
    Data_length: 2637824 #2.5 MB
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: 3052
    Create_time: 2015-11-21 15:50:52
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment