mysql 添加列 性能,查询的MySQL性能在where子句中添加列

I have a query making an addition of several column values in the WHERE clause. I can't precompute this addition in a single column because the combination of columns to use varies between queries. My problem is that my table is very large (several hundreds of millions of rows) and the performances very bad.

Example table:

+---------+------------+--------+--------+--------+--------+

| tableId | categoryId | value1 | value2 | value3 | value4 |

+---------+------------+--------+--------+--------+--------+

| 1 | 1 | 1 | 0 | 5 | 7 |

| 2 | 1 | 8 | 1 | 7 | 0 |

| 3 | 1 | 10 | 5 | 0 | 20 |

| 4 | 2 | 0 | 15 | 0 | 22 |

| 5 | 2 | 20 | 0 | 11 | 0 |

+---------+------------+--------+--------+--------+--------+

Example queries:

SELECT * FROM myTable WHERE categoryId = 1 AND (value1 + value2 + value3 + value4) > 9;

SELECT * FROM myTable WHERE categoryId = 1 AND (value1 + value3 + value4) > 5;

What would be the best strategy for improving performances of such queries? (edit: I already have an index on categoryId, this does not help)

Does using an index help for such queries? Would I then have to create all possible indexes for all possible combinations of columns? Wouldn't the resulting indexes be very very large?

ALTER TABLE myTable

ADD INDEX(categoryId, value1),

ADD INDEX(categoryId, value2),

ADD INDEX(categoryId, value3),

ADD INDEX(categoryId, value4),

ADD INDEX(categoryId, value1, value2),

ADD INDEX(categoryId, value1, value3),

ADD INDEX(categoryId, value1, value4),

etc

Or maybe creating a link table, with boolean value fields specifying which columns were used? But that would result in a table with several billions rows, not sure this is better...

+---------+-----------+-----------+-----------+-----------+----------+

| tableId | useValue1 | useValue2 | useValue3 | useValue4 | valueSum |

+---------+-----------+-----------+-----------+-----------+----------+

| 1 | 1 | 1 | 1 | 1 | 13 |

| 1 | 1 | 1 | 1 | 0 | 6 |

| 1 | 1 | 1 | 0 | 0 | 1 |

| 1 | 1 | 1 | 0 | 1 | 8 |

| 1 | 1 | 0 | 1 | 1 | 13 |

| 1 | 1 | 0 | 1 | 0 | 6 |

| 1 | 1 | 0 | 0 | 0 | 1 |

| 1 | 1 | 0 | 0 | 1 | 8 |

| 1 | 0 | 1 | 1 | 1 | 12 |

| 1 | 0 | 1 | 1 | 0 | 5 |

| 1 | 0 | 1 | 0 | 0 | 0 |

| 1 | 0 | 1 | 0 | 1 | 7 |

| 1 | 0 | 0 | 1 | 1 | 12 |

| 1 | 0 | 0 | 1 | 0 | 5 |

| 1 | 0 | 0 | 0 | 1 | 7 |

+---------+-----------+-----------+-----------+-----------+----------+

With an Index:

ALTER TABLE linkTable INDEX(tableId, useValue1, useValue2, useValue3, useValue4, valueSum);

Any other ideas?

解决方案

All my data types and indexes are correct.

My design with enumerated columns is not very elegant, but adapted to a row-based database such as MySQL and gives the best performances on that kind of engine.

To really fix this performance issue, I should move to a column-based database, using a better design as described in comments of my other question (where data to aggregate would be in a same column but several rows).