Estimating the selectivity of LIKE queries using pattern-based histograms

Abstract

Accurate cost and time estimation of a query is one of the major success indicators for database management systems. SQL allows the expression of flexible queries on text-formatted data. The LIKE operator is used to search for a specified pattern (e.g., LIKE "luck%") in a string database. It is vital to estimate the selectivity of such flexible predicates for the query optimizer to choose an efficient execution plan. In this paper, we study the problem of estimating the selectivity of a LIKE query predicate over a bag of strings. We propose a new type of pattern-based histogram structure to summarize the data distribution in a particular column. More specifically, we first mine sequential patterns over a given string database and then construct a special histogram out of the mined patterns. During query optimization time, pattern-based histograms are exploited to estimate the selectivity of a LIKE predicate. The experimental results on a real dataset from DBLP show that the proposed technique outperforms the state of the art for generic LIKE queries like %s1%s2%...%sn% where si represents one or more characters. What is more, the proposed histogram structure requires more than two orders of magnitude smaller memory space, and the estimation time is almost an order of magnitude less in comparison to the state of the art.

Using positional sequence patterns to estimate the selectivity of SQL LIKE queries

“…Recently, Aytimur and Çakmak (2018) propose a novel approach, called SPH. SPH first mines all frequent closed sequence patterns by using an existing sequence mining algorithm (Lee et al 2004).…”

“…Out of the mined positional sequence patterns, a histogram is built in a similar way as in SPH (Aytimur & Çakmak, 2018) with some extensions. We first briefly explain the histogram building steps (please refer to (Aytimur & Çakmak, 2018) for details), and then present our extensions to eliminate redundant endpoints.…”

“…As an extension to SPH (Aytimur & Çakmak, 2018), we eliminate such patterns based on pattern containment and information theoretic filtering as formally defined next.…”

“…The partitioning-based matching is placed after the encapsulated match case in the above list of attempted matches in the original SPH (Aytimur & Çakmak, 2018) approach. That is, if the exact and encapsulated match is not possible, then the partitioning-based match is explored.…”

“…We recently proposed an algorithm, called SPH, to predict the selectivity of LIKE query predicates (Aytimur & Çakmak, 2018). In SPH approach, first, frequent sequence patterns are computed from the database before query time.…”