View Revisions: Issue #15021

All RevisionsBack to Issue
Summary 15021: Use VIEW to cirumvent the database column limit
Revision 2019-07-04 17:29 by ollehar
Description

One answer table can be split into multiple smaller tables and then merged in a VIEW.

Example:

CREATE TABLE one (
    id INT AUTO_INCREMENT PRIMARY KEY,
    col1 varchar(5000),
    col2 varchar(5000),
    col3 varchar(5000),
    col4 varchar(5000),
    col5 varchar(5000),
    col6 varchar(5000),
    col7 varchar(5000),
    col8 varchar(5000),
    col9 varchar(5000),
    col10 varchar(5000),
    col11 varchar(5000),
    col12 varchar(5000),
    col13 varchar(5000)
) ENGINE MyISAM;

CREATE TABLE two (
    pid INT AUTO_INCREMENT PRIMARY KEY,
    lol1 varchar(5000),
    lol2 varchar(5000),
    lol3 varchar(5000),
    lol4 varchar(5000),
    lol5 varchar(5000),
    lol6 varchar(5000),
    lol7 varchar(5000),
    lol8 varchar(5000),
    lol9 varchar(5000),
    lol10 varchar(5000),
    lol11 varchar(5000),
    lol12 varchar(5000),
    lol13 varchar(5000)
) ENGINE MyISAM;

And then

CREATE VIEW view1 AS SELECT * FROM one, two;

A limitation:

MariaDB [c1www]> INSERT INTO view1(col1, lol1) VALUES ('foo', 'bar');
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'c1www.view1'

Postgres might be stricter still:

The defining query of the view must have exactly one entry in the FROM clause, which can be a table or another updatable view.

Source: http://www.postgresqltutorial.com/postgresql-updatable-views/

This script shows that a VIEW has no limit on number of columns

<?php

$mysqli = mysqli_connect("localhost", "root", "", "c1www");
if (mysqli_connect_errno($mysqli)) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$res = mysqli_query($mysqli, "DROP TABLE four");
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

$res = mysqli_query($mysqli, "DROP TABLE five");
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

$res = mysqli_query($mysqli, "DROP VIEW view2");
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

$columns = [];
for ($i = 0; $i < 1000; $i++) {
    $columns[] = " somelongcolumnnamethatisnottoolongthough$i INT(1) ";
}
$columns = implode(',', $columns);
$query = "CREATE TABLE four (
    id INT AUTO_INCREMENT PRIMARY KEY,
    $columns
) ENGINE MyISAM";
$res = mysqli_query($mysqli, $query);
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

$columns = [];
for ($i = 0; $i < 1000; $i++) {
    $columns[] = " fomelongcolumnnamethatisnottoolongthough$i INT(1) ";
}
$columns = implode(',', $columns);
$query = "CREATE TABLE five (
    pid INT AUTO_INCREMENT PRIMARY KEY,
    $columns
) ENGINE MyISAM";
$res = mysqli_query($mysqli, $query);
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

$query = "CREATE VIEW view2 AS SELECT * FROM four, five";
$res = mysqli_query($mysqli, $query);
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

BUT: Should be key/value store instead
BUT: key/value store is not efficient for a stats module (depending on how the queries look) - performance test it?
Revision 2019-07-04 17:28 by ollehar
Description

One answer table can be split into multiple smaller tables and then merged in a VIEW.

Example:

CREATE TABLE one (
    id INT AUTO_INCREMENT PRIMARY KEY,
    col1 varchar(5000),
    col2 varchar(5000),
    col3 varchar(5000),
    col4 varchar(5000),
    col5 varchar(5000),
    col6 varchar(5000),
    col7 varchar(5000),
    col8 varchar(5000),
    col9 varchar(5000),
    col10 varchar(5000),
    col11 varchar(5000),
    col12 varchar(5000),
    col13 varchar(5000)
) ENGINE MyISAM;

CREATE TABLE two (
    pid INT AUTO_INCREMENT PRIMARY KEY,
    lol1 varchar(5000),
    lol2 varchar(5000),
    lol3 varchar(5000),
    lol4 varchar(5000),
    lol5 varchar(5000),
    lol6 varchar(5000),
    lol7 varchar(5000),
    lol8 varchar(5000),
    lol9 varchar(5000),
    lol10 varchar(5000),
    lol11 varchar(5000),
    lol12 varchar(5000),
    lol13 varchar(5000)
) ENGINE MyISAM;

And then

CREATE VIEW view1 AS SELECT * FROM one, two;

A limitation:

MariaDB [c1www]> INSERT INTO view1(col1, lol1) VALUES ('foo', 'bar');
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'c1www.view1'

