Selection Queries Research Articles

A Performance Benchmark for the PostgreSQL and MySQL Databases

This study highlights the necessity for efficient database management in continuous authentication systems, which rely on large-scale behavioral biometric data such as keystroke patterns. A benchmarking framework was developed to evaluate the PostgreSQL and MySQL databases, minimizing repetitive coding through configurable functions and variables. The methodology involved experiments assessing select and insert queries under primary and complex conditions, simulating real-world scenarios. Our quantified results show PostgreSQL’s superior performance in select operations. In primary tests, PostgreSQL’s execution time for 1 million records ranged from 0.6 ms to 0.8 ms, while MySQL’s ranged from 9 ms to 12 ms, indicating that PostgreSQL is about 13 times faster. For select queries with a where clause, PostgreSQL required 0.09 ms to 0.13 ms compared to MySQL’s 0.9 ms to 1 ms, making it roughly 9 times more efficient. Insert operations were similar, with PostgreSQL at 0.0007 ms to 0.0014 ms and MySQL at 0.0010 ms to 0.0030 ms. In complex experiments with simultaneous operations, PostgreSQL maintained stable performance (0.7 ms to 0.9 ms for select queries during inserts), while MySQL’s performance degraded significantly (7 ms to 13 ms). These findings underscore PostgreSQL’s suitability for environments requiring low data latency and robust concurrent processing capabilities, making it ideal for continuous authentication systems.

Secure Range-Searching Using Copy-And-Recurse

Range searching is the problem of preprocessing a set of points P, such that given a query range gamma we can efficiently compute some function f(P cap gamma). For example, in a 1 dimensional range counting query, P is a set of numbers, gamma is a segment and we need to count how many numbers of P are in gamma. In higher dimensions, P is a set of d dimensional points and the query range is some volume in R^d. In general, we want to compute more than just counting, for example, the average of P cap gamma. Range searching has applications in databases where some SELECT queries can be translated to range queries. It had received a lot of attention in computational geometry where a data structure called partition tree was shown to solve range queries in time sub-linear in |P| using space only linear in |P|. In this paper we consider partition trees under FHE where we answer range queries without learning the value of the points or the parameters of the range. We show how partition trees can be securely traversed with O(t n^{1-1/d+epsilon} + n^{1+epsilon}) operations, where n=|P|, t is the number of operations needed to compare to gamma and epsilon>0 is a parameter. When the ranges are axis-parallel hyper-boxes the running time is O(t n^epsilon + n log^{d-1} n). As far as we know, this is the first non-trivial bound on range searching under FHE and it improves over the naive solution that needs O(t n) operations. Our algorithms are independent of the encryption scheme but as an example we implemented them using the CKKS FHE scheme. Our experiments show that for databases of sizes 2^{23} and 2^{25}, our algorithms run x2.8 and x4.7 (respectively) faster than the naive algorithm. The improvement of our algorithm comes from a method we call copy-and-recurse. With it we efficiently traverse a r-ary tree (where each inner node has r children) that also has the property that at most xi of them need to be recursed into when traversing the tree. We believe this method is interesting in its own and can be used to improve traversals in other tree-like structures.

Paweł Sobolewski i jego "Poland" – pierwsze polonijne wydawnictwo periodyczne w USA z 1842 r.

The history of the magazine Poland is outlined against the background of the conditions faced by Polish exiles deported from Austria to the USA in March 1834. After a short period of support from American society, they were left to their own entrepreneurship. Most of them came from the nobility and did not know English, which significantly hindered finding employment. This article focuses on the figures of Paweł Sobolewski and Eustachy Wyszyński – exiles who managed to adapt to the unfamiliar conditions. Their biographies and achievements are brought closer; especially Sobolewski, who gained fame as a poet and the editor and publisher of the first English-language magazine „Poland,” dealing with Polish history and culture. The article details the circumstances of the periodical’s edition and its form and content based on copies preserved in the National Library in Warsaw. The second goal of the article is an attempt to collect Sobolewski’s literary achievements, scattered in American press. As a result of a selective query, over twenty of his works published in contemporary journals were compiled, indicating the titles of publications in which they were included, with the reservation that this list is not complete. A more comprehensive query of contemporary press in the USA may uncover new, previously unknown works of the editor of „Poland” and fill gaps in his biography.

