First, and foremost, if you are not using InnoDB, close this question, rebuild with InnoDB, then see if you need to re-open the question. MyISAM is not preferred and should not be discussed. How did you build the indexes in MySQL? There are several ways to explicitly or implicitly build indexes; they lead to better … Read more
The author of the SQLite implementation has disclaimed copyright. If LGPL is okay, then maybe you could use GNUpdate’s implementation?
https://web.archive.org/web/20161221112438/http://www.toadworld.com/platforms/oracle/w/wiki/11001.oracle-b-tree-index-from-the-concept-to-internals Notes: Databases do not directly implement indexes based on B-tree but on a variant called B+ tree. Which according to wikipedia: A B+ tree can be viewed as a B-tree in which each node contains only keys (not key-value pairs), and to which an additional level is added at the bottom with linked leaves. … Read more
First off, depending on the Storage Engine used, you may just not have a choice (InnoDB for example is exclusively using BTREE for its index). Also, BTREE is the default index type for most storage engines. Now… There are cases, when using alternative index types may result in improved performance. There are (relatively rare case) … Read more
Algorithmic complexity is the same, since O(logb n) = O(c log n) = O(log n) but the constant factors, which are hidden in big-O notation, could vary noticeably, depending on implementation and hardware. B-trees were designed for platter hard disks, which have a large access time (moving the head into position) after which an entire … Read more
AVL trees are intended for in-memory use, where random access is relatively cheap. B-trees are better suited for disk-backed storage, because they group a larger number of keys into each node to minimize the number of seeks required by a read or write operation. (This is why B-trees are often used in file systems and … Read more
A red/black tree is more or less equivalent to a 2-3-4 tree, which is just a type of B-tree. The worst-case performance is identical, provided you do a binary search of the B-tree node values. The obvious disadvantage of a B-tree is wasted space, but depending on the language/memory allocator used, you may find that … Read more
Take this with a pinch of salt: B-tree when you’re managing more than thousands of items and you’re paging them from a disk or some slow storage medium. RB tree when you’re doing fairly frequent inserts, deletes and retrievals on the tree. AVL tree when your inserts and deletes are infrequent relative to your retrievals.
You can only access elements by their primary key in a hashtable. This is faster than with a tree algorithm (O(1) instead of log(n)), but you cannot select ranges (everything in between x and y). Tree algorithms support this in Log(n) whereas hash indexes can result in a full table scan O(n). Also the constant … Read more