Postgres might be stricter still:

The defining query of the view must have exactly one entry in the FROM clause, which can be a table or another updatable view.

Source: http://www.postgresqltutorial.com/postgresql-updatable-views/

This script shows that a VIEW has no limit on number of columns

<?php

$mysqli = mysqli_connect("localhost", "root", "", "c1www");
if (mysqli_connect_errno($mysqli)) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$res = mysqli_query($mysqli, "DROP TABLE four");
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

$res = mysqli_query($mysqli, "DROP TABLE five");
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

$res = mysqli_query($mysqli, "DROP VIEW view2");
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

$columns = [];
for ($i = 0; $i < 1000; $i++) {
    $columns[] = " somelongcolumnnamethatisnottoolongthough$i INT(1) ";
}
$columns = implode(',', $columns);
$query = "CREATE TABLE four (
    id INT AUTO_INCREMENT PRIMARY KEY,
    $columns
) ENGINE MyISAM";
$res = mysqli_query($mysqli, $query);
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

$columns = [];
for ($i = 0; $i < 1000; $i++) {
    $columns[] = " fomelongcolumnnamethatisnottoolongthough$i INT(1) ";
}
$columns = implode(',', $columns);
$query = "CREATE TABLE five (
    pid INT AUTO_INCREMENT PRIMARY KEY,
    $columns
) ENGINE MyISAM";
$res = mysqli_query($mysqli, $query);
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

$query = "CREATE VIEW view2 AS SELECT * FROM four, five";
$res = mysqli_query($mysqli, $query);
if (!$res) {
    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error . PHP_EOL;
}

BUT: Should be key/value store instead
BUT: key/value store is not efficient for a stats module (depending on how the queries look) - performance test it?
Revision 2019-07-03 16:47 by ollehar
Description

One answer table can be split into multiple smaller tables and then merged in a VIEW.

Example:

CREATE TABLE one (
    id INT AUTO_INCREMENT PRIMARY KEY,
    col1 varchar(5000),
    col2 varchar(5000),
    col3 varchar(5000),
    col4 varchar(5000),
    col5 varchar(5000),
    col6 varchar(5000),
    col7 varchar(5000),
    col8 varchar(5000),
    col9 varchar(5000),
    col10 varchar(5000),
    col11 varchar(5000),
    col12 varchar(5000),
    col13 varchar(5000)
) ENGINE MyISAM;

CREATE TABLE two (
    pid INT AUTO_INCREMENT PRIMARY KEY,
    lol1 varchar(5000),
    lol2 varchar(5000),
    lol3 varchar(5000),
    lol4 varchar(5000),
    lol5 varchar(5000),
    lol6 varchar(5000),
    lol7 varchar(5000),
    lol8 varchar(5000),
    lol9 varchar(5000),
    lol10 varchar(5000),
    lol11 varchar(5000),
    lol12 varchar(5000),
    lol13 varchar(5000)
) ENGINE MyISAM;

And then

CREATE VIEW view1 AS SELECT * FROM one, two;

A limitation:

MariaDB [c1www]> INSERT INTO view1(col1, lol1) VALUES ('foo', 'bar');
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'c1www.view1'

Postgres might be stricter still:

The defining query of the view must have exactly one entry in the FROM clause, which can be a table or another updatable view.

Source: http://www.postgresqltutorial.com/postgresql-updatable-views/

BUT: Should be key/value store instead
BUT: key/value store is not efficient for a stats module (depending on how the queries look) - performance test it?
Revision 2019-07-03 15:36 by ollehar
Description

One answer table can be split into multiple smaller tables and then merged in a VIEW.

Example:

CREATE TABLE one (
    id INT AUTO_INCREMENT PRIMARY KEY,
    col1 varchar(5000),
    col2 varchar(5000),
    col3 varchar(5000),
    col4 varchar(5000),
    col5 varchar(5000),
    col6 varchar(5000),
    col7 varchar(5000),
    col8 varchar(5000),
    col9 varchar(5000),
    col10 varchar(5000),
    col11 varchar(5000),
    col12 varchar(5000),
    col13 varchar(5000)
) ENGINE MyISAM;

CREATE TABLE two (
    pid INT AUTO_INCREMENT PRIMARY KEY,
    lol1 varchar(5000),
    lol2 varchar(5000),
    lol3 varchar(5000),
    lol4 varchar(5000),
    lol5 varchar(5000),
    lol6 varchar(5000),
    lol7 varchar(5000),
    lol8 varchar(5000),
    lol9 varchar(5000),
    lol10 varchar(5000),
    lol11 varchar(5000),
    lol12 varchar(5000),
    lol13 varchar(5000)
) ENGINE MyISAM;

