Revolutionizing Database Performance: AI-Powered Cardinality Estimation for LIKE Queries

      Optimizing database performance is a constant challenge for enterprises, especially as data volumes grow and queries become more complex. At the heart of this optimization lies a critical component: the query optimizer. To efficiently process information, these optimizers rely heavily on accurate "cardinality estimation," which predicts how many rows a given query will return. This is particularly challenging for `LIKE` queries, which use pattern matching to find strings, often leading to performance bottlenecks. A breakthrough system, LEARNT, offers a practical estimator for the cardinality of `LIKE` queries, providing formal accuracy guarantees and significantly outperforming existing methods.

The Foundation of Efficient Databases: Cardinality Estimation

      For any modern database system, executing a query isn't just about finding the right data; it's also about finding it as quickly and cost-effectively as possible. This is where the query optimizer steps in, acting as the database's strategic brain. Before fetching any data, the optimizer considers various execution plans and selects the one it believes will be fastest. Its decisions are heavily influenced by "cardinality estimation" – an educated guess at how many records a specific part of a query will match. If this estimate is wrong, the optimizer might choose a suboptimal plan, leading to slow query execution, wasted computing resources, and frustrated users.

      String data, ubiquitous in modern applications from customer names to product descriptions, often involves `LIKE` queries. These queries use wildcard characters (like `%`) to match patterns, such as prefixes (`S%`), suffixes (`%S`), or substrings (`%S%`). While seemingly straightforward, accurately estimating the cardinality for these flexible patterns is far more complex than for exact matches. Statistics from various real-world benchmarks, such as JOB and TPC-H, reveal that these prefix, suffix, and substring patterns are extremely common, making their accurate estimation crucial for overall database health.

Unpacking the Challenges of ‘LIKE’ Queries

      Traditional and even many existing AI-powered methods struggle with the unique complexities of `LIKE` queries. The nature of string data distribution, the wide range of possible query patterns, and the varying lengths of strings make consistent and accurate predictions difficult. Several critical gaps have been identified in current approaches, often leading to unreliable database performance:

• Inconsistent Accuracy: Many estimators struggle to maintain high accuracy across the full spectrum of query cardinalities (from queries returning very few results to those returning many) and different string lengths. This inconsistency can lead to suboptimal or even catastrophic query plans.
• Lack of Robustness and Formal Guarantees: Existing methods often fail to provide formal error bounds, meaning individual estimates could be arbitrarily far from the true value. This lack of robustness makes it risky to rely on them for mission-critical operations.
• Unreliable Empty-Answer Handling: Queries that return zero results (empty-answer queries) are frequently overlooked. Incorrectly identifying an empty-answer query can lead to unnecessary resource allocation and wasted processing time. For example, some systems might mistakenly process data for a query that has no matches, draining compute cycles.
• High Overheads: Many current estimators demand significant computational resources for setup (preprocessing time) or consume excessive memory at inference time, making them impractical for production environments where efficiency is paramount. For example, some methods require hours to prepare data or consume tens of megabytes of memory for each inference.

Introducing LEARNT: A New Paradigm for LIKE Query Estimation

      To address these fundamental issues, researchers proposed LEARNT, a `LIKE` query Estimator designed with a focus on Accuracy, Robustness, Negligible overhead, Tunability, and Theoretical guarantees. LEARNT redefines cardinality estimation for non-empty queries by framing it as a "bucket classification" problem. Instead of directly predicting an exact number, it assigns queries to predefined "buckets," each representing a range of cardinalities. This approach allows for formal bounds on estimation error if a query is correctly classified into its bucket, offering a level of reliability previously unavailable. The user can even specify an acceptable error bound to fine-tune the balance between accuracy and memory footprint.

      At its core, LEARNT uses a sophisticated "bucketed layered-filter architecture." Each bucket employs a multi-layer structure of Bloom filters—a memory-efficient probabilistic data structure that quickly checks if an element might be in a set, filtering out definite non-members—and a compact auxiliary table to eliminate any remaining false positives. This combination ensures accurate bucket assignment with minimal storage and computational demands. To further reduce memory, LEARNT intelligently exploits empirical skew in query distributions, avoiding unnecessary data storage. This intelligent architectural design is akin to how ARSA Technology develops its AI Video Analytics solutions, which process vast amounts of data efficiently at the edge.

Handling Nuances: Empty-Answer and Arbitrarily Long Queries

      A crucial innovation of LEARNT lies in its robust handling of often-neglected scenarios. For "empty-answer queries"—those that legitimately return no matching strings—LEARNT incorporates specialized filter-based and prefix-walk strategies. These dedicated mechanisms provide probabilistic guarantees for correctly identifying when a query will yield no results, preventing the database from expending resources on fruitless searches. This conservative approach is vital for maintaining system efficiency and avoiding unnecessary processing delays.

      Furthermore, `LIKE` queries are not always short; they can involve arbitrarily long string patterns. To manage this, LEARNT extends its capabilities with a "Markov modeling scheme." This statistical technique allows the system to compose statistics from shorter query patterns to generate accurate estimates for much longer and more complex queries. This adaptability ensures that LEARNT remains effective regardless of query length, offering a versatile solution for diverse data environments.

Behind the Performance: Technical Innovations and Real-World Impact

      LEARNT’s internal optimizations and design principles contribute to its superior performance. Its memory-efficient bucketed layered-filter architecture, combined with techniques that exploit query distribution skew, minimizes storage requirements. The classification-based formulation reduces the complexity of direct regression, making estimations more robust and accurate. This is particularly relevant for modern `edge AI systems` where computational resources are limited but real-time processing is essential. ARSA's AI Box Series, for example, embodies this edge AI philosophy by providing pre-configured systems that process data locally for rapid, on-site deployment.

      Extensive experiments conducted on four diverse, real-world datasets demonstrate LEARNT’s significant advantages over state-of-the-art methods like CLIQUE and LPLM, as detailed in the paper "LEARNT: A Practical Estimator for Cardinality of LIKE Queries with Formal Accuracy Guarantees" by Lan et al. (2020), published in PVLDB, and available at https://arxiv.org/abs/2605.24308. The findings are compelling:

• Higher Accuracy: LEARNT consistently achieved 1.3–1.7 times lower mean Q-error (a standard metric for estimation error), indicating more precise cardinality predictions across the board.
• Improved Robustness: It showed significantly lower tail errors, meaning it avoids catastrophic estimation mistakes that can severely derail query optimizers.
• Faster Construction: LEARNT boasts up to 70 times faster construction times compared to competitors, making it much quicker to deploy and integrate into existing database systems.
• Comparable Memory Usage: These performance gains were achieved while maintaining memory usage comparable to other leading methods, making it practical for large-scale deployments.

      By delivering more accurate and robust cardinality estimates, LEARNT empowers database systems to make smarter decisions, leading to faster query execution, better resource utilization, and ultimately, enhanced operational efficiency and reduced costs for enterprises. This level of precision and performance is critical for companies relying on robust data infrastructure, much like the demanding environments ARSA has experienced since 2018 in various industries.

      In the realm of AI and database management, innovations like LEARNT highlight the ongoing evolution toward more intelligent and efficient systems. For organizations seeking to unlock peak performance from their data infrastructure, understanding and adopting such advanced estimation techniques is key.

      Ready to enhance your enterprise's data processing and operational efficiency with cutting-edge AI and IoT solutions? Explore ARSA Technology's innovative products and services, and contact ARSA for a free consultation to discuss your specific needs.