MySQL Bugs: #83066: Feature Request : Use Invisible Indexes Specific Query

Bug #83066	Feature Request : Use Invisible Indexes Specific Query
Submitted:	21 Sep 2016 4:12	Modified:	21 Oct 2017 1:25
Reporter:	Yoshiaki Yamasaki	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Invisible Indexes

Description:
I want to use invisible indexes in specific query for "Staged Rollout". Oracle Database has the feature to turn them on for a particular session. I hope MySQL will have same feature.

How to repeat:
N/A

Suggested fix:
Please check "Description".

I think this feature is useful when we create new index as follows.

 1. Create invisible index.
 2. Evaluate some queries performance using invisible index. The queries are used by application.
 3. If evaluate result is fine, user change invisible index to visible index. If evaluate result is not fine, user remove invisible index.

This is something that we considered in the original design of this feature, but elected to not implement.

The rationale was that it might be confusing if an invisible index was sometimes still visible in some contexts, leading to non-full confidence that it could indeed be removed.  In a similar case; FORCE INDEX will also not use an invisible index (since 8.0.1).

We rely on feedback from users in order to decide on product direction.  I am going to leave this bug as verified for now, so that we can collect feedback.  Please click "affects me" if this is something you would like to see added.

I think this feature implemented on 8.0.3 with SET_VAR optimizer hint as follows.

  SELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=ON') */ name,region FROM country WHERE region='Eastern Asia';

Is this correct?

Hi Yoshiaki,
yes, you are right that the new optimizer_switch was added as the fix for this bug. You can either set it for the whole session:

SET SESSION @@optimizer_switch = 'use_invisible_indexes=ON';

Or even globally:

SET GLOBAL @@optimizer_switch = 'use_invisible_indexes=ON';

You are quite correct that there is no hint to let a query see an invisible index, but your workaround with SET_VAR is an elegant solution to the problem .