> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/analytics-overview.md).

# Analytics

Report on sales history in the same database as your live tables. Load it into a ColumnStore table beside your InnoDB tables and join them in one query.

The shop has been taking orders for a few months, and every Monday someone asks the same question: which products earned the most last week? The first answer is a `GROUP BY` against the order tables, and it works fine until the data grows. Then the report starts scanning millions of rows on the same tables that checkout writes to, and both of them slow down.

The tables aren't the problem. Their layout is. InnoDB, the default storage engine, keeps each row together on disk, which is exactly right for a checkout that reads or writes one order. A report wants the opposite. It reads two or three columns from every row and ignores the rest.

<figure><picture><source srcset="https://2719019623-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1H8tuThi64HJmOJHG6nV%2Fuploads%2Fgit-blob-693d906809599c109e9ff9e71eb2b89654e1e1af%2Fanalytics-two-engines-dark.svg?alt=media" media="(prefers-color-scheme: dark)"><img src="https://2719019623-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1H8tuThi64HJmOJHG6nV%2Fuploads%2Fgit-blob-08b4bccfd7ccd7178d40b49f0262a56fdc278066%2Fanalytics-two-engines-light.svg?alt=media" alt="One database, two storage engines. The products table uses InnoDB, which stores whole rows together, so a checkout reads one complete row. The sales table uses a columnar engine, which stores each column apart, so a revenue report reads only the product_id, quantity, and price columns. One SELECT joins both tables."></picture><figcaption><p>Each table picks the storage engine that fits how it's read, and one query can join them.</p></figcaption></figure>

MariaDB ColumnStore stores each column on its own, so a report reads only the columns it names. Because every table in MariaDB Server chooses its own storage engine, the sales history can move to ColumnStore while products and orders stay on InnoDB, all in the same `shop` database. ColumnStore is open source and ships with MariaDB Community Server and MariaDB Enterprise Server.

{% hint style="info" %}
To follow along, you need a server with ColumnStore installed, because both the `ENGINE=ColumnStore` tables and the `cpimport` loader come from it. The official Docker image from the landing page doesn't include ColumnStore, so use a Linux host for this page. On MariaDB Community Server, install the `mariadb-plugin-columnstore` package (`MariaDB-columnstore-engine` on RHEL) from the MariaDB repository. For MariaDB Enterprise Server, the [single-node install guide](/docs/columnstore/management/deployment/install-guide/singlenode-localstorage.md) walks through the setup.
{% endhint %}

## Move Sales to ColumnStore

{% stepper %}
{% step %}

### Create a ColumnStore Table

Add a `sales` table to the `shop` database with `ENGINE=ColumnStore`. ColumnStore tables don't use primary keys or indexes the way InnoDB tables do, because the engine scans compressed columns instead of looking up rows.

```sql
CREATE TABLE shop.sales (
  sale_id    INT,
  product_id INT,
  sale_date  DATE,
  quantity   INT,
  price      DECIMAL(8,2)
) ENGINE=ColumnStore;
```

The [ColumnStore quickstart](/docs/columnstore/columnstore-quickstart-guides/mariadb-columnstore-guide.md) explains how the engine stores and compresses each column.
{% endstep %}

{% step %}

### Bulk Load With cpimport

The `cpimport` tool ships with ColumnStore and writes straight to the column files, skipping the SQL layer, which makes it the fastest way to load history. It reads pipe-delimited text by default, so write a small file on the server host and load it.

```bash
printf '1|1|2026-09-01|2|39.00\n2|2|2026-09-01|1|249.00\n3|3|2026-09-02|10|4.50\n4|1|2026-09-03|3|39.00\n' > sales.txt

sudo cpimport shop sales sales.txt
```

Reports keep running while the load is in progress. When your order history already lives in an InnoDB table, an `INSERT INTO shop.sales SELECT ...` statement run outside a transaction becomes the same bulk load. [Data Loading with cpimport](/docs/columnstore/clients-and-tools/data-import/mariadb-enterprise-columnstore-data-loading-with-cpimport.md) covers the file formats and options.
{% endstep %}

{% step %}

### Join Across Engines

Revenue per product needs the sales figures from ColumnStore and the product names from InnoDB. One `SELECT` reads both.

```sql
SELECT p.name, SUM(s.quantity * s.price) AS revenue
FROM shop.sales AS s
JOIN shop.products AS p ON p.id = s.product_id
GROUP BY p.name
ORDER BY revenue DESC;
```

ColumnStore reaches the InnoDB table through a cross-engine connection that you set up once with the `mcsSetConfig CrossEngineSupport` commands in [Start and Configure ColumnStore](/docs/columnstore/management/deployment/install-guide/singlenode-localstorage/step-3-start-and-configure-enterprise-columnstore.md).
{% endstep %}
{% endstepper %}

With the sales history on ColumnStore, the Monday report reads a few compressed columns and leaves checkout alone. When the sales history outgrows one server, MariaDB Exa, part of MariaDB Enterprise Platform, runs analytics on a separate cluster. MaxScale CDC reads the binary log and loads each change into Exa, and you query the copy there, not in the same query as your InnoDB tables.

## Keep Going

<table data-view="cards"><thead><tr><th></th><th></th><th data-hidden data-card-target data-type="content-ref"></th></tr></thead><tbody><tr><td><strong>Choose a Storage Engine</strong></td><td>Match each table to how your application reads and writes it.</td><td><a href="/docs/server/server-usage/storage-engines/choosing-the-right-storage-engine.md">Choosing the Right Storage Engine</a></td></tr><tr><td><strong>ColumnStore Hardware</strong></td><td>Size memory, disk, and CPU cores before you pick machines, since scan speed depends on all of them.</td><td><a href="/docs/columnstore/columnstore-quickstart-guides/mariadb-columnstore-hardware-guide.md">MariaDB ColumnStore Hardware Guide</a></td></tr><tr><td><strong>How MariaDB Exa Works</strong></td><td>See how MaxScale CDC reads the binary log and loads each change into the Exa cluster for analytics.</td><td><a href="/docs/exa/architecture.md">Architecture</a></td></tr><tr><td><strong>Load Data With INSERT ... SELECT</strong></td><td>Copy rows from InnoDB tables into ColumnStore with plain SQL.</td><td><a href="/docs/columnstore/clients-and-tools/data-import/mariadb-enterprise-columnstore-data-loading-with-insert-select.md">Data Loading with INSERT .. SELECT</a></td></tr><tr><td><strong>Deploy MariaDB Exa</strong></td><td>Run the largest analytical workloads on a separate cluster.</td><td><a href="/docs/exa/deployment.md">Deployment</a></td></tr><tr><td><strong>HTAP on MariaDB Cloud</strong></td><td>Pair a transactional MariaDB Cloud service with MariaDB Exa, a technical preview on the Power and PowerPlus tiers, enabled on request.</td><td><a href="/docs/mariadb-cloud/quickstart/htap-mariadb-exa.md">HTAP using MariaDB Exa</a></td></tr></tbody></table>