How database indexing works internally
The article claims to talk about database indexing but as soon as it goes into any sort of details the information only applies to mysql.<p>&gt; So here, indexing comes into picture. What indexing does is, sets up the column your search conditions are on, in a sorted order to assist in optimising query performance.<p>That&#x27;s mysql specific, and not just that but it&#x27;s <i>innodb</i> specific: innodb uses the primary key as a clustering index, or the first UNIQUE index if there&#x27;s no PK.<p>Other engines don&#x27;t usually do that, some (e.g. postgres) don&#x27;t even have clustered indexes (aka indexes which affect the table&#x27;s own layout), in postgres clustering is an explicit discrete table-rewriting operation.<p>And of course a table can have multiple indices, so it&#x27;s not possible for an index to always determine the table order. In databases which support clustered indices, only one index per table can be clustered.<p>&gt; The major advantage of B-tree is that the data in it is sortable<p>That&#x27;s… not really true. A b-tree is sorted, period. You can&#x27;t have an unordered btree.<p>&gt; What are other types of indexes?<p>There are tons of index types not mentioned by the article e.g. BRIN, GIN, GiST<p>&gt; For example, suppose you want to find out all of the companies which has company_id less than 40. How could you do that with a hash table index? Well, it’s not possible because a hash table is only good for looking up key value pairs – which means queries that check for equality (like “WHERE company_id = 18”).<p>And because btrees naturally handle &quot;range&quot; queries, they also handle <i>prefix</i> queries well. After all, &quot;foo*&quot; is just looking for every value between &quot;foo&quot; included and &quot;fop&quot; excluded.