Optimizing MySQL Searches with "like" and Wildcards Without Compromising Indexing

In the realm of database optimization, queries utilizing "like" operators with leading wildcards, such as "SELECT * FROM sometable WHERE somefield LIKE '%value%'," often pose challenges for index utilization. This article explores a novel approach to optimize such queries while preserving the benefits of index-based lookups.

Prefix Suffix Indexing

The key to resolving the indexing issue lies in decomposing strings into their constituent suffixes. For instance, the string "value" can be broken down into "value," "alue," "lue," "ue," and "e." By storing these suffixes in the database and leveraging an index on the new column, queries can efficiently search for substrings without the hindrance of leading wildcards.

Storage Considerations

The trade-off for this approach lies in storage space. The number of characters required for storing a word's suffixes increases quadratically with its length. However, if storage is not a significant constraint, this method offers a robust solution for optimizing substring searches.

Consider the following example:

Limitations and Considerations

While not a perfect solution, prefix suffix indexing offers several advantages. It ensures that words can be found as both wholes and parts, facilitating the retrieval of fragments and full strings with flexibility. Additionally, it avoids the need for full-text searching, which may not be suitable when field values are not purely text-based.

However, when dealing with compound words or hyphenated phrases, it's crucial to strike a balance between storage efficiency and the ability to retain compound entities. Removing hyphens or decomposing compound words into their individual components may compromise their integrity in certain scenarios.

Furthermore, efficient storage techniques for suffix arrays are still being explored in the context of databases. Nevertheless, the approach presented in this article provides a practical method for optimizing "like" queries with leading wildcards.