CODEWITHSUNDEEP
mysqlperformanceindexing
XWang
XWang

Reputation: 797

How MySQL handles IN predicates

Suppose the table T has three columns, 

 id int not null auto_increment,
 my_id int,
 name varchar(200).

and the query is "select * from T where my_id in (var_1, var_2, ..., var_n) and name = 'name_var'".

Is there any performance difference between below two indices?

Upvotes: 0

Views: 50

Answers (3)

Upendrakumarp
Upendrakumarp

Reputation: 21

•Index1: (my_id, name) •Index2: (name, my_id).

Yes, above two would slightly differ when it comes to query performance.

Always, the leftmost fields are the most important in determining the efficiency and selectivity of an index. index should be built on the column(s) which are frequently used in the WHERE, ORDER BY, and GROUP BY clauses. Hope this helps!

Upvotes: 1

Rick James
Rick James

Reputation: 142296

In general, it is best to start the INDEX with the WHERE clauses with col = const. One range can come last. IN is sort of like =, sort like a range. Hence, this is best:

INDEX(name, id)

Think of it this way. The index is an ordered list. With this index, it will start at the name=... and then have to scan or leapfrog through all the ids with that name.

I suspect the PRIMARY KEY is not (id). If it were, why would you be checking the name?

Upvotes: 0

O. Jones
O. Jones

Reputation: 108696

In a composite index, the column to be searched should appear first. So, if you are searching for a set of id values, you'll want id to show up first in the index.

But if id is the primary key and you're using a SELECT * clause to retrieve the whole row, it doesn't make sense to add another index. The way tables are organized, all the data of the row appears clustered with each id value. So just use the index on the primary key.

tl;dr: neither (id,name) nor (name,id) will help this query.

Upvotes: 0

Related Questions