maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi Varun,

The code for the task is mostly fine. But I would like to question the user
experience. 

== Ignoring an index causes InnoDB table rebuild ==

First (and foremost), making an index ignored (or not ignored) in an InnoDB
table rebuilds the table:

MariaDB [test]> set session alter_algorithm='instant'; alter table t1 alter index c ignore;
Query OK, 0 rows affected (0.001 sec)

ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY

This misses the point of the feature. If one needs to rebuild the table anyway,
they can just drop (or recreate) the index.

Curiously, it works with MyISAM. I think, the ideal solution would be to make
ignoring/not ignoring the index an SQL layer change that doesn't concern the
storage engine. If this is not possible, lets make InnoDB aware that it
doesn't need to rebuild the table in this case.

== Backward compatibility for SHOW CREATE TABLE ==

SHOW CREATE TABLE will show something like:

  KEY `a` (`a`) IGNORE,

MySQL here is more backward-compatible and shows:

  KEY `a` (`a`) /*!80000 INVISIBLE */,

Do we need to do this as well?  I have no strong opinion here.

== Use of Ignored index in hints ==

I can use Ignored index in FORCE/IGNORE index hints:

MariaDB [test]> explain select * from t1 force index(a) where a<3 and b<3;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

This is allowed. I think, this misses the point of the feature. The point of
this feature is to check "what will happen if one drops the index".  For this
particular query, the effect will be like this:

MariaDB [test]> explain select * from t1 force index(no_such_index) where a<3 and b<3;
ERROR 1176 (42000): Key 'no_such_index' doesn't exist in table 't1'

and MySQL agrees with me here (below index a is present but is invisible) :

mysql> explain select * from t1 force index(a) where a<3 and b<3;
ERROR 1176 (42000): Key 'a' doesn't exist in table 't1'

== optimizer_switch flag ==

Seeing "ignore_indexes=on" in optimizer_switch looks scary.

I would say, we don't need an @@optimizer_switch flag. Maybe, Serg disagrees. 

If we have to have a switch flag, and its name should be short (my opinion), I
would use the noun form:
 
  ignored_indexes=ON|OFF

I'm not 100% happy with this, but ignoreD is much better here than ignore.

(Before acting on this review point, lets check with others).

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net