5.6 Adding new index, it does not get used
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?