Why are there three indexes in this table?

Hi, I created a table with this SQL:

CREATE TABLE dv.search (
  VID BYTES NOT NULL,
  WORD BYTES NOT NULL,
  INDEX (WORD)
);

This is the result if I show the indexes:

> show index from dv.search;
  table_name |   index_name    | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+-----------------+------------+--------------+-------------+-----------+---------+-----------
  search     | primary         |   false    |            1 | rowid       | ASC       |  false  |  false
  search     | search_word_idx |    true    |            1 | word        | ASC       |  false  |  false
  search     | search_word_idx |    true    |            2 | rowid       | ASC       |  false  |   true
(3 rows)

Any idea why there are three indexes? I understand that the database created a rowid index, but why is there a second one on rowid?

The output of a SHOW INDEX is one row for each column in each index. The search_word_index, like all secondary indexes, has a suffix which includes the primary key column[s] which it does not already include.

Thanks @ajwerner , but isn’t the rowid now indexed twice? If the rowid is automatically added to the search_word_idx, it looks redundant to me…

It’s saying that the rowid column is stored in search_word_idx. This is true because secondary indexes need to store primary index columns.