Do Indexes Work with "In" Clause

Do indexes work with IN clause

Yeah, that's right. If your Employee table has 10,000 records, and only 5 records have EmployeeTypeId in (1,2,3), then it will most likely use the index to fetch the records. However, if it finds that 9,000 records have the EmployeeTypeId in (1,2,3), then it would most likely just do a table scan to get the corresponding EmployeeIds, as it's faster just to run through the whole table than to go to each branch of the index tree and look at the records individually.

SQL Server does a lot of stuff to try and optimize how the queries run. However, sometimes it doesn't get the right answer. If you know that SQL Server isn't using the index, by looking at the execution plan in query analyzer, you can tell the query engine to use a specific index with the following change to your query.

SELECT EmployeeId FROM Employee WITH (Index(Index_EmployeeTypeId )) WHERE EmployeeTypeId IN (1,2,3)

Assuming the index you have on the EmployeeTypeId field is named Index_EmployeeTypeId.

Index for using IN clause in where condition

In this case using IN for that much data is not good at all.
this best way is to use INNER JOIN instead.
It would be nicer if insert those names into a temp table and INNER JOIN it with your SELECT query.

Database Index when SQL statement includes IN clause

You can also use EXISTS, depending on your database like so:

select * from table t
where id = 1
and exists (
    select 1 from groupteam
    where   department = 'marketing'
        and group = t.group
)

• Create a composite index on individual indexes on groupteam's department and group
• Create a composite index or individual indexes on table's id and group

Do an explain/analyze depending on your database to review how indexes are being used by your database engine.

Do indexes work in NOT IN or clause?

The issue is locality within the index. If you have two columns with letters in col1 and numbers in col 2, then an index might look like:

Ind  col1 col2
 1    A    1
 2    A    1
 3    A    1
 4    A    2
 5    B    1
 6    B    1
 7    B    2
 8    B    3
 9    B    3
10    C    2
11    C    3

(ind is the position in the index. The record locator is left out.)

If you are looking for col1 = 'B', then you can find position 5 and then scan the index until position 9. If you are looking for col1 <> 'B', then you need to find the first record that is not 'B' scan and repeat for the first record after. This becomes worse with IN and NOT IN.

An additional factor is that if a relative handful of records satisfy the equality condition, then almost all records will fail -- and often indexes are not useful when almost all records need to be read. One sometimes-exception to this are clustered indexes.

Oracle has better index optimizations than most databases -- it will do multiple scans starting in different locations. Even so, an inequality is often much less useful for an index.

IN clause not using index

I will go out on a limb and say it is because you are using the MyISAM engine.

It is working perfectly fine with INNODB as can be seen in this Answer of mine.

I will try to spook up at least 1 honorable reference on the matter.

Here, The range Join Type, clearly an INNODB focus as it is the default engine. And when not explicitly mentioned in the manual in some documentation hierarchy, it is assumed.

Note, there is nothing contiguous about the id's in my example link. Meaning, don't hyperfocus on type=range in its EXPLAIN output. The speed is arrived at via the Optimizer (the CBO).

The cardinality in my example is very high (4.3 Million). The target id counts are relatively low (1000). The index is used.

Your situation may be the opposite: your cardinality might be incredibly low, like 3, and the optimizer decides to abandon use of the index.

To check your index cardinality, see the Manual Page SHOW INDEX Syntax.

A simple call such as:

show index from ratings;

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ratings |          0 | PRIMARY  |            1 | id          | A         |     4313544 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

SQL index with in clause

The solution to the problem we faced, was reindexing the table. The table had a 10 million records and we recently cleaned up the data in the table (when we realized that we had duplicate records) and that reduced it to almost half of the amount of records it previously had. So we thought we will give a try with reindexing, since anyway it needed it. And that helped :)

Are indexes used if the WHERE clause contains unindexed columns

Yes, the query will use almost certainly one of the indexes to preselect which rows might fullfill at least some of the criteria. To check if the WHERE clause is true for unindexed columns (like your column H), Oracle just checks in the table itself. As the index points to the correct physical location of the table, this is normally quite fast.

Which index is used, depends on many factors like size of the table, size of the index, uniqueness of the table columns, uniqueness of the index, data distribution of the column values etc.

To see which indexes are used in your query, have a look at the execution plan, which you can see for instance in SQL Developer by hitting F10.

EDIT: In my experience, Oracle selects the most promising index (which will reduce the amount of rows most), and then checks all columns in the WHERE clause by such a table look up.

Please make also sure that the statistics of the table are up to date. If in doubt, check with

SELECT table_name, last_analyzed FROM USER_TABLES;

If last_analyzed is empty or an old date, please search for DBMS_STATS.GATHER_TABLE_STATS to refresh the stats.

Using index in Update Clause on Apache Ignite Sql Query

How Ignite executes these queries - at the time of writing this post - is by spliting the query into two parts:

1. SELECT with the same condition as specified in the original query.
2. Iterate over the SELECT results and update each record as specified in the SET clause.

It's usually easy to guess how the SELECT part will look like based on the original query. In your case, I'm pretty sure SELECT * FROM DB.MY_TABLE WHERE Name = 'Me' is the query that will be executed.

I would just check that EXPLAIN SELECT * FROM DB.MY_TABLE WHERE Name = 'Me' uses the index you want it to use and then trust the system to do the UPDATE correctly.

Does order of columns of Multi-Column Indexes in where clause in MySQL matter?

The order of columns in a multi-column index matters.

The documentation of the multiple-column indexes reads:

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

This means an index on columns name and city can be used when an index on column name is needed but it cannot be used instead of an index on column city.

The order of conditions in the WHERE clause doesn't matter. The MySQL optimizer does a lot of work on the conditions on the WHERE clause to eliminate as many candidate rows as possible as early as possible and to read as little data as possible from the tables and indexes (because some of the read data is dropped because it doesn't match the entire WHERE clause).

---

Related Topics