How to Write & Tune Queries in SingleStoreDB

SingleStoreDB is a high-performance, real-time distributed SQL database that processes queries at lightning-fast speeds.

It is designed to handle large volumes of data with ease, and also handles complex queries that require real-time analysis. Typically, when a customer transitions to SingleStoreDB, they are able to achieve performance improvements of 10-100x on their queries.

However, writing and tuning efficient queries is key to getting the most out of SingleStoreDB— something we’ll cover in this blog post.

Writing Queries in SingleStoreDB

Writing queries in SingleStoreDB is similar to writing queries in any other SQL-based database system. SingleStoreDB is MySQL wire protocol compatible, meaning you can use the standard ANSI SQL syntax to write queries that retrieve data from the database.

Here are some tips on how to write efficient queries in SingleStoreDB:

1. Use Shard Keys. The shard key is a table column (or multiple columns) used to control how the rows of a table are distributed. You should create shard keys on columns that are high cardinality values, frequently used in WHERE clauses or JOIN conditions. Here is a link to our documentation on understanding shard key selection.
2. Use Sort Keys. The sort key is an index that groups rows of columnstore tables into logical segments, where each segment contains data for many rows. You should define a sort key to help with segment elimination for optimal query performance. Check out this link to our documentation that goes into more details on sort key functionality.
3. Use Indexes. Indexes are critical in SingleStoreDB because they allow the database to quickly locate data based on specific criteria. SingleStoreDB supports a wide range of indexes — the full list of which can be found here on our docs page.
4. Limit the Number of Columns Returned. Only retrieve the columns that you need. This will reduce the amount of data that needs to be retrieved from the database, which improves query performance.
5. Use Aggregate Functions. Use aggregate functions such as SUM, COUNT, AVG and MAX to perform calculations on large datasets. This can be much faster than retrieving all the data, and performing the calculation in your application code.
6. Avoid SELECT *. Retrieving all columns using SELECT * can be inefficient, especially if you only need a few columns. Specify the columns you need instead of using SELECT *.

Tuning Queries in SingleStoreDB

Tuning queries in SingleStoreDB involves optimizing the queries to improve their performance. Here are some tips on how to do it:

1. Analyze the Visual Explain. SingleStoreDB provides a visual guide that outlines the query execution plan, showing users how the database engine executes a query. Analyzing this plan can help you identify inefficiencies in the query and optimize it accordingly.
2. Use EXPLAIN Statement. The EXPLAIN statement in SingleStoreDB can be used to obtain the query execution plan for a specific query. This can help you identify potential performance bottlenecks, and optimize your query accordingly.
3. Leverage Query Plancache. In SingleStoreDB, when our users execute a query, it activates code generation, gets optimized and translated into lower level machine language.  After code generation, the compiled query plans are saved for later use in a plancache. Saving the query in the plancache improves query performance by reducing the number of times the query engine needs to run on a commonly executed query.
4. Partition Configuration. SingleStoreDB uses a partitioning scheme called sharding, which splits data across multiple nodes in a cluster. Each shard contains a subset of the data, and each node can contain multiple shards. Depending on your query shapes, adjusting the partition to vCPU ratio can help improve performance of your queries. For example, workloads that require higher ingest performance, it is optimal to increase the number of partitions. For workloads where concurrency is the primary factor, reducing the number of partitions can help with query performance.

More details on query tuning can be found in our SingleStore docs.

Conclusion

SingleStoreDB is a high-performance, distributed SQL database that can process queries at blazing-fast speeds — and writing efficient queries and tuning them is critical to getting the most out of SingleStoreDB. By following the tips outlined in this blog post, you can write queries that are optimized for performance and tune them to improve their performance even further.

With these tips, you can harness the full power of SingleStoreDB and get the most out of your data. Get started for free today.