And then

CREATE VIEW view1 AS SELECT * FROM one, two;

A limitation:

MariaDB [c1www]> INSERT INTO view1(col1, lol1) VALUES ('foo', 'bar');
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'c1www.view1'

BUT: Should be key/value store instead
BUT: key/value store is not efficient for a stats module (depending on how the queries look) - performance test it?
Revision 2019-07-03 15:07 by ollehar
Description

One answer table can be split into multiple smaller tables and then merged in a VIEW.

Example:

CREATE TABLE one (
    id INT AUTO_INCREMENT PRIMARY KEY,
    col1 varchar(5000),
    col2 varchar(5000),
    col3 varchar(5000),
    col4 varchar(5000),
    col5 varchar(5000),
    col6 varchar(5000),
    col7 varchar(5000),
    col8 varchar(5000),
    col9 varchar(5000),
    col10 varchar(5000),
    col11 varchar(5000),
    col12 varchar(5000),
    col13 varchar(5000)
) ENGINE MyISAM;

CREATE TABLE two (
    pid INT AUTO_INCREMENT PRIMARY KEY,
    lol1 varchar(5000),
    lol2 varchar(5000),
    lol3 varchar(5000),
    lol4 varchar(5000),
    lol5 varchar(5000),
    lol6 varchar(5000),
    lol7 varchar(5000),
    lol8 varchar(5000),
    lol9 varchar(5000),
    lol10 varchar(5000),
    lol11 varchar(5000),
    lol12 varchar(5000),
    lol13 varchar(5000)
) ENGINE MyISAM;

And then

CREATE VIEW view1 AS SELECT * FROM one, two;

A limitation:

MariaDB [c1www]> INSERT INTO view1(col1, lol1) VALUES ('foo', 'bar');
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'c1www.view1'

BUT: Should be key/value store instead
BUT: key/value store is not efficient for a stats module - performance test it?
Revision 2019-07-03 14:50 by ollehar
Description

One answer table can be split into multiple smaller tables and then merged in a VIEW.

Example:

CREATE TABLE one (
    id INT AUTO_INCREMENT PRIMARY KEY,
    col1 varchar(5000),
    col2 varchar(5000),
    col3 varchar(5000),
    col4 varchar(5000),
    col5 varchar(5000),
    col6 varchar(5000),
    col7 varchar(5000),
    col8 varchar(5000),
    col9 varchar(5000),
    col10 varchar(5000),
    col11 varchar(5000),
    col12 varchar(5000),
    col13 varchar(5000)
) ENGINE MyISAM;

CREATE TABLE two (
    pid INT AUTO_INCREMENT PRIMARY KEY,
    lol1 varchar(5000),
    lol2 varchar(5000),
    lol3 varchar(5000),
    lol4 varchar(5000),
    lol5 varchar(5000),
    lol6 varchar(5000),
    lol7 varchar(5000),
    lol8 varchar(5000),
    lol9 varchar(5000),
    lol10 varchar(5000),
    lol11 varchar(5000),
    lol12 varchar(5000),
    lol13 varchar(5000)
) ENGINE MyISAM;

And then

CREATE VIEW view1 AS SELECT * FROM one, two;

A limitation:

MariaDB [c1www]> INSERT INTO view1(col1, lol1) VALUES ('foo', 'bar');
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'c1www.view1'
Revision 2019-07-03 14:46 by ollehar
Description

One answer table can be split into multiple smaller tables and then merged in a VIEW.

Example:

CREATE TABLE one (
    id INT AUTO_INCREMENT PRIMARY KEY,
    col1 varchar(5000),
    col2 varchar(5000),
    col3 varchar(5000),
    col4 varchar(5000),
    col5 varchar(5000),
    col6 varchar(5000),
    col7 varchar(5000),
    col8 varchar(5000),
    col9 varchar(5000),
    col10 varchar(5000),
    col11 varchar(5000),
    col12 varchar(5000),
    col13 varchar(5000)
) ENGINE MyISAM;

CREATE TABLE two (
    pid INT AUTO_INCREMENT PRIMARY KEY,
    lol1 varchar(5000),
    lol2 varchar(5000),
    lol3 varchar(5000),
    lol4 varchar(5000),
    lol5 varchar(5000),
    lol6 varchar(5000),
    lol7 varchar(5000),
    lol8 varchar(5000),
    lol9 varchar(5000),
    lol10 varchar(5000),
    lol11 varchar(5000),
    lol12 varchar(5000),
    lol13 varchar(5000)
) ENGINE MyISAM;

And then

CREATE VIEW view1 AS SELECT * FROM one, two;