how to create an index for a IN usage in two columns

Question

I need to optimize a table (It's INNODB) where I'm going to do a query using IN in two columns.

This is the query:

SELECT `emails`.* FROM `emails` 
  WHERE (`from` IN ('[email protected]', '[email protected]') OR `to` IN ('[email protected]', '[email protected]'))

the from and to fields are VARCHAR(255)

How I can create an index to help speed it up. Or if I should change my query strategy please let me know

I'm not sure if I should create one index for each column, or a single index with the two columns. I'm also not sure if the IN clause will make the index work or not.

Oleg Kuralenko · Accepted Answer · 2018-03-12 19:45:42Z

1

question 1 - which index to create

Just create one index for each column. After that mysql would be able combine results of each index into one set. More on the subject

question 2 - in clauses

You can check it out yourself via explain after you create the indexes. It should work. If for some reason your mysql version doesn't use the index for IN queries, you can rewrite your original query using OR because your query is equivalent to

`from` = '[email protected]' OR `from` = '[email protected]' OR `to` =  '[email protected]' OR `to` = '[email protected]'

answered Mar 12, 2018 at 19:45

Oleg Kuralenko

11.7k2 gold badges32 silver badges41 bronze badges

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

Comments

Rick James · Accepted Answer · 2018-03-13 02:20:10Z

0

In case "index merge" does not kick in, "turn OR into UNION":

SELECT  *
    FROM  `emails`
    WHERE  `from` IN ('[email protected]', '[email protected]')
UNION
SELECT  *
    FROM  `emails`
    WHERE  `from``to` IN ('[email protected]', '[email protected]')

And have

INDEX(`from`),
INDEX(`to`)

answered Mar 13, 2018 at 2:20

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

how to create an index for a IN usage in two columns

2 Answers 2

question 1 - which index to create

question 2 - in clauses

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

question 1 - which index to create

question 2 - in clauses

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related