This topic describes information about the performance test scenario of MySQL.

The following tables are created for the performance test on MySQL:

  • The customer table
    CREATE TABLE `customer` (
      `C_CUSTKEY` int(11) NOT NULL,
      `C_NAME` varchar(25) NOT NULL,
      `C_ADDRESS` varchar(40) NOT NULL,
      `C_NATIONKEY` int(11) NOT NULL,
      `C_PHONE` varchar(15) NOT NULL,
      `C_ACCTBAL` decimal(12,2) NOT NULL,
      `C_MKTSEGMENT` varchar(10) NOT NULL,
      `C_COMMENT` varchar(117) NOT NULL,
      PRIMARY KEY (`C_CUSTKEY`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • The lineitem table
    CREATE TABLE `lineitem` (
      `L_ORDERKEY` bigint(20) NOT NULL,
      `L_PARTKEY` int(11) NOT NULL,
      `L_SUPPKEY` int(11) NOT NULL,
      `L_LINENUMBER` bigint(20) NOT NULL,
      `L_QUANTITY` decimal(12,2) NOT NULL,
      `L_EXTENDEDPRICE` decimal(12,2) NOT NULL,
      `L_DISCOUNT` decimal(12,2) NOT NULL,
      `L_TAX` decimal(12,2) NOT NULL,
      `L_RETURNFLAG` varchar(1) NOT NULL,
      `L_LINESTATUS` varchar(1) NOT NULL,
      `L_SHIPDATE` date NOT NULL,
      `L_COMMITDATE` date NOT NULL,
      `L_RECEIPTDATE` date NOT NULL,
      `L_SHIPINSTRUCT` varchar(25) NOT NULL,
      `L_SHIPMODE` varchar(10) NOT NULL,
      `L_COMMENT` varchar(44) NOT NULL,
      PRIMARY KEY (`L_ORDERKEY`,`L_LINENUMBER`,`L_SHIPDATE`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • The nation table
    CREATE TABLE `nation` (
      `N_NATIONKEY` int(11) NOT NULL,
      `N_NAME` varchar(25) NOT NULL,
      `N_REGIONKEY` int(11) NOT NULL,
      `N_COMMENT` varchar(152) DEFAULT NULL,
      PRIMARY KEY (`N_NATIONKEY`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • The orders table
    CREATE TABLE `orders` (
      `O_ORDERKEY` bigint(20) NOT NULL,
      `O_CUSTKEY` int(11) NOT NULL,
      `O_ORDERSTATUS` varchar(1) NOT NULL,
      `O_TOTALPRICE` decimal(12,2) NOT NULL,
      `O_ORDERDATE` date NOT NULL,
      `O_ORDERPRIORITY` varchar(15) NOT NULL,
      `O_CLERK` varchar(15) NOT NULL,
      `O_SHIPPRIORITY` int(11) NOT NULL,
      `O_COMMENT` varchar(79) NOT NULL,
      PRIMARY KEY (`O_ORDERKEY`,`O_ORDERDATE`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • The part table
    CREATE TABLE `part` (
      `P_PARTKEY` int(11) NOT NULL,
      `P_NAME` varchar(55) NOT NULL,
      `P_MFGR` varchar(25) NOT NULL,
      `P_BRAND` varchar(10) NOT NULL,
      `P_TYPE` varchar(25) NOT NULL,
      `P_SIZE` int(11) NOT NULL,
      `P_CONTAINER` varchar(10) NOT NULL,
      `P_RETAILPRICE` decimal(12,2) NOT NULL,
      `P_COMMENT` varchar(23) NOT NULL,
      PRIMARY KEY (`P_PARTKEY`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • The partsupp table
    CREATE TABLE `partsupp` (
      `PS_PARTKEY` int(11) NOT NULL,
      `PS_SUPPKEY` int(11) NOT NULL,
      `PS_AVAILQTY` int(11) NOT NULL,
      `PS_SUPPLYCOST` decimal(12,2) NOT NULL,
      `PS_COMMENT` varchar(199) NOT NULL,
      PRIMARY KEY (`PS_PARTKEY`,`PS_SUPPKEY`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • The region table
    CREATE TABLE `region` (
      `R_REGIONKEY` int(11) NOT NULL,
      `R_NAME` varchar(25) NOT NULL,
      `R_COMMENT` varchar(152) DEFAULT NULL,
      PRIMARY KEY (`R_REGIONKEY`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • The supplier table
    CREATE TABLE `supplier` (
      `S_SUPPKEY` int(11) NOT NULL,
      `S_NAME` varchar(25) NOT NULL,
      `S_ADDRESS` varchar(40) NOT NULL,
      `S_NATIONKEY` int(11) NOT NULL,
      `S_PHONE` varchar(15) NOT NULL,
      `S_ACCTBAL` decimal(12,2) NOT NULL,
      `S_COMMENT` varchar(101) NOT NULL,
      PRIMARY KEY (`S_SUPPKEY`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The default MySQL schema is not sufficient to support the performance test against the 100 GB dataset. You must create indexes to improve data query performance so that you can complete the test on MySQL. The data provided in this performance white paper shows the results of tests with indexes.

create index idx_li_sd on lineitem(l_shipdate);
create index idx_li_rf_ls on lineitem(l_returnflag,l_linestatus);
create index idx_p_pk on part(p_partkey);
create index idx_ps_pk on partsupp(ps_partkey);
create index idx_s_sk on supplier(s_suppkey);
create index idx_ps_sk on partsupp(ps_suppkey);
create index idx_p_s on part(p_size);
create index idx_p_t on part(p_type);
create index idx_s_nk on supplier(s_nationkey);
create index idx_n_nk on nation(n_nationkey);
create index idx_n_rk on nation(n_regionkey);
create index idx_r_rk on region(r_regionkey);
create index idx_r_n on region(r_name);
create index idx_ps_sc on partsupp(ps_supplycost);
create index idx_c_mk on customer(c_mktsegment);
create index idx_c_ck on customer(c_custkey);
create index idx_o_ck on orders(o_custkey);
create index idx_li_ok on lineitem(l_orderkey);
create index idx_o_ok on orders(o_orderkey);
create index idx_o_od on orders(o_orderdate);
create index idx_o_op on orders(o_orderpriority);
create index idx_li_sk on lineitem(l_suppkey);
create index idx_c_nk on customer(c_nationkey);
create index idx_li_dc on lineitem(l_discount);
create index idx_li_q on lineitem(l_quantity);
create index idx_n_n on nation(n_name);
create index idx_li_rf on lineitem(l_returnflag);
create index idx_li_sm on lineitem(l_shipmode);
create index idx_li_cd on lineitem(l_commitdate);
create index idx_li_rd on lineitem(l_receiptdate);
create index idx_li_pk on lineitem(l_partkey);
create index idx_p_b on part(p_brand);
create index idx_p_c on part(p_container);
create index idx_o_os on orders(o_orderstatus);