Example

This example shows a simple use of Visual Explain to make a query run faster. First, an example table is created and filled with mock order data.

CREATE TABLE IF NOT EXISTS expedited_orders(
order_id INT,
order_date DATETIME,
KEY (order_id),
SHARD KEY (order_id));
INSERT INTO expedited_orders VALUES(1, now());

/* Run this command until 524288 rows of data have been inserted into the table. */

INSERT INTO expedited_orders
SELECT order_id+(SELECT MAX(order_id) FROM expedited_orders), NOW()
FROM expedited_orders;

Running the below queries using Visual Profile reveals that SingleStoreDB Cloud performs a full TableScan in this query plan. This means that each individual row is scanned in order to count those where the order_date is 2020-09-21 17:51:47.

/* Run this query to get an example order_date. */

SELECT order_date, COUNT(*)
FROM expedited_orders
GROUP BY 1
HAVING COUNT(*) = 1

/* Run this query using the order_date copied from from the previous query result,
in place of the date 2020-09-21 17:51:47. */

SELECT *
FROM expedited_orders
WHERE order_date = "2020-09-21 17:51:47";

Adding an index on the order_date column, then running and profiling the same query again reveals an increase in performance. The runtime for the query decreases from 95 milliseconds to 1 millisecond. This is because the new index on order_date allows SingleStoreDB Cloud to execute this query with IndexRangeScan instead of TableScan, meaning that the index is utilized in order to only scan rows which might contain the specified order_date.

CREATE INDEX date_index ON expedited_orders (order_date);

SELECT *
FROM expedited_orders
WHERE order_date = "2020-09-21 17:51:47";
DROP TABLE expedited_orders;

For more examples, see the final lab in the SingleStoreDB Cloud Training course, Query Tuning.

Related Topics