How to Force a Query to Not Use a Index on a Given Table

How can I force a query to not use a index on a given table?

SELECT *
FROM MyTable WITH (INDEX(0))
WHERE MyIndexedColumn = 0

Query would normally use the index on MyIndexedColumn, but due to the table hint, it will instead tablescan.

SELECT *
FROM MyTable WITH (INDEX(IndexName))
WHERE MyIndexedColumn = 0

Query would normally use the index on MyIndexedColumn, but due to the table hint, it will instead use the index named IndexName.

{SQLITE3} How can I force a query to not use a index on a given table?

It's buried in the syntax diagrams for SELECT, but there is a way - Using NOT INDEXED with a table name in the FROM clause:

sqlite> CREATE TABLE foo(bar);
sqlite> CREATE INDEX foo_idx ON foo(bar);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM foo WHERE bar = ?;
QUERY PLAN
`--SEARCH TABLE foo USING COVERING INDEX foo_idx (bar=?)
sqlite> EXPLAIN QUERY PLAN SELECT * FROM foo NOT INDEXED WHERE bar = ?;
QUERY PLAN
`--SCAN TABLE foo

As you can see, the first query uses the index, and the second one doesn't.

How to read EXPLAIN QUERY PLAN output.

SQL Server don't use specific index

SQL Server does not have a way to exclude an index for consideration by the query optimizer. However, you can use the WITH keyword to specify which index you want. For example, to force a query to use the index ix1_groupId_id2_id3_ival_iid:

SELECT id2, COUNT(*)
FROM table (WITH(INDEX(ix1_groupId_id2_id3_ival_iid))
WHERE groupId=x
GROUP BY id2

Before using an index hint, I would recommend trying to understand why the database is not wanting to use the index. It may be the table is very small, so a table scan is faster than an index seek. Or possibly the statistics are out of date on these indexes, so using the primary key appears to be faster to the optimizer.

Index hints introduce unexpected code dependencies on the schema. If someone wanted to change the index name in the future, they may not realize this query requests the index by name, so the query will now fail until the new name is used.

how to stop using a database index for a particular query?

You can give SQL Server a query hint, e.g. force it to use a specific index - but to my knowledge, there's no way to prevent it from using a specific index. Can you find some other index that your query in question could use instead?

Also, with SQL Server 2008, you have new additional query hints, called OPTIMIZE FOR, which might help you define (and explain) to the query optimizer what it should optimize for.

See the MSDN documentation on query hints for complete details.

This is the typical and fundamental tradeoff you must make when adding indices - yes, it might increase performance of one query you're looking at - but at the same time, it might decrease others. There's really no easy solution here - either the gain for the other query is greater than the pain for this query - then keep the index. Otherwise drop it.

How can I force a view to use a specific index?

What you need to take cares of writing the hints: the query/table hints are for recommendation only, the optimizer may ignore them as in your case.
This point is not well documented but mentioned somewhere, for example

When an index hint referring to multiple indexes is used on the fact
table in a star join, the optimizer ignores the index hint and returns
a warning message.

On other hand, your query is a typical example when OPTION(RECOMPILE) is required because of declared variables: once generated and cached plan for the narrow period is not suitable for a large period etc.

How to make postgres not use a particular index?

You can probably disable the index by doing some dummy arithmetic on the indexed column.

 ...AND "chaindata_tokentransfer"."chain_id" + 0 = 1...

If you put that into production, make sure to add a code comment on why you are doing such an odd thing.

I'm curious why it chooses to use that index, despite apparently knowing how astonishingly awful it is. If you show the plan for the query with the index disabled, maybe we could figure that out.

If the dummy arithmetic doesn't work, what you could do is start a transaction, drop the index, execute the query (or the just the EXPLAIN of it), then rollback the drop. That is probably not something you want to do often in production (especially since the table will be locked from when the index is dropped until the rollback. Also because you might accidentally commit!) but getting the plan is probably worth doing it once.

How to force oracle to not to use an index

use NO_INDEX hint

http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements006.htm#BABHJBIB

for instance

SELECT /*+ NO_INDEX(t1 id1) */ 
  FROM t1,
       t2  
 WHERE t1.id = t2.id;