Vindexes on the website's Vtexplain docs

[Brian Lie]

Hello, I need a help for understanding the Vtexplain docs at https://vitess.io/docs/user-guides/vtexplain/#explaining-a-query

First, why do we need users_name_idx table in the example? Will the absence of users_name_idx table make sharded cluster in the example completely unusable?

I'm not really sure about this one because I perceive user_id of users table as Primary Vindex.

Did users_name_idx table made to set the `name` column in users table as secondary Vindex to speed up queries (prevents all-shard scans)?

Second question, this is regarding the hash function Vitess use to generate keyspace IDs (if I understand this correctly).

In the Vtexplain docs, insert example part:

1 mainkeyspace/22-24: insert into users_name_idx(name, user_id) values ('john', 1) /* vtgate:: keyspace_id:22c0c31d7a0b489a16332a5b32b028bc */

how did Vtgate calculates the keyspace ID 22c0c31d7a0b489a16332a5b32b028bc ? This looked like MD5 hash (because it's 32 bytes in length) but if it so, which string is used as input for the MD5 hash function?

Third question closely related to previous question,

2 mainkeyspace/16-18: insert into users(user_id, name) values (1, 'john') /* vtgate:: keyspace_id:166b40b44aba4bd6 */

This keyspace ID here does look different than previous keyspace ID generated for users_name_idx table. Which algorithm are used for this particular keyspace ID?

[Brian Lie]

Wait so I searched the docs further by throwing keywords at Google and found answer to question #1 at https://vitess.io/docs/schema-management/vschema/

Functional and Lookup Vindex

A Functional Vindex is one where the column value to keyspace ID mapping is pre-established, typically through an algorithmic function. In contrast, a Lookup Vindex is one that gives you the ability to create an association between a value and a keyspace ID, and recall it later when needed.

Typically, the Primary Vindex is Functional. In some cases, it is the identity function where the input value yields itself as the kesypace id. However, one could also choose other algorithms like hashing or mod functions.

A Lookup Vindex is usually backed by a lookup table. This is analogous to the traditional database index, except that it is cross-shard. At the time of insert, the computed keyspace ID of the row is stored in the lookup table against the column value.

I see now, the table users_name_idx in the vtexplain example is a lookup table for a particular Vindex defined in the Vschema.

Still, I'm interested on how the hash was generated. Which part of the data is hashed, and did Vitess determine what key range a data belongs to from the few first characters of the hash result?