Hey. Interested in what is B-TREE , HASH indexes? How do they affect sample acceleration? What is their syntax?

    2 answers 2

    B-tree is also a balanced tree index, an index grouped by the leaves of a balanced tree. It is used for large indices, in fact it is an index of indices. Well, let's say the indices with a value from 1 to 10 are stored in one branch, from 11 to 20 in another, etc., when a request comes in for index number 35, we go to the 3rd branch and find the 5th element there. In general, something like this. Read more here.

    Hash index is used to compare / build indexes of string and / or binary data. Each value of the indexed expression is mapped to the value of a certain hash function that displays the original value to an integer (sometimes to a string). Read more here.

    B-Tree index gives the sampling rate of the order of log (N), hash gives linear. In real life, hash and B-Tree are used together, that is, hashes are still used to calculate the B-Tree index values.

    • 2
      > B-tree aka binary tree index This is not true. B - balanced - here means balanced, in other words, each leaf of a tree has the same number of ancestors. As a result, access to any data using such an index is accomplished in the same number of steps. - msi
    • Grateful for the links .. - zloctb
    • 3
      @Barmaley why does hash give linear velocity? For some simplicity of my soul, I thought that O (1) should be there, if conflicts should not be taken into account. - alexlz
    • @alexlz you're right. Linear indexing speed, and the search speed is of course constant - Barmaley

    Let you have a table

     goods ( id int primary key, catalog_id int, sales_from datetime, sales_till datetime )

    If you have Engine InnoDb or MyISAM, then the primary index is only BTREE, if the table type is MEMORY, then the primary index is by default HASH, but you can also assign BTREE. For ndb type, BTREE and HASH can be used.

    If you have frequent requests, such as:

      SELECT * FROM goods WHERE catalog_id = ...

    it makes sense to make the HASH index in the catalog_id field

    If you want to find products for which discounts currently exist, i.e. condition:

     WHERE sales_from > NOW() AND sales_till < NOW()

    then definitely need to use the BTREE index

    • +1 for such an important example of the actual use of different types of storage indexes. - voipp