MySQL Bugs: #70299: Feature request: make an index invisible to the optimizer

Bug #70299	Feature request: make an index invisible to the optimizer
Submitted:	11 Sep 2013 13:47	Modified:	2 May 2014 5:15
Reporter:	Morgan Tocker	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.7+	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Sometimes I will find a situation when I want to drop an index because it can be satisfied by another index, a composite index, etc.  However, if I make a wrong decision I could effectively bring my application down while i wait for the index to be re-added (could be quite a long time if the table is large).

A couple of examples how I could make this mistake:

1) The other indexes may perform far worse.

2) The other indexes are suitable, but there are queries which explicitly use an optimizer hint naming an index.  These will now have errors and refuse to run.

What I would like to have, is a way to disable indexes (in all sessions) from optimizer selection, while having them still maintained by DML.

How to repeat:
mysql [localhost] {msandbox} (test) > CREATE TABLE a (a int not null, b int not null, index index_on_b (b));
Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {msandbox} (test) > SELECT * FROM a USE INDEX (index_on_b);
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > ALTER TABLE a DROP INDEX index_on_b;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > SELECT * FROM a USE INDEX (index_on_b);
ERROR 1176 (42000): Key 'index_on_b' doesn't exist in table 'a'

Suggested fix:
Similar functionality exists in Oracle 11g:
http://docs.oracle.com/cd/B28359%5F01/server.111/b28286/statements%5F5011.htm

"VISIBLE | INVISIBLE  Use this clause to specify whether the index is visible or invisible to the optimizer. An invisible index is maintained by DML operations, but it is not be used by the optimizer during queries unless you explicitly set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level.

To determine whether an existing index is visible or invisible to the optimizer, you can query the VISIBILITY column of the USER_, DBA_, ALL_INDEXES data dictionary views."

Thank you for your feature request.

I would agree with this feature request. Another use-case where error messages originating from USE INDEX hints are dangerous is when a database administrator wants to temporarily drop an index to increase insertion speed for some data migration / sanitation while the application(s) are still running.

Indexes should be a completely optional part of DDL.

The Oracle database, for instance, simply ignores /*+INDEX(...)*/ (and all other hints) if they can no longer be satisfied.

Of course, it's still a good idea to log warnings / info about a missing index somewhere.

The new hints in 5.7 work as Lukas describes (warning instead of error) :)
http://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html

The existing query hints remain as errors, and not all of these have a corresponding equivalent using the new syntax yet.

Ooh, Oracle comment-style syntax! Nice move + pragmatic solution at keeping existing error behaviour the way it is. Looks worth a blog post to me :)

I think the warning instead of error is good as it maintains the separation between the query (sql) and implementation (database).

However with LDAP (e.g. sun dsee) it is possible to disallow queries which don't use an index (result is the "unwilling to perform" error). Which is helpful to prevent full scans. However with InnoDB the PRIMARY is also an index, so this would be hard to do in a useful way.

I've recently done some queries with a FORCE INDEX(idx) to make sure it couldn't do a table scan and had to do an index scan. This is basically disabling the fallback.

So please consider some options to force index usage and fail otherwise as the fallback might be too expensive.

Related:
https://bugs.mysql.com/bug.php?id=79073