Query History Examples

Create the Dataset

1. In the SQL Editor or 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 the SQL Editor or 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 2024-01-12 19:07:19 for the order_date. Use an order_date from your own database to ensure that this example will work.

   Copy

   SELECT * FROM expedited_orders WHERE order_date = "2024-01-12 19:07:19";

View Query Performance

1. Note that the trace_events_queue_size value is s set to 16,000,000 bytes by default and can be updated with a new value via the following SQL statement.

   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 Query History. After a few minutes, those queries that take over a second to run will be displayed, from the most time-intensive to the least.

   

Visualize and Optimize the Query

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

   Copy

   PROFILE SELECT * FROM expedited_orders WHERE order_date = "2024-01-12 19:07:19";

   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 71 milliseconds, which includes the query compilation time.

   

To determine if adding an index will improve performance:

1. In the SQL Editor or 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.

   Copy

   SELECT * FROM expedited_orders WHERE order_date = "2024-01-12 19:07:19";

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

   Copy

   PROFILE SELECT * FROM expedited_orders WHERE order_date = "2024-01-12 19:07:19";

   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 51 milliseconds, which also includes the query compilation time.

   

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

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

   Copy

   SELECT * FROM expedited_orders WHERE order_date = "2024-01-12 19:07:19";

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

   Copy

   PROFILE SELECT * FROM expedited_orders WHERE order_date = "2024-01-12 19:07:19";

   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 1 millisecond.

   

Review Query Performance

View Query History. Sort by elapsed time and look for instances of 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 71 milliseconds for a full table scan

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

• To only 1 millisecond 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.