DITA: DETR with improved queries for end-to-end temporal action detection

The DEtection TRansformer (DETR), with its elegant architecture and set prediction, has revolutionized object detection. However, DETR-like models have yet to achieve comparable success in temporal action detection (TAD). To address this gap, we introduce a series of improvements to the original DETR, proposing a new DETR-based model for TAD that achieves competitive performance relative to conventional TAD methods. Specifically, we adapt advanced techniques from DETR variants used in object detection, including deformable attention, denoising training, and selective query recollection. Furthermore, we propose several new techniques aimed at enhancing detection precision and model convergence speed, such as geographic query grouping and learnable proposals. Leveraging these innovations, we introduce a new model called DETR with Improved queries for Temporal Action Detection (DITA). DITA not only adheres to DETR’s elegant design philosophy but is also competitive to state-of-the-art action detection models. Remarkably, it is the first TAD model to achieve an mAP over 70% on THUMOS14, outperforming the previous best DETR variant by 13.5 percentage points.

PERBANDINGAN PERFORMANSI ANTARA MONGODB DAN MARIADB

Along with the use of information technology in almost all sectors of life, making databases has a very important role in being able to store and process large amounts of data effectively, and high performance is required when processing the database. The Nosql database is widely used in managing very large amounts of data besides that it is also an open source database, but so far many still use the mysql database which is an open source DBMS and has many communities. This paper will compare the performance between a relational database that stores structured data (Sql) and a database with a dynamic and unstructured schema (Nosql). Specifically the database being compared between MariaDB and MongoDB. An experiment will be carried out by performing a query insert, update, delete on the two databases, with the number of records of 500, 1000, 5000, 7500 and 10000, besides that, it is also tested for a select query without criteria, with 1 criterion and 2 criteria. And from the experiments conducted it can be concluded that for insert, update and delete queries on a single NoSQL table has a much better response time. Whereas the select query without NoSQL criteria also has a better response time, but to display all data with criteria, especially the two database criteria, the SQL version has a better response time than NoSQL.

Research on Location Privacy Protection Technology in Wireless Sensor Networks Based on Big Data

The digital footprint in wireless sensor networks can bring great academic and commercial value, but it will also bring the risk of privacy disclosure to users. This study discusses the location privacy protection methods in location based service under multiple scenarios. In the experiment, a false location filtering algorithm for real-time location request scenarios and a false path generation model for offline location release scenarios are proposed. The false position filtering algorithm is implemented based on the idea of a large top heap selection query. The algorithm can construct false position candidate sets and filter false positions. The false track generation model combines the false position technology and the generative adversarial networks model, which mainly protects the user’s track data by synthesizing tracks. In the attacker’s recognition experiment of a real location. The minimum distance between the false locations generated by the algorithm proposed in the study is above 400 m and the generation time does not exceed 5 ms, generating a better set of false locations in terms of both effectiveness and efficiency. Compared with several commonly used privacy-preserving algorithms, the proposed algorithm has the lowest probability of being identified with real locations, with no more than 21% overall, and is almost independent of the k value. the recognition accuracy of the trajectory user link task decreases from over 90% to about 34%, indicating that the proposed fake trajectory generation model can effectively protect users’ data privacy. The experimental results demonstrate that the algorithm and model proposed in the study can quickly generate physically dispersed and semantically diverse sets of fake locations and effectively protect users’ trajectory privacy, which is important for users’ digital footprint privacy protection.

Performance analysis of databases created in virtualized and containerized environment

Database systems are currently implemented on different environments - containerized and virtualized. This paper aimed to compare the performance of relational databases running on these two environments. Therefore, two research hypotheses were formulated. The first hypothesis assumed that databases running on Docker containers are more efficient than those on virtual machines. The second one assumed that the Oracle is the most efficient database regardless of which environment it was run on. MySQL, PostgreSQL, Microsoft SQL Server and Oracle databases were tested. The study measured the execution times of INSERT, UPDATE, DELETE and SELECT queries. Each test was repeated 100 times. It was stated that databases running on Docker containers outperform instances running on virtual machines. Furthermore, it was found that the PostgreSQL database have a definite advantage in performance over the rest, analysed databases, which allowed the second hypothesis to be rejected.

