MySQL Bugs: #57523: Use/force index if exists

Bug #57523	Use/force index if exists
Submitted:	18 Oct 2010 12:57	Modified:	18 Oct 2010 14:28
Reporter:	Joffrey MICHAIE	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	force index, IF EXISTS, use index

Description:
Hi,

Currently, when you want to force the optimizer to use a particular index, and the index doesn't exists, the query will fail with an error

How to repeat:
Load the sakila database,

mysql> use sakila;
mysql> SELECT SQL_NO_CACHE  * FROM film_actor USE INDEX (fake_index) WHERE film_id > 5 AND actor_id < 50  AND actor_id >= 10;
ERROR 1176 (42000): Key 'fake_index' doesn't exist in table 'film_actor'
mysql> 

Suggested fix:
When the index does not exists, it should throw a warning, and still perform the query.

Otherwise, the availability to use "IF EXISTS" like for other DDL statements, would be great !

Best regards,
Joffrey

Thank you for the feature request.

This is also an issue I think if you reference a view (as a table may get "migrated" to a view for a number of reasons).