How to Ignore an Index in an SQL Query

1. Overview

A database index is a data structure that can be defined on one or more columns of a table. An index is designed to enable fast data lookup. As a result, query performance can improve. Further, the query optimizer determines whether to use an index for a specific query. We can supply the query with hints to ignore one or more indexes. We do this when we find a query using an index when it shouldn’t.

Some relational databases, notably MySQL and SQL Server, provide SQL syntax to ignore index(es) in a query. However, it may not always be possible for a query to ignore an index. Therefore, a query will use an index if it’s required for the query plan even if an index hint is added to ignore the index.

In this tutorial, we’ll learn how to design a query to ignore an index. We’ll use MySQL and SQL Server database examples to illustrate the feature. PostgreSQL does not support ignoring an index for a specific query with index/optimizer hints. PostgreSQL does support dropping an index, and disabling index scans altogether, but this is not the focus of this article.

2. Prerequisite Setup

Let’s set up the sample schemas provided on GitHub. We’ll be using the table Program in the sample database called University. If we need to find what indexes already exist in the table, for MySQL we can use:

SHOW INDEXES 
FROM Program;

For MySQL, we’ll use the predefined index program_department_id_fkey in the table Program.

For SQL Server we’ll use a new index. Therefore, let’s create a nonclustered column store index called csindx_simple in the table Program:

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple 
ON Program (department_id, type);

We’ll use this index to demonstrate ignoring an index.

3. A MySQL Example

To begin with, let’s identify an index to ignore. Therefore, let’s use the query profiling clauses EXPLAIN ANALYZE with a query on the Program table. Thereupon, we find that the query is using the program_department_id_fkey index to make an index lookup on the Program table:

