What are covering, Composite and compound compared to Dexes?

Question

I’m reading on Left in Mysql and I came up with three terms that I can’t quite understand that are: covering, Composite and compound. These terms are strongly linked to the use of Dexes, however, I cannot understand what their meanings and objectives are, and what impacts they have on the creation of Dexes.

Therefore, I would like to know what is covering, Composite and compound and what are their differences with each one?

Answer 1

First understand how to build an index. You create a binary search tree (search the basic concept to know what it is), which is usually a B-tree (which is a more complicated type to understand so you might not want to bother, it was designed to minimize the cost of rebalancing the tree when it undergoes many changes, so it is used in databases and file systems, among others).

You populate this tree so that you can quickly search for information related to the elements that have been populated in it, which is what search trees are for (for example, you concatenate (linking) name and surname of a table people and populating in this tree the concatenations, so when you need to find information related to someone with name "Cat" and surname "Silva" will be able to find it fast (by the way the search is done in this data structure).

Note that order is important, you can find fast "Gatosilva" but not "Silvagato". As the order is respected when traversing the tree nodes, by the way the search is done if you only have the name "Cat" you will still be able to take advantage of the tree to restrict the search space (which will restrict to "Gatomendes", "Gatosilva", etc).

Well, let’s get down to brass tacks.

Composite was a wrong use when it meant compound, that’s all. AR uses the term but points out the error later. Compound is the past of the verb compose, in English (something to be composed by others). Composite passes the same idea of composition but is noun (chemical compound, design Composite Pattern). Then who asked the question got confused.

Compound index is compound index, that is, what is not the simple (a single column). That is, the compound has its elements generated from more than one column and is constructed based on the concatenation of the values of these columns in a certain order defined in the index statement and then populated in a search tree.

Therefore, again: an index composed by the columns name and surname will optimize (help to find faster) the search for a record that searches columns in that order, already if searching by surname and name will not help, because it is not the order that the elements were populated when building the search tree (a simple analogy to this limitation, only that reversing the importance of the order for surname and name, is a phone book (is it from your time? kkk) which is ordered by surnames, i.e., "Silva Gato", "Silva Henrique", etc. So in this case it will not work if you try to use it to find someone by first name, but by last name before).

On the other hand, returning to the first example (and uninpouring in your head rs, now the important first is the name), being the name the first column, it is still possible to use the composite index to optimize (expedite) a query containing only the name "Cat". I don’t know if you have a term for this, I think the Mysql explain will show "using index" ("using index").

Finally, as I understood from the answer, a covering index (I didn’t know the term) is an index that covers (cover) all the fields of the query, and so you don’t have to go through the table to find the information, only the index. I think the term is being explained wrong in the answer, it would not be a proper term that means "index cover" but simply "covering the index" (covering the index, something in this intention).