In the sciences and elsewhere, the use of relational databases has become ubiquitous. An important challenge is finding hot spots of user interests. In principle, one can discover user interests by clustering the queries in the query log. Such a clustering requires a notion of query similarity. This, in turn, raises the question of what features of SQL queries are meaningful. We have studied the query representations proposed in the literature and corresponding similarity functions and have identified shortcomings of all of them. To overcome these limitations, we propose new similarity functions for SQL queries. They rely on the so-called access area of a query and, more specifically, on the overlap and the closeness of the access areas. We have carried out experiments systematically to compare the various similarity functions described in this article. The first series of experiments measures the quality of clustering and compares it to a ground truth. In the second series, we focus on the query log from the well-known SkyServer database. Here, a domain expert has interpreted various clusters by hand. We conclude that clusters obtained with our new measures of similarity seem to be good indicators of user interests.

Today, many scientific data sets are open to the public. For their owners, it is important to know what the information needs of the users are. In this paper, we study the problem of extracting and analyzing patterns from the query log of a database. We focus on design errors (antipatterns). Antipatterns do not only have a negative effect on query performance, they also might introduce bias on any subsequent analysis of the SQL log. We propose a framework to discover patterns and antipatterns in SQL query logs and to clean antipatterns. To study the usefulness of our approach and to reveal insights on antipatterns in logs of real-world systems, we examine the SQL log of the SkyServer project, with more than 40 million queries. Among the top 15 patterns, we found 6 antipatterns. Altogether, our results give way to the conclusion that antipatterns might falsify refactoring and any other downstream analyses.

Today, many scientific data sets are open to the public. For their operators, it is important to know what the users are interested in. In this paper, we study the problem of extracting and analyzing patterns from the query log of a database. We focus on design errors (antipatterns), which typically lead to unnecessary SQL statements. Such antipatterns do not only have a negative effect on performance. They also introduce bias on any subsequent analysis of the SQL log. We propose a framework designed to discover patterns and antipatterns in arbitrary SQL query logs and to clean antipatterns. To study the usefulness of our approach and to reveal insights regarding the existence of antipatterns in real-world systems, we examine the SQL log of the SkyServer project, containing more than 40 million queries. Among the top 15 patterns, we have found six antipatterns. This result as well as other ones gives way to the conclusion that antipatterns might falsify refactoring and any other downstream analyses.