EXPLAIN 
ANALYZE 
SELECT name, type, department_id 
FROM Program 
WHERE department_id=1
GROUP BY name, type, department_id 
ORDER BY department_id;
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on   (cost=2.53..4.8 rows=8) (actual time=0.145..0.147 rows=8 loops=1)
    -> Temporary table with deduplication  (cost=2.2..2.2 rows=8) (actual time=0.141..0.141 rows=8 loops=1)
        -> Index lookup on Program using program_department_id_fkey (department_id=1)  (cost=1.4 rows=8) (actual time=0.0899..0.0997 rows=8 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------+

Thereupon, let’s use the program_department_id_fkey index to demonstrate ignoring an index.

3.1. Ignoring Index With a USE INDEX Hint

The simplest way we can ignore one or more indexes is to use the USE INDEX() index hint in the query. Furthermore, we should specify only the indexes that we want to include. Accordingly, the query planner automatically ignores any index that’s not specified. Let’s analyze the query plan for the same query using the USE INDEX() index hint.

Furthermore, if we want to ignore all indexes we should specify an empty list:

EXPLAIN 
ANALYZE 
SELECT name, type, department_id 
FROM Program 
USE INDEX()
WHERE department_id=1 
GROUP BY name, type, department_id 
ORDER BY department_id;
+----------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                        |
+----------------------------------------------------------------------------------------------------------------+
| -> Table scan on   (cost=3.66..5.68 rows=4.8) (actual time=0.113..0.115 rows=8 loops=1)
    -> Temporary table with deduplication  (cost=3.13..3.13 rows=4.8) (actual time=0.11..0.11 rows=8 loops=1)
        -> Filter: (Program.department_id = 1)  (cost=2.65 rows=4.8) (actual time=0.0546..0.0729 rows=8 loops=1)
            -> Table scan on Program  (cost=2.65 rows=24) (actual time=0.0526..0.0671 rows=24 loops=1)
|
+----------------------------------------------------------------------------------------------------------------+

Accordingly, the query doesn’t use the program_department_id_fkey index this time and instead makes a table scan.

3.2. Ignoring Index With an IGNORE INDEX Hint

Another method we can use with a query to ignore one or more indexes is to use the IGNORE INDEX() index hint. Furthermore, this type of index hint can be used to explicitly specify an index list to ignore.

Let’s run the same query and specify program_department_id_fkey as the index to ignore:

EXPLAIN ANALYZE 
SELECT name, type, department_id 
FROM Program IGNORE INDEX(program_department_id_fkey) 
WHERE department_id=1 
GROUP BY name, type, department_id 
ORDER BY department_id;
+---------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                       |
+---------------------------------------------------------------------------------------------------------------+
| -> Table scan on   (cost=3.66..5.68 rows=4.8) (actual time=0.132..0.134 rows=8 loops=1)
    -> Temporary table with deduplication  (cost=3.13..3.13 rows=4.8) (actual time=0.129..0.129 rows=8 loops=1)
        -> Filter: (Program.department_id = 1)  (cost=2.65 rows=4.8) (actual time=0.0665..0.0861 rows=8 loops=1)
            -> Table scan on Program  (cost=2.65 rows=24) (actual time=0.0641..0.0799 rows=24 loops=1)
|
+---------------------------------------------------------------------------------------------------------------+

Indeed, the query doesn’t use the index and makes a table scan instead.

Furthermore, we can set the scope of the IGNORE INDEX() index hint to one of three options:

• IGNORE INDEX FOR GROUP BY
• IGNORE INDEX FOR ORDER BY
• IGNORE INDEX FOR JOIN

Additionally, the IGNORE INDEX without any scope-limiting sub-clause is equivalent to all three. To ignore an index in a specific scope only, let’s run the same query with the scope limited to GROUP BY:

EXPLAIN ANALYZE 
SELECT name, type, department_id 
FROM Program 
IGNORE INDEX FOR GROUP BY(program_department_id_fkey) 
WHERE department_id=1 
GROUP BY name, type, department_id 
ORDER BY department_id;
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on   (cost=4.33..6.6 rows=8) (actual time=0.636..0.639 rows=8 loops=1)
    -> Temporary table with deduplication  (cost=4..4 rows=8) (actual time=0.632..0.632 rows=8 loops=1)
        -> Index lookup on Program using program_department_id_fkey (department_id=1)  (cost=3.2 rows=8) (actual time=0.518..0.529 rows=8 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------+

As a result, the query is still using the index. This is because the index is not used for GROUP BY in the first place; therefore, it is not ignored. Let’s try limiting the scope to ORDER BY:

EXPLAIN ANALYZE 
SELECT name, type, department_id 
FROM Program 
IGNORE INDEX FOR ORDER BY(program_department_id_fkey) 
WHERE department_id=1 
GROUP BY name, type, department_id 
ORDER BY department_id;
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on   (cost=2.53..4.8 rows=8) (actual time=0.0891..0.0912 rows=8 loops=1)
    -> Temporary table with deduplication  (cost=2.2..2.2 rows=8) (actual time=0.0864..0.0864 rows=8 loops=1)
        -> Index lookup on Program using program_department_id_fkey (department_id=1)  (cost=1.4 rows=8) (actual time=0.0513..0.0572 rows=8 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------+

However, the query still uses the index. This is because the index is not used for ORDER BY in the first place; therefore, it is not ignored.

How about if we limit the scope to JOIN only:

EXPLAIN ANALYZE 
SELECT name, type, department_id 
FROM Program 
IGNORE INDEX FOR JOIN(program_department_id_fkey) 
WHERE department_id=1 
GROUP BY name, type, department_id 
ORDER BY department_id;
+---------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                       |
+---------------------------------------------------------------------------------------------------------------+
| -> Table scan on   (cost=3.66..5.68 rows=4.8) (actual time=0.094..0.0956 rows=8 loops=1)
    -> Temporary table with deduplication  (cost=3.13..3.13 rows=4.8) (actual time=0.0926..0.0926 rows=8 loops=1)
        -> Filter: (Program.department_id = 1)  (cost=2.65 rows=4.8) (actual time=0.0399..0.0519 rows=8 loops=1)
            -> Table scan on Program  (cost=2.65 rows=24) (actual time=0.0384..0.0478 rows=24 loops=1)
|
+---------------------------------------------------------------------------------------------------------------+

Indeed, this time, the query ignores the index and uses a table scan. This is because the index is used for JOIN in the first place; therefore, it is ignored.

3.3. Ignoring Index with Optimizer Hints

There’s yet another way we can ignore index/es in a query. That’s by supplying optimizer hints. Further, the optimizer hints to ignore indexes are introduced in MySQL 8.0 and have an equivalent for each of the index hints:

Furthermore, a regular query can specify an optimizer hint within /*+ */. When we profile the example query by including a NO_INDEX optimizer hint we find that the query doesn’t use the index and instead makes a table scan:

EXPLAIN ANALYZE 
SELECT /*+ NO_INDEX(Program program_department_id_fkey)*/ name, type, department_id 
FROM Program  
WHERE department_id =1  
GROUP BY name, type, department_id 
ORDER BY department_id;
+-------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                     |
+-------------------------------------------------------------------------------------------------------------+
| -> Table scan on   (cost=3.66..5.68 rows=4.8) (actual time=0.125..0.127 rows=8 loops=1)
    -> Temporary table with deduplication  (cost=3.13..3.13 rows=4.8) (actual time=0.123..0.123 rows=8 loops=1)
        -> Filter: (Program.department_id = 1)  (cost=2.65 rows=4.8) (actual time=0.0604..0.081 rows=8 loops=1)
            -> Table scan on Program  (cost=2.65 rows=24) (actual time=0.0583..0.0743 rows=24 loops=1)
|
+-------------------------------------------------------------------------------------------------------------+

When we use the scope limiting NO_INDEX_FOR_ORDER_BY, or the NO_INDEX_FOR_GROUP_BY, the query doesn’t ignore the index.

4. SQL Server Example

SQL Server supports two methods to ignore an index: the WITH(INDEX()) index hint and the query hint IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX. To start, let’s set the SHOWPLAN_ALL to ON to list the query plan so that we can verify if a query ignores an index.

4.1. Ignoring Index Using a WITH(INDEX()) Index Hint

We can use the WITH(INDEX()) hint to explicitly specify an index list to use.  Accordingly, the query planner automatically ignores any indexes that aren’t in the index list.

Let’s start with an example of using WITH to specify two indexes PK__Program__3213E83F9B13E52E, and csindx_simple:

SELECT name, type, department_id 
FROM Program 
WITH (INDEX(PK__Program__3213E83F9B13E52E,csindx_simple)) 
WHERE department_id =1  
GROUP BY name, type, department_id 
ORDER BY department_id;  
                                                                                                   
  |--Hash Match(Aggregate, HASH:([                                                                         1 Hash Match                     Aggregate   
       |--Hash Match(Inner Join, HASH:([                                                                   2 Hash Match                     Inner Join  
            |--Clustered Index Scan(OBJECT:([University].[dbo].[Program].[PK__Program__3213E83F9B13E52E]), 4 Clustered Index Scan           Clustered Index Scan 
            |--Index Scan(OBJECT:([University].[dbo].[Program].[csindx_simple]),                           4 Index Scan                     Index Scan

Indeed, the query uses the two indexes.

As an example, let’s ignore the csindx_simple index by specifying only the PK__Program__3213E83F9B13E52E index in the WITH(INDEX()) hint:

SELECT name, type, department_id 
FROM Program 
WITH (INDEX(PK__Program__3213E83F9B13E52E)) 
WHERE department_id =1  
GROUP BY name, type, department_id 
ORDER BY department_id; 
  |--Sort(DISTINCT ORDER BY:([University].[dbo].[Program].[name] ASC, [University].[dbo].[Program].[type] ASC))      1 Sort                           Distinct Sort  
       |--Clustered Index Scan(OBJECT:([University].[dbo].[Program].[PK__Program__3213E83F9B13E52E]),                2 Clustered Index Scan           Clustered Index Scan

As a result, the query ignores the csindx_simple index.

4.2. Ignoring Index With a Query Hint

A nonclustered memory-optimized column store index can improve query performance.  Further, SQL Server 2012 introduced a query hint called IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX that doesn’t let a query use the nonclustered memory-optimized column store index.

To show its use, let’s include the query hint IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX with the example query to not use a nonclustered column store index:

SELECT name, type, department_id 
FROM Program 
WHERE department_id =1 AND type='Major' 
GROUP BY name, type, department_id 
ORDER BY department_id 
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);   

  |--Sort(DISTINCT ORDER BY:([University].[dbo].[Program].[name] ASC))                                1 Sort                           Distinct Sort  
       |--Clustered Index Scan(OBJECT:([University].[dbo].[Program].[PK__Program__3213E83F9B13E52E]), 2 Clustered Index Scan           Clustered Index Scan

Accordingly, the query only uses the Clustered index scan along with a distinct order sort. Conversely, when we don’t include a query hint to ignore the nonclustered column store index, the query uses a Hash Match Aggregate operator to build a hash table in the memory.

5. Conclusion

In this article, we learned about running an SQL query by excluding all or specific indexes. However, only some relational databases support ignoring indexes in a query. We used an example query based on sample schemas in MySQL and SQL Server databases to exclude, or ignore, specific indexes. Further, the examples showed the different methods supported by these databases to ignore an index in a query.

In conclusion, we can ignore non-essential indexes in a query by using database-specific methods and SQL clauses. Furthermore, most of these methods use index or optimizer hints in a query.