0

So the so source of the data is the internet, and random bots that submit values to my apache server.

Periodically new columns appear, but they are not always present. The other problem is then you have a bunch of NULLs which you don't want to store at all if possible.

Later, I would statistically analyze the data, but we are just trying to store it here.

Currently there are 99 columns, with a minimum of 27 and a max of 59, but the max will probably go up slowly.

I am currently using a single master table, but the downside is there are tons of NULLs stored for each record. Lots of wasted space. Presently the most common value is 64 NULLs per row.

So I had an idea, and was wondering is anyone can improve on it.

You need a master table, and every submitted column has a lookup table with an index and a value.

master
index   num_columns table_index
 1        27        1

There would be table for simplicity named tables27 to table100

table27 would be
index , columns_names_csv, col1_index,col2_index,...col27_index

table28 would be much the same except
index,  columns_names_csv, col1_index,col2_index,...col28_index

All the indexes would be integers used to point to the correct values in the corresponding lookup table.

Since they would all be integers there would be no need to worry about data types.

So in addition to the master table and 99 (and growing) lookup tables you would have tables27 through, currently, 59 but that number could eventually increase.

Is there a better way to do this?

For fun here's the table

+-----------------------------------+---------------------+------+-----+---------+----------------+
| Field                             | Type                | Null | Key | Default | Extra          |
+-----------------------------------+---------------------+------+-----+---------+----------------+
| id                                | int(11)             | NO   | PRI | NULL    | auto_increment |
| lstREMOTE_ADDR                    | int(11)             | NO   |     | NULL    |                |
| lstCONTENT_LENGTH                 | int(11)             | YES  |     | NULL    |                |
| lstREQUEST_TIME_FLOAT             | datetime(4)         | NO   |     | NULL    |                |
| lstUNIQUE_ID                      | varchar(128)        | NO   |     | NULL    |                |
| lstSERVER_PORT                    | int(5)              | NO   |     | NULL    |                |
| lstREMOTE_PORT                    | int(5)              | NO   |     | NULL    |                |
| lstREDIRECT_UNIQUE_ID             | int(10) unsigned    | YES  |     | NULL    |                |
| lstREDIRECT_REQUEST_METHOD        | int(10) unsigned    | YES  |     | NULL    |                |
| lstREDIRECT_STATUS                | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_HOST                      | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_USER_AGENT                | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_ACCEPT                    | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_ACCEPT_LANGUAGE           | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_ACCEPT_ENCODING           | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_CONNECTION                | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_COOKIE                    | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_UPGRADE_INSECURE_REQUESTS | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_CACHE_CONTROL             | int(10) unsigned    | YES  |     | NULL    |                |
| lstPATH                           | tinyint(3) unsigned | YES  |     | NULL    |                |
| lstSERVER_SIGNATURE               | tinyint(3) unsigned | YES  |     | NULL    |                |
| lstSERVER_SOFTWARE                | int(10) unsigned    | YES  |     | NULL    |                |
| lstSERVER_NAME                    | int(10) unsigned    | YES  |     | NULL    |                |
| lstSERVER_ADDR                    | int(10) unsigned    | YES  |     | NULL    |                |
| lstDOCUMENT_ROOT                  | tinyint(3) unsigned | YES  |     | NULL    |                |
| lstREQUEST_SCHEME                 | int(10) unsigned    | YES  |     | NULL    |                |
| lstCONTEXT_PREFIX                 | int(10) unsigned    | YES  |     | NULL    |                |
| lstCONTEXT_DOCUMENT_ROOT          | int(10) unsigned    | YES  |     | NULL    |                |
| lstSERVER_ADMIN                   | int(10) unsigned    | YES  |     | NULL    |                |
| lstSCRIPT_FILENAME                | tinyint(3) unsigned | YES  |     | NULL    |                |
| lstREDIRECT_URL                   | int(10) unsigned    | YES  |     | NULL    |                |
| lstGATEWAY_INTERFACE              | int(10) unsigned    | YES  |     | NULL    |                |
| lstSERVER_PROTOCOL                | int(10) unsigned    | YES  |     | NULL    |                |
| lstREQUEST_METHOD                 | int(10) unsigned    | YES  |     | NULL    |                |
| lstQUERY_STRING                   | int(10) unsigned    | YES  |     | NULL    |                |
| lstREQUEST_URI                    | int(10) unsigned    | YES  |     | NULL    |                |
| lstSCRIPT_NAME                    | tinyint(3) unsigned | YES  |     | NULL    |                |
| lstPHP_SELF                       | int(10) unsigned    | YES  |     | NULL    |                |
| lstREDIRECT_spider                | int(10) unsigned    | YES  |     | NULL    |                |
| lstspider                         | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_PRAGMA                    | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_FROM                      | int(10) unsigned    | YES  |     | NULL    |                |
| lstREDIRECT_QUERY_STRING          | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_REFERER                   | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_DNT                       | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_REQUESTED_WITH          | int(10) unsigned    | YES  |     | NULL    |                |
| lstCONTENT_TYPE                   | int(10) unsigned    | YES  |     | NULL    |                |
| lstREDIRECT_ERROR_NOTES           | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_FORWARDED_FOR           | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_IF                        | int(10) unsigned    | YES  |     | NULL    |                |
| lstREDIRECT_HTTPS                 | int(10) unsigned    | YES  |     | NULL    |                |
| lstREDIRECT_SSL_TLS_SNI           | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTPS                          | int(10) unsigned    | YES  |     | NULL    |                |
| lstSSL_TLS_SNI                    | int(10) unsigned    | YES  |     | NULL    |                |
| lstPHP_AUTH_USER                  | int(10) unsigned    | YES  |     | NULL    |                |
| lstPHP_AUTH_PW                    | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_AMP_CACHE_TRANSFORM       | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_ACCEPT_CHARSET            | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_CLIENT_IP                 | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_REMOTE_IP               | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_ORIGINATING_IP          | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_REMOTE_ADDR             | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_REAL_IP                 | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_REVERSE_VIA               | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_VARNISH                 | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_UA_COMPATIBLE           | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_POWERED_BY              | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_TE                        | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_PIPER_ID                | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_UCBROWSER_UA            | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_WAP_PROFILE             | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_EBO_UA                  | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_OPERAMINI_FEATURES      | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_OPERAMINI_PHONE         | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_OPERAMINI_ROUTE         | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_DEVICE_STOCK_UA           | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_OPERAMINI_PHONE_UA      | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_FORWARDED                 | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_ACCEL_INTERNAL          | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_WAP_CONNECTION            | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_CONTENT_OPT             | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_KEEP_ALIVE                | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_APPID                     | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_CLIENTID                  | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_DID                       | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_PLATFORM                  | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_RISKUDID                  | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_SIGNTYPE                  | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_TRACKERID                 | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_WORKSPACEID               | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_VIA                       | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_PROXY_CONNECTION          | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_DEPTH                     | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_TRANSLATE                 | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_MIME_VERSION              | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_VERMEER_CONTENT_TYPE    | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_X_CNECTION                | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_EXPECT                    | int(10) unsigned    | YES  |     | NULL    |                |
| lstHTTP_IF_MODIFIED_SINCE         | int(10) unsigned    | YES  |     | NULL    |                |
+-----------------------------------+---------------------+------+-----+---------+----------------+
  • EAV is the alternative, but it has issues, too. – Rick James Aug 24 '19 at 18:19
