MySQL / MariaDB - Strategies for Large Number of Columns

I am about to design a database for which I will have frames from multiple data sources arriving, at different times. Each of these frames will need to be dissected and converted into engineering units for storage into the database with an appropriate data type (e.g. FLOAT, INT). Accordingly, the general structure would be a DATETIME datatype for a primary key and the remaining columns would be each of these converted values. This would keep everything aligned to the proper time of receipt.

For most of my tables (representing each data source/frame) the column counts are relatively small, several hundred columns. However there will be one that looks to have approximately 10,000 columns. From previous experience, I know that there are both hard limits for the maximum number of columns (4,096) as well as the the complete row size (65,536 bytes).

Previously, I would have split this up into "adjacent" tables, like tableA_000, tableA_001, etc with each holding some arbitrary number of columns and yet still all using the same primary key (time). But doing this becomes a bit messy, namely that I have to create a separate lookup table to first reference when I want to find something. (For example, "colVoltage is in tableA_007", "colTimer is in tableB_003") I can certainly do this again in this manner, but I'm curious ... does anybody else have any different strategies for handling this? In an ideal world, I would be able to both insert to and select from what (to me) looks like one big table despite having so many columns.

MariaDB 10.11.3 / CentOS 8

Personally I think this sounds like a use case for a kind of document store. Creating tables with so many columns is likely to become unmanageable quite quickly. You can do this in MySQL using the JSON data type which gives you a very flexible data structure within a single table. You can also use generated columns to create fast indexes on data elements within your data that are common.

I'd also question why you're using a DATETIME as a primary key, surely there's a potential for clashes? This would be my create statement:

CREATE TABLE frames (   
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  type VARCHAR(50) NOT NULL,
  data JSON NOT NULL,
  example_key INT GENERATED ALWAYS AS (data->"$.example_key"),
  INDEX(created),
  INDEX(type),
  INDEX(example_key)
); 

The MySQL docs are good on this, at https://dev.mysql.com/doc/refman/8.0/en/json.html and https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html#json-column-indirect-index

Obviously there are other database engines that are designed exclusively to function in this way which might be a better choice if this is the only function of your app and doesn't need a relational DB for other bits. Also bear in mind that the JSON type can be a bit slow, because if you're SELECTing you end up pulling out the entire contents of that field for each record returned. Again generated fields can be good for this.