The MemSQL Visual Explain page is a feature of SingleStore DB Studio that allows customers to see a query plan visualized via a graphical interface. This is useful for tuning database queries in order to reduce run-time or resource usage. See more about this in the Query Tuning guide.

Using Visual Explain

To use Visual Explain, highlight the desired query in the SQL Editor, and click the Visual Explain icon to the left of the “run” button. This should look like a tree. In doing this, you may choose between EXPLAIN and PROFILE.

At the top-left of the Visual Explain page, you can choose between the “Actual,” “Estimated,” or “Difference” options for a given query plan.

At the center of this interface is a tree composed of clickable elements, where each element displayed is a single query plan operation. These are ordered so that the operator at the top of the tree is the one that returns the result to the client.

An alternative option for using Visual Explain is uploading a JSON file containing your query plan from the Visual Explain page itself by clicking the icon on the sidebar to the left.

Metrics Displayed by Visual Explain

Each element of the center graphic portion of this page includes the following information:

• The query plan operation being performed
• The percent of total query execution time for each query plan operation
• The number of rows processed by each query plan operation. This number could be greater than the number of rows in a given table if the query includes a command, such as a nested loop join, that requires the engine to read the table multiple times
• The associated table each query plan operation is performed on (where applicable)

The following metrics are also shown in text form on the right of the Visual Explain page. If no element of the graphic has been clicked, a summary of all query operations is shown. If a specific query operation has been selected, this section will show more detailed information.

• Summary
  • Total execution time
  • Total number of operations
  • Total memory usage
• Details
  • Each query plan operation
  • Time spent on each query plan operation

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(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 MemSQL 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 MemSQL 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 MemSQL Training course, Query Tuning.

Related Topics

• Query Tuning
• Query Plan Operations
• EXPLAIN
• PROFILE

Was this Article Helpful?

Thanks For Your Feedback!