I'm working on the backend of a quality evaluation tool. And I hit a the limit of too many columns.
SQLSTATE[HY000]: General error: 1005 Can't create table
xxx(errno: 185 "Too many columns")
And because I've seen this question. Let me explain:
The company I work for is selling advanced sensors with algorithms in the firmware. With every new firmware, we want to compare it to the previous ones, to check if something still works, has improved or is now worse. For this, we have a number of test scenarios (200+). Each scenario consists of (400-2.000) measurements from which all are needed. I call a run through one scenario a record.
In my previous design I created a table for each record and I ended up with 50.000+ tables, which is not optimal, to say the least. In an attempt to normalize the data I created the following schema:
There is on table for each scenario (
slug_2 here). And a
idmap table to relate each record with the firmware and other important data (here shown as
other_stuff). The problem now is, that I need 1-4 columns for each measurement in one scenario which means 8.000 columns max, which is a new problem.
id and the table name, there are no relations, nor search requirements on these large tables. Each request would consist of two rows from the same table to compare them.
- Is there a better design to store the data?
I thought maybe I should store the measurements as json string or serialize the measurements array directly to binary. I could use a compression algorithm before storing and decompress the data after reading?
- Are this many columns even that bad, if they are never searched?
If not, how can I get around the
errno: 185 "Too many columns"
To answer some questions:
- Are all the tests pass/fail, or do you need to store more detail.
Every measurement consists of two values at the moment.
Ok (bool) and
Ok just stores if there was an error or not. The sensor can provide ~10 different errors, why the test failed, but I'm reducing this to
false for now. If there is a need to store the error, I'll create an error table and store the relation instead. The second value
Ct stores the time needed for this measurement. It is possible that there will be a 3. value in the future, but for now this is not needed.
- Does the type of detail depend on the test?
No, it doesn't in all scenarios the measurements are the same.
- Is there a Product or Sensor these tests apply to?
Yes, I store all sensors, firmwares, and other things in separate tables like
other_stuff and relate it with the
idmap table to the records of the tested scenarios.
Here some info what I'm working with:
Laravel 8 with php eloquent on a