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;
|