MySQL and index on sum
I've got a table with some data. For simplicity it looks like:
id - auto_increment c1 - int c2 - int created - date
There might be lots of data
The way I get the data is such:
SELECT * FROM my_table WHERE created >= DATE_SUB(NOW(), INTERVAL 1 HOUR) ORDER by c1 + c2 DESC LIMIT 10
How to make index for c1 + c2? Is it possible? Should I add it, I mean will it slow the query?
The only index that is useful for
SELECT * -- I assume many fields here FROM my_table WHERE created >= DATE_SUB(NOW(), INTERVAL 1 HOUR) -- useful ORDER by c1 + c2 DESC -- expressions cannot be indexed LIMIT 10 -- does not matter
For more discussion on creating the optimal index, see my index cookbook.
Hot off the press: 5.7 can create and index materialized virtual columns, such as (c1+c2). Hence, a composite index starting with that col and ending with created might be better in 5.7.
You can just create a index with both fields. Something like:
ALTER TABLE my_table ADD INDEX sumC1C2 (c1,c2)