Selective Query Processing: A Risk-Sensitive Selection of Search Configurations

In information retrieval systems, search parameters are optimized to ensure high effectiveness based on a set of past searches, and these optimized parameters are then used as the search configuration for all subsequent queries. A better approach, however, would be to adapt the parameters to fit the query at hand. Selective query expansion is one such an approach, in which the system decides automatically whether or not to expand the query, resulting in two possible search configurations. This approach was extended recently to include many other parameters, leading to many possible search configurations where the system automatically selects the best configuration on a per-query basis. One problem with this approach is the system training, which requires evaluation of each training query with every possible configuration. In real-world systems, so many parameters and possible values must be evaluated that this approach is impractical, especially when the system must be updated frequently, as is the case for commercial search engines. In general, the more configurations, the greater the effectiveness when configuration selection is appropriate but also the greater the risk of decreasing effectiveness in the case of an inappropriate configuration selection. To determine the ideal configurations to be used for each query in real-world systems, we have developed a method in which a limited number of possible configurations are pre-selected, then used in a meta-search engine that decides the best search configuration for each query. We define a risk-sensitive approach for configuration pre-selection that considers the risk-reward tradeoff between the number of configurations kept and system effectiveness. We define two alternative risk functions to apply to different goals. For final configuration selection, the decision is based on query feature similarities. We compare two alternative risk functions on two query types (ad hoc and diversity) and compare these to more sophisticated machine learning based methods. We find that a relatively small number of configurations (20) selected by our risk-sensitive model is sufficient to obtain results close to the best achievable results for each query. Effectiveness is increased by about 15% according to the P@10 and nDCG@10 evaluation metrics when compared to traditional grid search using a single configuration and by about 20% when compared to learning to rank documents. Our risk-sensitive approach works for both diversity- and ad hoc oriented searches. Moreover, the similarity-based selection method outperforms the more sophisticated approaches. Thus, we demonstrate the feasibility of developing per-query information retrieval systems, which will guide future research in this direction.

Erica: Query Refinement for Diversity Constraint Satisfaction

Relational queries are commonly used to support decision making in critical domains like hiring and college admissions. For example, a college admissions officer may need to select a subset of the applicants for in-person interviews, who individually meet the qualification requirements (e.g., have a sufficiently high GPA) and are collectively demographically diverse (e.g., include a sufficient number of candidates of each gender and of each race). However, traditional relational queries only support selection conditions checked against each input tuple, and they do not support diversity conditions checked against multiple, possibly overlapping, groups of output tuples. To address this shortcoming, we present Erica, an interactive system that proposes minimal modifications for selection queries to have them satisfy constraints on the cardinalities of multiple groups in the result. We demonstrate the effectiveness of Erica using several real-life datasets and diversity requirements.

PAINE Demo: Optimizing Video Selection Queries with Commonsense Knowledge

Because video is becoming more popular and constitutes a major part of data collection, we have the need to process video selection queries --- selecting videos that contain target objects. However, a naïve scan of a video corpus without optimization would be extremely inefficient due to applying complex detectors to irrelevant videos. This demo presents Paine; a video query system that employs a novel index mechanism to optimize video selection queries via commonsense knowledge. Paine samples video frames to build an inexpensive lossy index, then leverages probabilistic models based on existing commonsense knowledge sources to capture the semantic-level correlation among video frames, thereby allowing Paine to predict the content of unindexed video. These models can predict which videos are likely to satisfy selection predicates so as to avoid Paine from processing irrelevant videos. We will demonstrate a system prototype of Paine for accelerating the processing of video selection queries, allowing VLDB'23 participants to use the Paine interface to run queries. Users can compare Paine with the baseline, the SCAN method.

A Study on Perception of Visual–Tactile and Color–Texture Features of Footwear Leather for Symmetric Shoes

