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?

Answers


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

is

INDEX(created)

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)

Need Your Help

How do I integrate MongoDB with Solr?

php mongodb solr

I've seen this question before, but it's never received a real answer- so I was wondering.. Can someone point me in the right direction as to how I can integrate mongoDB with Solr? I'm looking for ...

Boost 1.45 - 1.49 breaks with MySql C++ Connector 1.1 when using Visual Studio 2010

c++ visual-studio-2010 visual-c++ boost mysql-connector

I have a C++ project that compiled fine under VS2008, but now I'm running VS2010 and not able to get back to 2008. That project isn't compiling anymore cause I get "error C2371: 'int8_t': redefinit...