How do composite indexes work?

Question

I created composite indexes (indexes for you, math people) on the tables before, with the assumption of how they work. I was just curious if my assumption is correct or not.

I assume that when you specify the column order for the index, you also specify how the indexes will be grouped. For example, if you have columns a , b and c , and you specify the index in the same order as a ASC , b ASC and c ASC , then the resulting index will essentially have many indexes for each “group” in a .

It is right? If not, what will be the actual result?

Answer 1

Composite indexes work exactly like regular indexes, except that they have multi-valued keys.

If you specify an index in the fields (a, b, c), the entries are sorted first by a, then b, then c.

Example:

 | A | B | C | ------------- | 1 | 2 | 3 | | 1 | 4 | 2 | | 1 | 4 | 4 | | 2 | 3 | 5 | | 2 | 4 | 4 | | 2 | 4 | 5 | 

Answer 2

A component index is similar to a regular alphabetical index in a dictionary, but spans two or more letters, for example:

 AA - page 1 AB - page 12 

and etc.

Rows of the table are ordered first by the first column in the index, then the second, etc.

Used when searching on both columns OR on the first column. If your index looks like this:

 AA - page 1 AB - page 12 … AZ - page 245 BA - page 246 … 

you can use it to search by 2 letters ( = 2 columns in the table) or as a simple index for a single letter:

 A - page 1 B - page 246 … 

Note that in the case of a dictionary, the pages themselves are sorted alphabetically. This is an example of a CLUSTERED index.

In the regular CLUSTERED index CLUSTERED links to pages are arranged, as in the history book:

 Gaul, Alesia: pages 12, 56, 78 Gaul, Augustodonum Aeduorum: page 145 … Gaul, Vellaunodunum: page 24 Egypt, Alexandria: pages 56, 194, 213, 234, 267 

Composite indexes can also be used if you are ORDER BY two or more columns. In this case, the DESC clause may come in handy.

See this blog post about using the DESC clause in a composite index:

• Descending Indexes

Answer 3

The most common index implementation uses B-trees, which allow a quick search, as well as a fairly fast range scan. This is too much to explain here, but here is a Wikipedia article on B-trees . And you're right, the first column that you declare in the creation index will be a high order column in the resulting B-tree.

A search in a high-order column comes down to scanning the range, and the B-tree index can be very useful for such a search. The easiest way to see this is by analogy with the old card catalogs that you have in libraries that have not yet been converted to online catalogs.

If you are looking for all the cards for authors whose last name is "Clemens", you simply go to the authors directory and very quickly find a box with the inscription "CLE-CLI" on the front panel. This is the right box. Now you are doing some informal binary search in this box to quickly find all the cards that say "Clemens, Roger" or "Clemens, Samuel."

But suppose you want to find all the cards for the authors whose name is Samuel. Now you are driving, because these cards are not collected in one place in the Author directory. A similar phenomenon occurs with composite indexes in the database.

Different DBMSs differ in how smart their optimizers are when they detect a scan of a range of indices and accurately evaluate their cost. And not all indexes are B-trees. You will need to read the documents for your specific DBMS in order to get real information.

Answer 4

No. The resulting index will be the only index, but with a composite key.

KeyX = A, B, C, D; KeyY = 1,2,3,4;

Index KeyX, KeyY will actually be: A1, A2, A3, B1, B3, C3, C4, D2

So, if you need to find something with KeyX and KeyY, it will be fast and will use a single index. Something like SELECT ... WHERE KeyX = "B" AND KeyY = 3.

But it’s important to understand: WHERE KeyX =? queries will use this index, and WHERE KeyY =? will NOT use such an index at all.

Answer 5

I understand that composite indexes work just like regular indexes, except that they have multi-valued keys. If you define an index in the fields (a, b, c), since the composite index will be stored in BinaryTree, so your index will only work after combinations of searches.

 ABC AB A 

For example, creating a composite index for fields a, b, and c is equivalent to creating separate indexes for a, ab, and abc.