MySQL Forums
Forum List  »  InnoDB

5.6 Adding new index, it does not get used
Posted by: Paul Williamson
Date: July 15, 2014 03:00PM

I am trying to add a new index to an InnoDB table with only three columns, which are:

id (PK)
name (varchar(255))
other_id (varchar(255))

id is of course an index, name is also indexed, but I want an index on other_id too (not it's real name, btw). I run the command, and it completes very quickly, gives no error but also says 0 rows affected. A 'show create table' shows the new index there as I would expect. Searches with a 'where other_id like' complete very quickly (and correctly), but when I use 'Explain' to ensure the index is being used, it says 'null' for possible keys, key used etc and shows a row count of pretty much the total number of rows in the table (thousands).

When I do an 'Explain' on a select using the name field, that index shows up as expected, with a row count of 9 (correct).

I read somewhere that 'analyse table' should be run on InnoDB tables if 'innodb_stats_persistent' is ON, so I did that because it is on in my setup. It returned immediately with a status of OK.

I tried restarting MySQL, no effect. Does anyone know why 'Explain' reports that the index is not even regarded as possible, let alone being used?

Options: ReplyQuote


Subject
Views
Written By
Posted
5.6 Adding new index, it does not get used
1532
July 15, 2014 03:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.