The study applies Kansei engineering in analyzing the color and texture of leather footwear, utilizing neural network verification to mirror consumers’ visual and tactile imageries onto varieties of leather. This aids in the development of an advanced system for selecting leather footwear based on such impressions. Initially, representative word pairs denoting consumers’ visual and tactile perceptions of leather footwear were delineated. Post-evaluation of these perceptions through a sensibility assessment questionnaire was administered, using 54 samples of leather footwear provided by manufacturers, with each leather type codified in terms of visual and tactile sensibilities. Subsequently, a customized software algorithm was crafted to isolate the primary color and adhesiveness as color features from the leather sample images. Analyzing grayscale values of the images and using pixel neighborhood as a base, the associated calculation methods, such as LBP, SCOV, VAR, SAC, etc., were proposed to extract texture features from the images. The derived color and texture feature values were used as the input layer and the sensory vocabulary quantified values as the output layer. Backpropagation neural network training was conducted on 49 leather samples, with five leather samples used for testing, culminating in the verification of neural network training for three types and 17 combinations. The outcome was an optimal method for leather footwear Kansei engineering and neural network training, establishing a design process for leather footwear characteristics assisted by sensory vocabulary and a backpropagation neural network. Additionally, a computer-aided system for selecting leather footwear, based on these impressions, was designed and validated through footwear design. This study utilized symmetry in footwear design. By using the design of a single shoe to represent the imagery of a pair of symmetrical shoes, we verified whether the leather samples recommended by the leather imagery selection query system met the expected system input settings.

Memory-Augmented Theory of Mind Network

Social reasoning necessitates the capacity of theory of mind (ToM), the ability to contextualise and attribute mental states to others without having access to their internal cognitive structure. Recent machine learning approaches to ToM have demonstrated that we can train the observer to read the past and present behaviours of other agents and infer their beliefs (including false beliefs about things that no longer exist), goals, intentions and future actions. The challenges arise when the behavioural space is complex, demanding skilful space navigation for rapidly changing contexts for an extended period. We tackle the challenges by equipping the observer with novel neural memory mechanisms to encode, and hierarchical attention to selectively retrieve information about others. The memories allow rapid, selective querying of distal related past behaviours of others to deliberatively reason about their current mental state, beliefs and future behaviours. This results in ToMMY, a theory of mind model that learns to reason while making little assumptions about the underlying mental processes. We also construct a new suite of experiments to demonstrate that memories facilitate the learning process and achieve better theory of mind performance, especially for high-demand false-belief tasks that require inferring through multiple steps of changes.

A smart clinical cohort selection system for clinical oncology research.

e13613 Background: Cohort selection for specialized clinical trials is a cardinal pillar of the evidence-based medicine; however, it is the most difficult, complex, time consuming, and expensive step. Determining the efficacy of a new treatment (or intervention) requires finding eligible patients meeting the inclusion and exclusion criteria. In specialized scenarios, the complex criteria may even require researchers to do time consuming manual reviews and analyses of electronic health records (EHRs) to shortlist qualified patients. The major contribution of this research is a set of novel semantic models to build a cohort for clinical research by enabling semantic search over electronic health records represented as Knowledge graphs (KGs). Methods: We present the design of a novel cohort retrieval system, satisfying inclusion and exclusion criteria of an oncology clinical research study. Knowledge graphs and semantic models: We construct knowledge graphs (KGs) to interconnect different data sources, stored in a data-lake, and develop semantic models that enable semantic search over the processed data. We designed and constructed an oncology knowledge graph that enables semantics driven cohort selection. In addition, we have built a novel cohort retrieval system, satisfying the inclusion and exclusion criteria of an oncology clinical research study, that utilizes a semantics driven dynamic query engine to generate and execute cohort selection queries on heterogeneous EHR data. Results: We obtained real world oncology data of 21,000 oncology patients and then constructed knowledge graphs for five cancer types -- Colon (C18), Lung (C34), Breast (C50), prostate (C61), and Multiple Myeloma (C90). The cohort building scenarios are designed to represent a mix of criterion types and combinations including both inclusion and exclusion criteria, involving conjunctions, disjunctions and numeric ranges. Our cohort builder is evaluated against ten well known key competency scenarios. A team of experts validated the results of our cohort builder obtained against these competency scenarios by directly querying the graph. Our extensive evaluations demonstrate that the cohort builder searches with 100% accuracy the patients that match the criteria specified in all ten competencies. The average time to build the cohort on all graphs for these competencies is less than 10 seconds compared with that of days when patients are manually searched in EHR systems. Conclusions: Our query engine is not tightly coupled with the architecture of our data-lake; rather its architecture is flexible and can be easily integrated with other enterprise data-lakes or EMR systems. In future, we plan to scale the extent of inclusion and exclusion criteria to provide interoperability with existing clinical trial knowledge. We also aim to empirically evaluate the efficiency of cohort selection queries using a knowledge graph with classical database query approaches.

