The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

MyRocks and Index-Only Scans

Contents

  1. Secondary Keys Only
  2. Background: Mem-Comparable Keys
  3. Index-Only Support for Various Datatypes
  4. Index-Only Support for Various Collations
    1. 1. Binary (Reversible) Collations
    2. 2. Restorable Collations
    3. 3. All Other Collations
  5. Covering Secondary Key Lookups for VARCHARs

This article is about MyRocks and index-only scans on secondary indexes. It applies to MariaDB's MyRocks, Facebook's MyRocks, and other variants.

Secondary Keys Only

The primary key in MyRocks is always the clustered key, that is, the index record is THE table record and so it's not possible to do "index only" because there isn't anything that is not in the primary key's (Key,Value) pair.

Secondary keys may or may not support index-only scans, depending on the datatypes of the columns that the query is trying to read.

Background: Mem-Comparable Keys

MyRocks indexes store "mem-comparable keys" (that is, the key values are compared with memcmp). For some datatypes, it is easily possible to convert between the column value and its mem-comparable form, while for others the conversion is one-way.

For example, in case-insensitive collations capital and regular letters are considered identical, i.e. 'c' ='C'. For some datatypes, MyRocks stores some extra data which allows it to restore the original value back. (For the latin1_general_ci collation and character 'c', for example, it will store one bit which says whether the original value was a small 'c' or a capital letter 'C'). This doesn't work for all datatypes, though.

Index-Only Support for Various Datatypes

Index-only scans are supported for numeric and date/time datatypes. For CHAR and VAR[CHAR], it depends on which collation is used, see below for details.

Index-only scans are currently not supported for less frequently used datatypes, like

  • BIT(n)
  • SET(...)
  • ENUM(...) It is actually possible to add support for those, feel free to write a patch or at least make a case why a particular datatype is important

Index-Only Support for Various Collations

As far as Index-only support is concerned, MyRocks distinguishes three kinds of collations:

1. Binary (Reversible) Collations

These are binary, latin1_bin, and utf8_bin.

For these collations, it is possible to convert a value back from its mem-comparable form. Hence, one can restore the original value back from its index record, and index-only scans are supported.

2. Restorable Collations

These are collations where one can store some extra information which helps to restore the original value.

Criteria (from storage/rocksdb/rdb_datadic.cc, rdb_is_collation_supported()) are:

  • The charset should use 1-byte characters (so, unicode-based collations are not included)
  • strxfrm(1 byte) = {one 1-byte weight value always}
  • no binary sorting
  • PAD attribute

The examples are: latin1_general_ci, latin1_general_cs, latin1_swedish_ci, etc.

Index-only scans are supported for these collations.

3. All Other Collations

For these collations, there is no known way to restore the value from its mem-comparable form, and so index-only scans are not supported.

MyRocks needs to fetch the clustered PK record to get the field value.

Covering Secondary Key Lookups for VARCHARs

TODO: there is also this optimization:

https://github.com/facebook/mysql-5.6/issues/303 https://github.com/facebook/mysql-5.6/commit/f349c95848e92b5b27b44f0e57194100eb0997e7

document it.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.