Re: Large number of columns causing - Unknown Error

[Rick James]

I recommend a compromise.

(I assume most of the columns are usually null?)

1. Decide which columns you need to search on. Decide on no more than, say, 5 indexes into the table, using those columns.

2. Build a table with an ID (probably AUTO_INCREMENT), plus the searchable columns.

3. Build a second table with two columns: an ID and a blob. The ID is the same as the one in the other table (except not AUTO_INCREMENT) and used for JOINing. The blob (TEXT / BLOB / MEDIUMTEXT / ...) is a marshalled up set of key-values of the rest of the 'hilarious number of columns'. Simply leave out any that are missing.

Advantages:
* Civilized tables (sane number of columns and rows)
* Efficiently searchable (probably faster than your many-column version, MUCH faster than the key-value approach)
* Still open-ended... Adding a column requires no ALTER TABLE.
* Less disk space.

Disadvantages:
* Your code must do the marshaling. XML would do. I prefer JSON (easily usable in Perl, PHP, etc). Other options exist. I even prefer to compress it before storing (CPU is cheaper than I/O).
* One JOIN needed (unless the query can live with the main table's set of columns).
* Some complex searches (one that needs some data from the blob) cannot be performed entirely in SQL. (Do what you can in SQL, then finish the filtering in your App.)