0

If I understand correctly, the table structure you're proposing appears to introduce a large overhead and will also make effective querying impossible.

First of all, note that InnoDB tables can be modified to add, drop and many other operations without locking the table for DML (i.e. SELECT, INSERT, UPDATE, DELETE etc) - see the MariaDB documentation for an InnoDB Online DDL Overview.

Secondly, the idea that NULL values create so much wasted space is actually untrue. With the default storage engine (InnoDB), NULL values only take up a single bit per row. So with 64 NULL values per row, you're only wasting the equivalent of a single INT.

Therefore, stop worrying, and just create the columns in your table in the straightforward, normal way. There is no need for complicated solutions here.

I know this may sound a bit incredible, so I don't expect you to just take my word for it. Therefore, below I've attempted to provide some hard evidence.

EVIDENCE

First of all, consider the following quote is from the MySQL InnoDB row format documentation pages under the section about the COMPACT row format storage characteristics (MariaDB InnoDB now uses the DYNAMIC row format by default, but it's identical to COMPACT with regards to NULL values):

The variable-length part of the record header contains a bit vector for indicating NULL columns. If the number of columns in the index that can be NULL is N, the bit vector occupies CEILING(N/8) bytes. (For example, if there are anywhere from 9 to 16 columns that can be NULL, the bit vector uses two bytes.) Columns that are NULL do not occupy space other than the bit in this vector.

The proof is in the pudding as they say, so let's perform an experiment. First let's create two identical tables and look at how much space they take up on disk:

MariaDB [db_1]> CREATE TABLE table1(id int unsigned PRIMARY KEY, 
value1 int, 
value2 int, 
value3 int, 
value4 int, 
value5 int, 
value6 int, 
value7 int, 
value8 int, 
value9 int, 
value10 int, 
value11 int, 
value12 int, 
value13 int, 
value14 int, 
value15 int, 
value16 int
);
Query OK, 0 rows affected (0.044 sec)

MariaDB [db_1]> CREATE TABLE table2 LIKE table1;
Query OK, 0 rows affected (0.022 sec)

[root@localhost ~]# cd /var/lib/mysql/db_1
[root@localhost db_1]# ll
total 204
-rw-rw---- 1 mysql mysql    65 Aug 25 00:08 db.opt
-rw-rw---- 1 mysql mysql  1379 Aug 25 10:32 table1.frm
-rw-rw---- 1 mysql mysql 98304 Aug 25 10:32 table1.ibd
-rw-rw---- 1 mysql mysql  1379 Aug 25 10:32 table2.frm
-rw-rw---- 1 mysql mysql 98304 Aug 25 10:32 table2.ibd

And then let's populate table1 with integers and table2 with NULLs, 10,000 rows for each:

MariaDB [db_1]> DELIMITER ;;
MariaDB [db_1]> BEGIN NOT ATOMIC
SET @i:=0; 
REPEAT 
  INSERT INTO table1(id, value1, value2, value3, value4, value5, value6,
    value7, value8, value9, value10, value11, value12, value13, value14,
    value15, value16) 
  VALUES (@i, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16); 
  INSERT INTO table2(id) VALUES (@i);
  SET @i := @i + 1; 
UNTIL @i > 10000 END REPEAT; 
END;;
Query OK, 20002 rows affected (1 min 11.007 sec)
MariaDB [db_1]> DELIMITER ;

[root@localhost db_1]# ll
total 9588
-rw-rw---- 1 mysql mysql      65 Aug 25 00:08 db.opt
-rw-rw---- 1 mysql mysql    1379 Aug 25 10:32 table1.frm
-rw-rw---- 1 mysql mysql 9437184 Aug 25 10:36 table1.ibd
-rw-rw---- 1 mysql mysql    1379 Aug 25 10:32 table2.frm
-rw-rw---- 1 mysql mysql  360448 Aug 25 10:36 table2.ibd

As you can see, table1 which is populated with only non-NULL INTs is 26 times larger than table2 which is populated only with NULLs except for the PK column.

We can also compare this with a table3 that has only a PK colum:

CREATE TABLE table3(id int unsigned PRIMARY KEY);
Query OK, 0 rows affected (0.033 sec)
MariaDB [db_1]> DELIMITER ;;
MariaDB [db_1]> BEGIN NOT ATOMIC
SET @i:=0; 
REPEAT 
  INSERT INTO table3(id) VALUES (@i);
  SET @i := @i + 1; 
UNTIL @i > 10000 END REPEAT; 
END;;
Query OK, 10001 rows affected (37.228 sec)

MariaDB [db_1]> DELIMITER ;

And this is just 16K (the size of an InnoDB page) smaller than table2 which has 16 extra INT columns all populated with NULLs - not bad for 10,000 rows:

[root@localhost db_1]# ll table3.*
-rw-rw---- 1 mysql mysql    922 Aug 25 10:48 table3.frm
-rw-rw---- 1 mysql mysql 344064 Aug 25 10:50 table3.ibd

For info, I'm using MariaDB Server 10.4.7 and (mostly) default settings.

  • This is not an entirely accurate way of measuring its true size due to metadata, pre-allocation and more. Even with optimize command on the table does not result in predictable numbers. If you add a integer to a table with 10k rows thats an increase of 20k plus overhead. I should be able to compute the over head manually and it should agree with observed file sizes, but it doesn't. If run an optimize, add a row, and optimize again it should come out 1 record larger, but the math never works out for me. – cybernard Aug 25 '19 at 16:41
  • Note: I am not saying that NULL don't use less space than I think, but more I would like proof that I can manually compute to verify what is happening. A few columns do have actual data in them a date/time column and a single varchar.with 27 bytes per record. My idd file is 24M which seems unusually large to me. If your theory is correct my table should be way smaller. – cybernard Aug 25 '19 at 17:12
  • @cybernard You're right that it's not easy to measure the true size, and the calculations and rules involved are beyond me. My point is more to show that NULLs take up very little space. I'll look for support for my claim that 1 NULL = 1 bit in the InnoDB documentation and add that to the answer if I can find it. – dbdemon Aug 25 '19 at 17:13
  • If your numbers are correct something is seriously going wrong as my table is actually 24M in size. Also a second table which has no business being 15MB in size is. It has five ints and a varchar(27) and 96572 rows. Each varchar has the full 27 bytes but 27 *100,000 is only 2.7M. – cybernard Aug 25 '19 at 17:20
  • 1
    @cybernard I think we need some more info about your system in order to answer that question: Operating system, MariaDB version, storage engine - I assume InnoDB? Row format of the table, InnoDB file format. Any non-default settings in your .cnf files that could be relevant? Are you able to re-produce the findings from my experiment on your system? – dbdemon Aug 26 '19 at 9:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.