Member-only story
Understanding String Comparison of Databases
Three approaches to speed up string comparison
In the previous article, we briefly talked about how B-tree-based databases use partial indexes to improve performance. In this article, we will talk about another story of indexing.
Generally speaking, B-tree-based database indexes have one characteristic, which is leftmost matching. Take MySQL for example, if the WHERE condition is a = 1 AND b = 2 AND c = "Hello" then the index must be built in this order, i. e. (a, b, c).
In addition, if the index is (a, b, c, d), then the same query can be applied based on the leftmost match principle.
Furthermore, if the condition we want to query is a string match in the c field, for example, WHERE a = 1 AND b = 2 AND c LIKE "Hello%", such a condition can still apply to the (a, b, c) index. Because of the leftmost match rule, even if LIKE is used, the leftmost edge of the string is already specified, so it still meets the rule.
On the contrary, if the query condition becomes WHERE a = 1 AND b = 2 AND c LIKE "%World", then only the a and b fields can be indexed, while the c field must compare all rows matched (a, b) values, which will use a lot of CPU computation of the database, in other words, very resource consuming.
