MySQL create index sum of two columns data in one table

Question

 >> table1
 -------------------------------------------------------------
 | column1 | column2 | column3 | column4 | column5 | column6 |
 +---------+---------+---------+---------+---------+---------+
 |  data1  |  data2  |  data3  |  data4  |  data5  |  data6  |
 +---------+---------+---------+---------+---------+---------+
 |  data7  |  data8  |  data9  |  data10 |  data11 |  data12 | 
 +---------+---------+---------+---------+---------+---------+
                               .
                               .
                               .
 +---------+---------+---------+---------+---------+---------+
 |  data   |  data   |  data   |  data   |  data   |  data   | 
 -------------------------------------------------------------

 >> query
 SELECT * FROM table1
     WHERE (column5 + column6) >= 6;

How do I create an index for this query in MySQL?

An index might not help this particular query because you are adding together the two columns. — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 16, 2017 at 4:10
in Postgresql it would be CREATE INDEX sumthing ON table1 (( column5 + column6 )); for mysql i don't know. — Jasen
– Jasen, Commented Nov 16, 2017 at 5:36

Rick James · Accepted Answer · 2017-11-16 14:42:41Z

2

Newer versions of MySQL (5.7.6) (and MariaDB in 5.2 with VIRTUAL columns) have "Generated" columns. You would create such as (col5+col6), then index this fake column. Such a column can be STORED in the table our could be VIRTUAL. Read the docs to get the details.

col56 GENERATED ALWAYS AS (col5 + col6) VIRTUAL  -- or STORED

answered Nov 16, 2017 at 14:42

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jamongcake Over a year ago

I do not want to create a new column. But thanks for telling me the new way.

John Biddle · Accepted Answer · 2017-11-16 05:05:34Z

0

I don't believe you can create an index on the sum of two columns.

You could add an additional field that contains column5 + column6 and index on that.

You could create a view with an additional column equal to column5 + column6 and index on that.

answered Nov 16, 2017 at 5:05

John Biddle

2,7022 gold badges17 silver badges26 bronze badges

4 Comments

Tim Biegeleisen Over a year ago

Hi John, I agree with creating a computed column, but I'm not sure if it is possible to index a view in MySQL.

John Biddle Over a year ago

Yeah, you're right, no indexes on views, sorry for the bad advice.

jamongcake Over a year ago

@TimBiegeleisen Thanks for the advice. I think I should study harder.

jamongcake Over a year ago

@JohnBiddle I don't know why I can comment on only one person at once. Thank you for your kind answers.

Collectives™ on Stack Overflow

MySQL create index sum of two columns data in one table

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related