SQL Optimization with SingleStore

Understand single store db cloud workspace architecture, including aggregator and leaf nodes, how they handle requests, and how compute and storage separation enables read/write and read-only database attachments.

Discover how SingleStore partitions work at the database level, how to create and size partitions, and how flexible parallelism and partition count influence cluster expansion and repartitioning.

Learn to create and tune partitions and sub partitions in single store, using flexible parallelism to parallelize reads across cores, balancing concurrency with single-query performance.

Discover how SingleStore handles database partitions by default and how to specify a custom partition count, using leaf nodes, CPUs, and primary partitions in various workspaces.

Explore flexible parallelism in single store db cloud by examining partitions and sub partitions, and learn how engine variables like sub-to-physical ratio (16) and leaf query parallelism (1) shape performance.

Learn to attach and detach databases in a single store cloud workspace, and observe how partitions distribute across nodes when connected to workspaces of different sizes.

Learn how partitions and leaf node counts affect cluster expansion, rebalancing, and single-query performance, and discover three practical workarounds to repartition a single store database.

Explore how sharding and shard keys distribute data across partitions, reduce data skew, and optimize queries and joins in single store with high cardinality and compound shard keys.

Learn how choosing shard keys and managing cardinality affects data skew and query performance in SingleStore db cloud tables, including joins and partitioning.

Explore single store table types and universal storage table that blends in-memory and on-disk storage for analytical and transactional workloads, in-memory row stores and reference tables to accelerate joins.

Explore universal storage tables, a hybrid of row and column stores for transactional and analytical workloads. Observe how in-memory segments, on-disk column stores, and cloud object stores enable unlimited storage.

Explore SingleStore columnstore technology, including sub partitions and column segments, enabling fast on-disk reads via segment elimination and sub segment access, plus bulk inserts and high compression up to 90%.

Create universal storage tables with a sort key to enable segment elimination, and tune column store flush bytes and idle flush seconds for in-memory behavior.

Explore universal storage tables with unlimited storage in single-store db cloud; no setup required, as data moves from an in-memory roaster segment to an on-disk column store and object store.

Explore universal storage tables' in-memory row stores and on-disk column stores, with information_schema metadata and partitioning, demonstrated by loading 1 million rows and applying sort and shard keys.

Explore single store row stores, and reference tables, learning when to use them, how they behave in memory and on disk in universal storage, and how to create them.

Explore row store performance in single store db cloud with a demo on sparse compression for wide tables with many nulls; compare row store to column store for complete rewrites.

Create reference tables and observe replication across every node in the SingleStore db cloud workspace. Understand how column store and row store reference tables enable fast joins by staying small.

Explore skip list indexes, hash indexes, and full text indexes, and learn when to use them in row and column stores, with primary keys and shard keys.

Explore indexing JSON and spatial data by extracting JSON into a persisted calculated field using JSON double colon notation, and applying skip lists indexes to the JSON and geography columns.

Explore query tuning and performance optimization in single store by analyzing execution plans in distributed systems and adjusting them to boost query performance.

Explore how the query optimizer uses statistics and cardinality to cost plans in a distributed SingleStore setup, with schema design guiding joins and data reshuffles or broadcasts.

Learn how single store uses column statistics and histograms, with auto stats in the background, to select plans and populate the plan cache, while analyze table refreshes and invalidates plans.

Explains how explain and profile work in SingleStore, detailing the execution plan, resource metrics, and json mode outputs for tuning using sharded and reference tables.

Explore how the SingleStore query optimizer builds execution plans. Learn common operations from scan and filter to joins and post-join aggregates, with a visual explain example.

explore the four phases of a query execution plan in sql optimization with SingleStore, from scans and filters to joins, data movement, and gather operations.

Learn to use the visual explain tool to profile a sql query and read the execution plan. Identify bottlenecks like table scans, repartitions, shard keys, and broadcasts to optimize performance.