33 posts tagged with "pawsql"

Overview​

TPCH is a database test benchmark for decision support systems，developed by TPC (Transaction Processing Performance Council) in 1994. TPCH tests consist of a set of 22 complex business queries and some data maintenance queries to measure the performance of relational database management systems under complex analytical queries. These queries simulate real queries in a data warehouse of a wholesale supplier, covering aspects such as order analysis, supply chain management, and partial sales analysis.

Database vendors use TPCH tests to evaluate their products, assessing the impact of different servers, storage configurations, parallel processing, and resource scheduling on database performance.

In this article, we will evaluate the effectiveness of PawSQL's optimizations on the TPCH queries by only rewriting queries and recommending indexes without changing the hardware configuration and database parameters.

Problem​

Functionrand(MySQL) or random(PostgreSQL) returns a random floating-point value in the range 0 to 1.0. It is useful to get a random sample of dataset using following query.

select * from orders order by rand() limit 1; 

This works fine if customer table is less than 10,000 rows. But when you have 1,000,000 rows, the overhead for sorting the rows becomes unacceptable. The reason is quiet clear: we sort all rows, but only keep one.

Indexes are one of the most important tools for optimizing database performance. However, creating too many indexes or indexing the wrong columns can also have a negative impact on performance. Therefore, it is important to follow certain principles when designing indexes.

A database index is a data structure used to speed up database queries. It is similar to the catalog of books and can help quickly locate a certain row or rows in a table. A database index usually consists of a set of index keys (or index fields) whose values are stored in a data structure for fast lookup of specific rows.

Copyright © 2023 PawSQL

Problem Definition​

Using the LIMIT clause in UPDATE/DELETE operations can lead to master-slave data inconsistency or interruption of slave synchronization.

PawSQL Auditing​

PawSQL provides a corresponding SQL Audit warning for this case.