Information-Theoretically Secure and Highly Efficient Search and Row Retrieval

Information-theoretic or unconditional security provides the highest level of security --- independent of the computational capability of an adversary. Secret-sharing techniques achieve information-theoretic security by splitting a secret into multiple parts (called shares ) and storing the shares across non-colluding servers. However, secret-sharing-based solutions suffer from high overheads due to multiple communication rounds among servers and/or information leakage due to access-patterns ( i.e. , the identity of rows satisfying a query) and volume ( i.e. , the number of rows satisfying a query). We propose S 2 , an information-theoretically secure approach that uses both additive and multiplicative secret-sharing, to efficiently support a large class of selection queries involving conjunctive, disjunctive, and range conditions. Two major contributions of S 2 are: ( i ) a new search algorithm using additive shares based on fingerprints, which were developed for string-matching over cleartext; and ( ii ) two row retrieval algorithms: one is based on multiplicative shares and another is based on additive shares. S 2 does not require communication among servers storing shares and does not reveal any information to an adversary based on access-patterns and volume.

HINT: a hierarchical interval index for Allen relationships

Indexing intervals is a fundamental problem, finding a wide range of applications, most notably in temporal and uncertain databases. We propose HINT, a novel and efficient in-memory index for range selection queries over interval collections. HINT applies a hierarchical partitioning approach, which assigns each interval to at most two partitions per level and has controlled space requirements. We reduce the information stored at each partition to the absolutely necessary by dividing the intervals in it, based on whether they begin inside or before the partition boundaries. In addition, our index includes storage optimization techniques for the effective handling of data sparsity and skewness. We show how HINT can be used to efficiently process queries based on Allen’s relationships. Experiments on real and synthetic interval sets of different characteristics show that HINT is typically one order of magnitude faster than existing interval indexing methods.

MySQL Select Query Optimization Using Self-Join

MySQL Select Query Optimization Using Self-Join

ALEC: Active learning with ensemble of classifiers for clinical diagnosis of coronary artery disease

Invasive angiography is the reference standard for coronary artery disease (CAD) diagnosis but is expensive and associated with certain risks. Machine learning (ML) using clinical and noninvasive imaging parameters can be used for CAD diagnosis to avoid the side effects and cost of angiography. However, ML methods require labeled samples for efficient training. The labeled data scarcity and high labeling costs can be mitigated by active learning. This is achieved through selective query of challenging samples for labeling. To the best of our knowledge, active learning has not been used for CAD diagnosis yet. An Active Learning with Ensemble of Classifiers (ALEC) method is proposed for CAD diagnosis, consisting of four classifiers. Three of these classifiers determine whether a patient's three main coronary arteries are stenotic or not. The fourth classifier predicts whether the patient has CAD or not. ALEC is first trained using labeled samples. For each unlabeled sample, if the outputs of the classifiers are consistent, the sample along with its predicted label is added to the pool of labeled samples. Inconsistent samples are manually labeled by medical experts before being added to the pool. The training is performed once more using the samples labeled so far. The interleaved phases of labeling and training are repeated until all samples are labeled. Compared with 19 other active learning algorithms, ALEC combined with a support vector machine classifier attained superior performance with 97.01% accuracy. Our method is justified mathematically as well. We also comprehensively analyze the CAD dataset used in this paper. As part of dataset analysis, features pairwise correlation is computed. The top 15 features contributing to CAD and stenosis of the three main coronary arteries are determined. The relationship between stenosis of the main arteries is presented using conditional probabilities. The effect of considering the number of stenotic arteries on sample discrimination is investigated. The discrimination power over dataset samples is visualized, assuming each of the three main coronary arteries as a sample label and considering the two remaining arteries as sample features.

