Quick start with columnar table

Step 1: Create a columnar table

Run the following SQL statement to create a columnar table.

CREATE TABLE sales_records (
  sale_id INT PRIMARY KEY AUTO_INCREMENT,
  product_id INT NOT NULL,
  region VARCHAR(50),
  sale_date DATE,
  amount DECIMAL(10, 2)
);

Step 2: Create a stored procedure for bulk loading

Create a stored procedure that inserts one million rows and commits every 5,000 rows.

DELIMITER //

CREATE PROCEDURE InsertMillionRows()
BEGIN
    DECLARE i INT DEFAULT 1;

    SET autocommit = 0;

    WHILE i <= 1000000 DO
        INSERT INTO users (username, email, age)
        VALUES (
            CONCAT('user_', LPAD(i, 7, '0')),
            CONCAT('user_', LPAD(i, 7, '0'), '@test.com'),
            FLOOR(18 + RAND() * 50)
        );

        SET i = i + 1;

        IF i % 5000 = 0 THEN
            COMMIT;
        END IF;
    END WHILE;

    COMMIT;
    SET autocommit = 1;
END //

DELIMITER ;

Step 3: Call the stored procedure

Run the following statement to call the stored procedure and bulk-load data.

CALL InsertMillionRows();

Step 4: Run an aggregate query

Run the following aggregate query to verify that the data has been loaded.

SELECT COUNT(*) FROM users;

Expected output:

+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.01 sec)

Step 5: View the columnar execution plan

Run the EXPLAIN command to view the columnar execution plan and verify that the query uses the columnar engine.

EXPLAIN SELECT COUNT(*) FROM users;

The expected output is as follows. The Extra Info column displays IMCI Execution Plan, which indicates that the query uses the columnar execution engine.

+----+--------------------+-------+--------+--------+---------------------------------------------------------------+
| ID | Operator           | Name  | E-Rows | E-Cost | Extra Info                                                    |
+----+--------------------+-------+--------+--------+---------------------------------------------------------------+
|  1 | Select Statement   |       |        |        | IMCI Execution Plan (max_dop = 12, max_query_mem = 858993459) |
|  2 | └─Aggregation      |       |        |        |                                                               |
|  3 |   └─Table Scan     | users |        |        |                                                               |
+----+--------------------+-------+--------+--------+---------------------------------------------------------------+