Welcome to Wide Tables benchmark for MySQL’s documentation !¶

Contents

Welcome to Wide Tables benchmark for MySQL’s documentation !
Where can I learn more?
Indices and tables

Where can I learn more?¶

The goal of this study is well described at : https://dev.lsstcorp.org/trac/wiki/db/tests/MySql/WideTables.

Data structure ¶

Tables are filled with integer random values, and double and float random values between [0.1]. Each case contains arrays of 5500 columns. A value can be stored in a standard MySQL column, or clustered with other value in a MySQL blob column.

SQL queries ¶

Query 0 : count(*)¶

Retrieve number of tuples of the array:

SELECT count(*) as COUNT FROM Table_0;

Query 1 : search by id ¶

Retrieve first value of the array for a given id:

SELECT Table_0.Field_0 FROM Table_0 WHERE id=500;

Query 2 : search by id with JOIN ¶

Retrieve first value of each MySQL table composing the full array for a given id:

SELECT Table_0.Field_0, Table_1.Field_1 FROM Table_0, Table_1 WHERE id=500 AND Table_0.id=Table_1.id;

Query 3 : search predicate on simple columns with JOIN ¶

Retrieve first value of each MySQL table composing the full array with a search predicate on the first column containing double values:

SELECT Table_0.Field_0, Table_1.Field_1 FROM Table_0, Table_1 WHERE Table_0.Double_0=0.99 AND Table_0.id=Table_1.id;

Technical informations ¶

MySQL query cache is removed after each case.

Case A : Keeping blobs together with non-blob columns in one table ¶

Case B : Keeping all blobs together in a different dedicated table, with objectId that can be used to join back to the table with non-blob columns ¶

Case C : Have all non-blob columns but split across multiple tables ¶

The legend for C case still need to be corrected w.r.t. this :

Subcase 01 : 500 columns by table

Subcase 02 : one table of 500 columns, and 100 columns by table

Subcase 03 : one table of 500 columns, and 1000 columns by table

Navigation

Navigation