Automatic SQL to HQL-NoSQL Querying using PostgreSQL and Integrated Hive-Hbase

The amount of digital data is constantly growing in almost all fields. This data is divided into two categories, structured and unstructured data. Non-structural databases known as NoSQL became one of the main fields of big data. Many companies are still using relational databases like PostgreSQL and MySQL. But with the rapid evolution and diversity of stored data, companies find themselves obliged to use big data tools like HBase or Hive. Big data is characterized by its capacity, speed, and ability to store diverse types of data. Data analysis and high storage capacity are the main reasons for companies to search for new database systems. Data migration to new systems is associated with the modification of the existing data and applications. This process costs a lot to adopt new specialists to handle this transition. Furthermore, due to different sources of data in old systems, e.g., real-time applications that are continuously collecting new data, companies will not be able to leave relational databases. For this reason, we present a system, termed Automatic Query Language, or AQL in short form, for migrating data from PostgreSQL to integrated HBase/Hive databases. In addition, we provide a platform that allows any user to query automatically PostgreSQL, Hive, and HBase databases using SQL query only. Querying the system is related to where each big data tool’s performance is better. After the platform was completed, we were able to insert and select data from both relational databases and big data components. Join operation was not a problem because complex queries for analysis were executed using Hive which was integrated with HBase. The tested AQL system proved that HBase can insert data with more efficiency than PostgreSQL and Hive, and that select query in Hive has a better performance than PostgreSQL for big data size, whereas, for small data size, the performance of PostgreSQL is better.

Random Access in Persistent Strings and Segment Selection

We consider compact representations of collections of similar strings that support random access queries. The collection of strings is given by a rooted tree where edges are labeled by an edit operation (inserting, deleting, or replacing a character) and a node represents the string obtained by applying the sequence of edit operations on the path from the root to the node. The goal is to compactly represent the entire collection while supporting fast random access to any part of a string in the collection. This problem captures natural scenarios such as representing the past history of an edited document or representing highly-repetitive collections. Given a tree with n nodes, we show how to represent the corresponding collection in O(n) space and \(O(\log n/ \log \log n)\) query time. This improves the previous time-space trade-offs for the problem. Additionally, we show a lower bound proving that the query time is optimal for any solution using near-linear space. To achieve our bounds for random access in persistent strings we show how to reduce the problem to the following natural geometric selection problem on line segments. Consider a set of horizontal line segments in the plane. Given parameters i and j, a segment selection query returns the j th smallest segment (the segment with the j th smallest y-coordinate) among the segments crossing the vertical line through x-coordinate i. The segment selection problem is to preprocess a set of horizontal line segments into a compact data structure that supports fast segment selection queries. We present a solution that uses O(n) space and support segment selection queries in \(O(\log n/ \log \log n)\) time, where n is the number of segments. Furthermore, we prove that that this query time is also optimal for any solution using near-linear space.

Generating experimentally unrelated target molecule-binding highly functionalized nucleic-acid polymers using machine learning

In vitro selection queries large combinatorial libraries for sequence-defined polymers with target binding and reaction catalysis activity. While the total sequence space of these libraries can extend beyond 1022 sequences, practical considerations limit starting sequences to ≤~1015 distinct molecules. Selection-induced sequence convergence and limited sequencing depth further constrain experimentally observable sequence space. To address these limitations, we integrate experimental and machine learning approaches to explore regions of sequence space unrelated to experimentally derived variants. We perform in vitro selections to discover highly side-chain-functionalized nucleic acid polymers (HFNAPs) with potent affinities for a target small molecule (daunomycin KD = 5–65 nM). We then use the selection data to train a conditional variational autoencoder (CVAE) machine learning model to generate diverse and unique HFNAP sequences with high daunomycin affinities (KD = 9–26 nM), even though they are unrelated in sequence to experimental polymers. Coupling in vitro selection with a machine learning model thus enables direct generation of active variants, demonstrating a new approach to the discovery of functional biopolymers.