Query History Examples

Create the Dataset

1. In a SQL client, create the following rowstore table in the database of your choice (such as a test database) and fill it with mock order data.

   SQL

   Copy

   CREATE ROWSTORE 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());

2. Re-run the following SQL statement until 1,048,576 rows of data have been inserted into the table.

   SQL

   Copy

   INSERT INTO expedited_orders
   SELECT order_id+(SELECT max(order_id) FROM expedited_orders), now()
   FROM expedited_orders;

Obtain an Order Date

1. In a SQL client, run the following query to obtain a sample order_date.

   SQL

   Copy

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

2. Run the following query using an order_date from the previous query result.

   Note: The remainder of these steps will use 2023-12-04 15:50:41 for the order_date. Use an order_date from your own database to ensure that this example will work.

   SQL

   Copy

   SELECT * FROM expedited_orders WHERE order_date = "2023-12-04 15:50:41";

View Query Performance

1. Set the events queue size, trace_events_queue_size, to 16,000,000 bytes.

   SQL

   Copy

   SET GLOBAL trace_events_queue_size = 16000000;

2. Enable the Query_completion event type to look for queries that take over 250 milliseconds to run.

   SQL

   Copy

   CREATE EVENT TRACE Query_completion WITH (Query_text = on, Duration_threshold_ms = 250);

3. View the Query History dashboard. After a few minutes, those queries that take over a second to run will be displayed, from the most time-intensive to the least.

   

   Of the queries displayed, the SELECT * FROM expedited_orders WHERE order_date = "2023-12-04 15:50:41"; is the example query to optimize as it required nearly 500 milliseconds to complete.

Visualize and Optimize the Query

1. In a SQL client, run the following PROFILE-related queries to obtain the JSON output.

   SQL

   Copy

   PROFILE SELECT * FROM expedited_orders WHERE order_date = "2023-12-04 15:50:41";

   SQL

   Copy

   SHOW PROFILE JSON;

2. In Visual Explain, click the Paste JSON button, paste the JSON output into the provided field, and click the Show Explain button.

   Visualizing this query reveals that SingleStore performed a full table scan (indicated by TableScan) in this query. As a consequence, each row was scanned to determine which order matched the desired order_date.

   Note that the execution time for this query is 579 milliseconds, which includes the query compilation time.

   

To determine if adding an index will improve performance:

1. In a SQL client, add an index on the order_date column.

   SQL

   Copy

   CREATE INDEX date_index ON expedited_orders (order_date);

2. Re-run the SELECT * FROM expedited_orders query.

   SQL

   Copy

   SELECT * FROM expedited_orders WHERE order_date = "2023-12-04 15:50:41";

3. Re-run the PROFILE-related queries to obtain the JSON output.

   SQL

   Copy

   PROFILE SELECT * FROM expedited_orders WHERE order_date = "2023-12-04 15:50:41";

   SQL

   Copy

   SHOW PROFILE JSON;

4. Copy the JSON output to the clipboard.

5. In Visual Explain, click the vertical dots menu in the upper right-hand corner, click the Paste JSON Explain/Profile option, paste the JSON output into the provided field, and click the Show Explain button.

   Visualizing this query reveals that SingleStore used an index range scan (indicated by IndexRangeScan) instead of a full table scan, thereby limiting the scan to only those rows that should contain the specified order_date.

   Note that the execution time for this query is down to 54 milliseconds, which also includes the query compilation time.

   

To see how quickly this query will run without the query compilation time:

1. In a SQL client, re-run the SELECT * FROM expedited_orders query.

   SQL

   Copy

   SELECT * FROM expedited_orders WHERE order_date = "2023-12-04 15:50:41";

2. Re-run the PROFILE-related queries to obtain the JSON output.

   SQL

   Copy

   PROFILE SELECT * FROM expedited_orders WHERE order_date = "2023-12-04 15:50:41";

   SQL

   Copy

   SHOW PROFILE JSON;

3. Copy the JSON output to the clipboard.

4. In Visual Explain, click the vertical dots menu in the upper right-hand corner, click the Paste JSON Explain/Profile option, paste the JSON output into the provided field, and click the Show Explain button.

   Visualizing this query reveals that the execution time has been reduced to 4 milliseconds.

   

Review Query Performance

View the Query History dashboard. Filter by the SELECT * FROM expedited_orders query.

The query’s performance history is displayed.

Note that the query duration in Query History and the query execution time in Visual Explain will differ slightly due to how the query duration is measured.

Summary

By employing both the Query History and Visual Explain features to locate and optimize the query in this example, the runtime for the SELECT * FROM expedited_orders query has been reduced:

• From an initial 597 milliseconds for a full table scan

• To 54 milliseconds after introducing an index, which includes the query compilation time

• To only 4 milliseconds after the query had been compiled

For additional examples of using Visual Explain, refer to SingleStore Visual Explain and the final lab in the SingleStore Query Tuning training course.