2

In SQL Server you can create an index and have additional columns included on that index that can help performance in certain circumstances. Is that same ability available for mysql or mariadb under a different name, perhaps? I was unable to find anything using the term "included columns".

5

Looking at the MySQL Documentation, the glossary indicates this about Covering Indexes:

An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O. InnoDB can apply this optimization technique to more indexes than MyISAM can, because InnoDB secondary indexes also include the primary key columns. InnoDB cannot apply this technique for queries against tables modified by a transaction, until that transaction ends.

Any column index or composite index could act as a covering index, given the right query. Design your indexes and queries to take advantage of this optimization technique wherever possible.

The implication here is that there is no direct equivalent of an INCLUDE statement in MySQL, however if the index provides coverage of the columns involved in an index, it can, rather obviously, still be considered to be covering, much like an index with included columns in SQL Server would be.

MariaDB has the following in their documentation for covering indexes:

A "Covering" index is an index that contains all the columns in the SELECT. It is special in that the SELECT can be completed by looking only at the INDEX BTree. (Since InnoDB's PRIMARY KEY is clustered with the data, "covering" is of no benefit when considering at the PRIMARY KEY.)

Mini-cookbook:

  1. Gather the list of column(s) according to the "Algorithm", above.
  2. Add to the end of the list the rest of the columns seen in the SELECT, in any order.

Examples:

SELECT x FROM t WHERE y = 5; ⇒ INDEX(y,x) -- The algorithm said just INDEX(y)  
SELECT x,z FROM t WHERE y = 5 AND q = 7; ⇒ INDEX(y,q,x,z) -- y and q in either order (Algorithm), then x and z in either order (covering).  
SELECT x FROM t WHERE y > 5 AND q > 7; ⇒ INDEX(y,q,x) -- y or q first (that's as far as the Algorithm goes), then the other two fields afterwards.   

The speedup you get might be minor, or it might be spectacular; it is hard to predict.

But...

  • It is not wise to build an index with lots of columns. Let's cut it off at 5 (Rule of Thumb).
  • Prefix indexes cannot 'cover', so don't use them anywhere in a 'covering' index.
  • There are limits (3KB?) on how 'wide' an index can be, so "covering" may not be possible.
2
  • 2
    The INCLUDE feature is usually used together with unique indexes, so that you can get still get a "covering index" even though the included column is not part of the unique constraint. Oct 30 '20 at 13:16
  • @a_horse - agreed. There are many ways in which an index with INCLUDEd columns is prefferrable over simply adding all the columns as index keys. Least not of which would be index key size.
    – Hannah Vernon
    Oct 30 '20 at 14:13
3

MySQL does not support included columns.

However, if you use the InnoDB storage engine (which is the default), all columns are physically included in the primary key. In other words, the primary key is the table. If you define the primary key as PRIMARY KEY (id) the rows will be physically ordered by id; but for all queries that use the primary key, the primary key will be a covering index.

The following query uses the primary key as a covered index:

SELECT * FROM tab WHERE pk = 10;

As Akina pointed out in a comment, InnoDB secondary indexes include a reference to the primary key. So if you index the email fiels, in the index you'll have all emails in order, and for each you'll have the corresponding id. So for each secondary index the primary key column(s) are included column(s).

The following query uses a secondary index as a covering index:

SELECT indexed_column, pk FROM tab WHERE indexed_column=100;

As a curiosity, I can also tell you that the TokuDB storage engine supports clustering indexes, which is just another name for included columns. Unfortunately TokuDB is not maintained anymore by its vendor, Percona.

4
  • if you use the InnoDB storage engine (which is the default), all columns are physically included in the primary key. 1) Not primary but clustered (CI) - when PK exists it is CI, when no PK in a table then 1st UNIQUE index if exists is CI, when no unique indices then inner row number is used as CI. 2) ... and each index includes clustered index reference (and hense all columns included in CI, so each index includes all CI columns).
    – Akina
    Oct 30 '20 at 6:12
  • If the clustered index is an autogenerated id it has nothing to do with this question, because there is no way for the user to use it explicitally. A UNIQUE key could be used as clustered index (remember, it must also be NOT NULL) but relying on this is not good PKs should be explicit. Your second poing is correct. Oct 30 '20 at 9:06
  • @FedericoRazzoli - ORDER BY id DESC is an explicit use? In that case, I like to deliberately include id in the secondary index to give a clue that I am depending on id being in the index.
    – Rick James
    Oct 30 '20 at 17:03
  • @RickJames the only drawback I see is that this will pollute pt-duplicate-key-checker output (or is there a way to tell the tool not to list indexes that end with the PK?). I prefer to use comments for that. Oct 30 '20 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.