I have one table with hundreds of columns, but almost all are null.
Each 'select' in this table return only one line, with hundreds of fields.
Working with PHP, I am testing each field with:
if (!empty($dataset["field"])) { ... }
For optimization reasons, If I select only the valued fields I can use array functions to know the name of each field, and speed up my application.
But how can I select only the fields with values in this case, with MySQL 5.6 ?
something like:
SELECT * FROM mytable ONLY fields_not_null();
This table records the amount of events per minute during one day. The recordset is basically:
Change it to 24 tables (for each hour) decrease the performance, and we still have dozens of null fields in each table.
Change it to one recordset with date_time and boolean is the worst scenario for performance.
You can try using col IS NOT NULL: SELECT * FROM mytable WHERE column IS NOT NULL
To me, this is an indication that your MySQL database is not properly constructed.
The easiest way to do this in PHP would be to use array_filter.
For example: array_filter($row) would get rid of all the empty values (loosely equivalent to false). If you only wanted to get rid of null and empty values but leave 0's then you can use a different callback, for example strlen which would return only values that have at least one character:
$filteredRow = array_filter($row, 'strlen');
Now $filteredRow will have all the fields that have values in a field=>value array. If you just want the field names, you can use array_keys:
$fields = array_keys($filteredRows);
Without knowing what you will be doing with the data, I can't point at a single solution. Here are several:
Plan A: Let the application code deal with all the NULLs.
Plan B: Have one row per non-NULL minute. You may want to use the GROUP_CONCAT() function.
Plan C: Use COALESCE() and/or IFNULL() functions.
Plan D: Store the non-NULL values in an associative array (minute->value), serialize into a JSON string, store the JSON ins a TEXT field.
Do not use 24 tables.
Do not try to have 1440 columns in a table; it will not work in InnoDB.