--- title: "Getting Started with TiDB Cloud Serverless" version: "v2" status: "GA" description: "In this hands-on lab, you will learn the basic operations and advanced features of TiDB Cloud Serverless. Specifically, we will cover creating a cluster and issuing queries from MySQL Client, accessing the database through Python scripts, and accelerating complex queries with TiFlash/HTAP. This is a practical workshop that allows even beginners to learn the basics and advanced features of TiDB Cloud Serverless in one go." --- # Getting Started with TiDB Cloud Serverless ## Overview In this workshop, you will learn the basic operations and advanced features of TiDB Cloud Serverless by using SQL. Specifically, we will cover issuing queries from MySQL Client, accessing the database programmatically by using Python scripts as an example, accelerating complex queries with TiFlash, and exploring the fundamental features to support GenAI applications which are Vector Search and Full Text Search. This is a practical workshop that allows even beginners to learn the basics and advanced features of TiDB Cloud Serverless in one go. ### Duration + This lab should take you approximately 90 minutes to complete. ### Tasks + Connect to a TiDB Cloud Serverless cluster and issue queries from MySQL Client. + Create a Python script to access TiDB. + Speed ​​up long-running complex queries by using the columnar storage option TiFlash. > [!TIP] > Click **Start** to launch the lab environment. > Once the environment is ready, select **Next** on the bottom toolbar to advance to **Module 1**. > And, follow the step-by-step lab guide to connect to the hands-on environment and begin your tasks. ## Module 1: Connecting to TiDB Cloud Serverless ### Step 1. Log into the remote Linux + Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab. ### Step 2. Check the connectivity to the TiDB Cloud Serverless cluster + The following operations are performed on a remote Virtual Machine. Connect to TiDB Cloud Serverless with the following three patterns. (When you want to exit the MySQL client, use `EXIT`.) + A. Connection (with SSL): Connect using the following command. ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/pki/tls/certs/ca-bundle.crt ``` ```output mysql> ``` ```sql EXIT; ``` + B. Connection (with SSL): If you don't specify the `--ssl-mode` option, the default mode is `PREFERRED`, which means the MySQL client will first attempt to establish an SSL connection and fall back to an unencrypted connection if SSL fails. ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} ``` ```output mysql> ``` ```sql EXIT; ``` + C. Connection (without SSL): Change to `--ssl-mode=DISABLED`. A connection error will occur. ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=DISABLED ``` ```output mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1105 (HY000): Connections using insecure transport are prohibited. See https://docs.tidb.io/tidbcloud/secure-connections-to-serverless-tier-clusters ``` ### Step 3. Basic database operations + Connect to TiDB. ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/pki/tls/certs/ca-bundle.crt ``` ```output mysql> ``` + Create a new database. ```sql DROP DATABASE IF EXISTS ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; CREATE DATABASE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; SHOW DATABASES\G ``` ```output *************************** 1. row *************************** Database: INFORMATION_SCHEMA *************************** 2. row *************************** Database: PERFORMANCE_SCHEMA *************************** 3. row *************************** Database: lab_demo_201_2370028 *************************** 4. row *************************** Database: mysql *************************** 5. row *************************** Database: test 5 rows in set (0.02 sec) ``` + Create a new table. ```sql USE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; CREATE TABLE color (id INT AUTO_INCREMENT, name TEXT, Point INT, PRIMARY KEY (id)); INSERT INTO color (name, Point) VALUES ('red', 1); INSERT INTO color (name, Point) VALUES ('orange', 1); INSERT INTO color (name, Point) VALUES ('yellow', 2); SELECT * FROM color; ``` ```output Database changed Query OK, 0 rows affected (0.14 sec) Query OK, 1 row affected (0.04 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.01 sec) +----+--------+-------+ | id | name | Point | +----+--------+-------+ | 1 | red | 1 | | 2 | orange | 1 | | 3 | yellow | 2 | +----+--------+-------+ 3 rows in set (0.01 sec) ``` ### Step 4. Check MySQL compatibility + TiDB is not 100% compatible with MySQL. Here we check the MySQL 8.0 features such as Window Functions and unsupported Stored Procedures. You can check compatibility from the following URL: [https://docs.pingcap.com/tidb/stable/mysql-compatibility/](https://docs.pingcap.com/tidb/stable/mysql-compatibility/). + Check that the default character set and collation are different. ```sql SHOW CREATE TABLE color\G ``` ```output *************************** 1. row *************************** Table: color Create Table: CREATE TABLE `color` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` text DEFAULT NULL, `Point` int(11) DEFAULT NULL, PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001 1 row in set (0.00 sec) ``` + Window function example. ```sql SELECT id, name, Point, RANK() OVER w as 'rank' FROM color WINDOW w AS (ORDER BY Point); ``` ```output +----+--------+-------+------+ | id | name | Point | rank | +----+--------+-------+------+ | 1 | red | 1 | 1 | | 2 | orange | 1 | 1 | | 3 | yellow | 2 | 3 | +----+--------+-------+------+ 3 rows in set (0.01 sec) ``` + Stored Procedure. This will fail as it is not supported. ```sql delimiter // CREATE PROCEDURE sample() BEGIN SELECT * FROM color; END // ``` ```output ERROR 8108 (HY000): Unsupported type *ast.ProcedureInfo ``` ```sql EXIT // ``` ```output Bye ``` ## Module 2: Accessing TiDB Cloud Serverless from Python ### Step 1. Import the sample data + Connect to TiDB. ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/pki/tls/certs/ca-bundle.crt ``` ```output mysql> ``` + Create the sample database, and import the sample **S&P 500** data. > [!TIP] > You can safely ignore the `Access Denied` message caused by the `LOCK TABLE` command from the import script in this step. `LOCK TABLE` is not necessary in a distributed SQL database like TiDB. ```sql CREATE DATABASE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; USE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; SOURCE sp500insight.sql ``` ```output ... Query OK, 7947 rows affected (0.34 sec) Records: 7947 Duplicates: 0 Warnings: 0 Query OK, 7882 rows affected (0.34 sec) Records: 7882 Duplicates: 0 Warnings: 0 ... Query OK, 0 rows affected, 1 warning (0.01 sec) ``` ### Step 2. Access the sample data ```sql USE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; SELECT sector, industry, COUNT(*) AS companies FROM companies c WHERE c.stock_symbol IN (SELECT stock_symbol FROM index_compositions WHERE index_symbol = 'SP500') GROUP BY sector, industry ORDER BY sector, companies DESC; ``` ```output Database changed +------------------------+------------------------------------------+-----------+ | sector | industry | companies | +------------------------+------------------------------------------+-----------+ | Basic Materials | Specialty Chemicals | 10 | | Basic Materials | Agricultural Inputs | 4 | | Basic Materials | Building Materials | 2 | | Basic Materials | Chemicals | 2 | | Basic Materials | Steel | 2 | | Basic Materials | Gold | 1 | | Basic Materials | Copper | 1 | | Communication Services | Entertainment | 7 | ... | Utilities | Utilities—Regulated Electric | 23 | | Utilities | Utilities—Diversified | 2 | | Utilities | Utilities—Regulated Gas | 2 | | Utilities | Utilities—Regulated Water | 1 | | Utilities | Utilities—Independent Power Producers | 1 | +------------------------+------------------------------------------+-----------+ 113 rows in set, 1 warning (0.19 sec) ``` ### Step 3. Create a script for executing an analytical query + Exit to OS prompt. ```sql EXIT; ``` + Create a Python script. ```bash cat > test01.py <<'EOF' import mysql.connector import datetime from prettytable import PrettyTable config = { 'user': '${LAB:SERVERLESS_CLUSTER_USERNAME}', 'password': '${LAB:SERVERLESS_CLUSTER_PASSWORD}', 'host': '${LAB:SERVERLESS_CLUSTER_HOST}', 'database': '${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}', 'raise_on_warnings': False, 'port': ${LAB:SERVERLESS_CLUSTER_PORT}, } def execQuery(query, queryName): print(queryName) print("Query execution start time: ", datetime.datetime.now().strftime("%H:%M:%S.%f")) # Executing the query cursor.execute(query) print("Query execution end time: ", datetime.datetime.now().strftime("%H:%M:%S.%f")) # Fetching all rows from the last executed query results = cursor.fetchall() # Creating a PrettyTable object my_table = PrettyTable() # Adding field names (column names) to the table field_names = [i[0] for i in cursor.description] my_table.field_names = field_names # Adding rows to the table for row in results: my_table.add_row(row) # Printing the table print(my_table) print("==========================") try: # Establishing the connection conn = mysql.connector.connect(**config) # Creating a cursor object cursor = conn.cursor() # Your SQL query queryName= "■■■Query 1: Market capitalization by industry■■■" query = """ SELECT `sector`, SUM(`market_cap`) AS `total_market_cap` FROM `companies` GROUP BY `sector`; """ execQuery(query, queryName) queryName= "■■■Query 2: Market capitalization ranking in the energy industry■■■" query = """ WITH index_companies AS ( SELECT c.stock_symbol, c.short_name, c.exchange_symbol, ic.weight, c.market_cap AS market_cap, c.revenue_growth AS revenue_growth FROM companies c JOIN index_compositions ic ON c.stock_symbol = ic.stock_symbol WHERE index_symbol = 'SP500' AND sector = 'Energy' ), companies_latest_price AS ( SELECT stock_symbol, DATE(last_day) AS last_change_day, last_close_price, last_2nd_close_price, last_close_price - last_2nd_close_price AS last_change, (last_close_price - last_2nd_close_price) / last_2nd_close_price AS last_change_percentage FROM ( SELECT LAST_VALUE(stock_symbol) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS stock_symbol, LAST_VALUE(record_date) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS last_day, NTH_VALUE(close, 1) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_close_price, NTH_VALUE(close, 2) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_2nd_close_price, ROW_NUMBER() OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS row_num FROM stock_price_history sph WHERE record_date > DATE_SUB((SELECT MAX(record_date) FROM stock_price_history), INTERVAL 2 DAY) AND sph.stock_symbol IN (SELECT stock_symbol FROM index_companies ic) ) sub WHERE row_num = 2 ) SELECT ic.stock_symbol, ic.exchange_symbol, ic.short_name, ic.weight, ic.market_cap, ic.revenue_growth, clp.last_change, clp.last_change_day, clp.last_close_price, clp.last_2nd_close_price, clp.last_change_percentage FROM index_companies ic LEFT JOIN companies_latest_price clp ON ic.stock_symbol = clp.stock_symbol ORDER BY ic.weight DESC; """ execQuery(query, queryName) except mysql.connector.Error as err: print(f"Error: {err}") finally: # Closing the connection if conn.is_connected(): cursor.close() conn.close() print("MySQL connection is closed") EOF ls -l test01.py ``` ```output -rw-r--r--. 1 rocky rocky 3636 Apr 28 02:22 test01.py ``` ### Step 4. Run the script to execute the analytical queries + Run the Python script to get two report from the sample data. Pay attention to the elapsed time. ```bash python test01.py ``` ```output ■■■Query 1: Market capitalization by industry■■■ Query execution start time: 02:22:15.881417 Query execution end time: 02:22:15.902559 +------------------------+------------------+ | sector | total_market_cap | +------------------------+------------------+ | Real Estate | 927820086272 | | Utilities | 1023797270016 | | Healthcare | 5122903213056 | | Technology | 7906855563776 | | Financial Services | 4661122020864 | | Industrials | 3199253085184 | | Consumer Cyclical | 3238477340160 | | Consumer Defensive | 2872367655424 | | Energy | 1788549832704 | | Basic Materials | 889871172608 | | Communication Services | 3669841019392 | +------------------------+------------------+ ========================== ■■■Query 2: Market capitalization ranking in the energy industry■■■ Query execution start time: 02:22:15.904362 Query execution end time: 02:22:18.595003 +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | stock_symbol | exchange_symbol | short_name | weight | market_cap | revenue_growth | last_change | last_change_day | last_close_price | last_2nd_close_price | last_change_percentage | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | XOM | NYQ | Exxon Mobil Corporation | 0.013441937585551282 | 474511933440 | 0.689 | 1.2800064086914100 | 2023-01-10 | 111.3700027465820300 | 110.0899963378906200 | 0.01162690935843785832 | | CVX | NYQ | Chevron Corporation | 0.009567226003564158 | 337731289088 | 0.81 | -0.8399963378906000 | 2023-01-10 | 175.1999969482422000 | 176.0399932861328000 | -0.00477162218772232275 | | COP | NYQ | ConocoPhillips | 0.004417814517419704 | 155952644096 | 1.237 | 0.1599960327148400 | 2023-01-10 | 118.2399978637695300 | 118.0800018310546900 | 0.00135497993084178221 | | SLB | NYQ | Schlumberger N.V. | 0.0022377960040345308 | 78996119552 | 0.279 | None | None | None | None | None | | EOG | NYQ | EOG Resources, Inc. | 0.0021024604924476343 | 74218659840 | 0.924 | -0.7600021362304600 | 2023-01-10 | 126.8499984741211000 | 127.6100006103515600 | -0.00595566282105957140 | | MPC | NYQ | Marathon Petroleum Corporation | 0.002050747487098508 | 72393146368 | 0.818 | 1.0999984741211000 | 2023-01-10 | 117.5999984741211000 | 116.5000000000000000 | 0.00944204698816394850 | | OXY | NYQ | Occidental Petroleum Corporatio | 0.001695676548826279 | 59858837504 | 0.792 | None | None | None | None | None | | PXD | NYQ | Pioneer Natural Resources Compa | 0.0016017013932190817 | 56541433856 | 0.641 | None | None | None | None | None | | VLO | NYQ | Valero Energy Corporation | 0.0015472850935959574 | 54620491776 | 0.593 | None | None | None | None | None | | HES | NYQ | Hess Corporation | 0.0012744122628788822 | 44987846656 | 0.776 | 1.6300048828125000 | 2023-01-10 | 145.2500000000000000 | 143.6199951171875000 | 0.01134942861878312162 | | PSX | NYQ | Phillips 66 | 0.0012589621320069535 | 44442443776 | 0.799 | None | None | None | None | None | | KMI | NYQ | Kinder Morgan, Inc. | 0.0012062691273686955 | 42582335488 | 0.354 | 0.0400009155273440 | 2023-01-10 | 18.7800006866455080 | 18.7399997711181640 | 0.00213452059850037319 | | DVN | NYQ | Devon Energy Corporation | 0.0012053375130788024 | 42549448704 | 0.789 | -0.2800025939941400 | 2023-01-10 | 62.8499984741210940 | 63.1300010681152340 | -0.00443533326875800678 | | WMB | NYQ | Williams Companies, Inc. (The) | 0.0011162172177908748 | 39403425792 | 0.091 | None | None | None | None | None | | HAL | NYQ | Halliburton Company | 0.0010393744130051477 | 36690808832 | 0.388 | 0.0500030517578160 | 2023-01-10 | 40.9900016784668000 | 40.9399986267089840 | 0.00122137404580161222 | | OKE | NYQ | ONEOK, Inc. | 0.0008854096904464799 | 31255721984 | 0.769 | None | None | None | None | None | | BKR | NMS | Baker Hughes Company | 0.0008807471517968449 | 31091130368 | 0.054 | -0.0400009155273450 | 2023-01-10 | 30.8299999237060550 | 30.8700008392334000 | -0.00129578602008027511 | | FANG | NMS | Diamondback Energy, Inc. | 0.000730020047064916 | 25770334208 | 0.565 | 0.2900085449218600 | 2023-01-10 | 142.2400054931640600 | 141.9499969482422000 | 0.00204303311839874783 | | MRO | NYQ | Marathon Oil Corporation | 0.0005965698099807201 | 21059426304 | 0.731 | 0.0399990081787110 | 2023-01-10 | 27.0499992370605470 | 27.0100002288818360 | 0.00148089625471161593 | | CTRA | NYQ | Coterra Energy Inc. | 0.0005690298110137097 | 20087240704 | 5.204 | -0.3899993896484380 | 2023-01-10 | 24.6900005340576170 | 25.0799999237060550 | -0.01555021494556719473 | | TRGP | NYQ | Targa Resources, Inc. | 0.0004842024080652878 | 17092760576 | 0.773 | None | None | None | None | None | | APA | NMS | APA Corporation | 0.00039910236086756015 | 14088655872 | 0.374 | 0.5799980163574200 | 2023-01-10 | 43.8199996948242200 | 43.2400016784668000 | 0.01341345961709929139 | | EQT | NYQ | EQT Corporation | 0.0003576031445812937 | 12623697920 | 1.092 | -0.6399993896484380 | 2023-01-10 | 33.4000015258789060 | 34.0400009155273440 | -0.01880139166966068805 | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ ========================== MySQL connection is closed ``` ## Module 3: Speeding Up Complex Queries with TiFlash ### Step 1. Execute analytical queries and review execution plans + Connect to TiDB and run an analytical query. ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/pki/tls/certs/ca-bundle.crt ``` ```sql USE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; WITH index_companies AS ( SELECT c.stock_symbol, c.short_name, c.exchange_symbol, ic.weight, c.market_cap AS market_cap, c.revenue_growth AS revenue_growth FROM companies c JOIN index_compositions ic ON c.stock_symbol = ic.stock_symbol WHERE index_symbol = 'SP500' AND sector = 'Energy' ), companies_latest_price AS ( SELECT stock_symbol, DATE(last_day) AS last_change_day, last_close_price, last_2nd_close_price, last_close_price - last_2nd_close_price AS last_change, (last_close_price - last_2nd_close_price) / last_2nd_close_price AS last_change_percentage FROM ( SELECT LAST_VALUE(stock_symbol) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS stock_symbol, LAST_VALUE(record_date) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS last_day, NTH_VALUE(close, 1) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_close_price, NTH_VALUE(close, 2) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_2nd_close_price, ROW_NUMBER() OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS row_num FROM stock_price_history sph WHERE record_date > DATE_SUB((SELECT MAX(record_date) FROM stock_price_history), INTERVAL 2 DAY) AND sph.stock_symbol IN (SELECT stock_symbol FROM index_companies ic) ) sub WHERE row_num = 2 ) SELECT ic.stock_symbol, ic.exchange_symbol, ic.short_name, ic.weight, ic.market_cap, ic.revenue_growth, clp.last_change, clp.last_change_day, clp.last_close_price, clp.last_2nd_close_price, clp.last_change_percentage FROM index_companies ic LEFT JOIN companies_latest_price clp ON ic.stock_symbol = clp.stock_symbol ORDER BY ic.weight DESC; ``` ```output +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | stock_symbol | exchange_symbol | short_name | weight | market_cap | revenue_growth | last_change | last_change_day | last_close_price | last_2nd_close_price | last_change_percentage | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | XOM | NYQ | Exxon Mobil Corporation | 0.013441937585551282 | 474511933440 | 0.689 | 1.2800064086914100 | 2023-01-10 | 111.3700027465820300 | 110.0899963378906200 | 0.01162690935843785832 | | CVX | NYQ | Chevron Corporation | 0.009567226003564158 | 337731289088 | 0.81 | -0.8399963378906000 | 2023-01-10 | 175.1999969482422000 | 176.0399932861328000 | -0.00477162218772232275 | | COP | NYQ | ConocoPhillips | 0.004417814517419704 | 155952644096 | 1.237 | 0.1599960327148400 | 2023-01-10 | 118.2399978637695300 | 118.0800018310546900 | 0.00135497993084178221 | | SLB | NYQ | Schlumberger N.V. | 0.0022377960040345308 | 78996119552 | 0.279 | NULL | NULL | NULL | NULL | NULL | | EOG | NYQ | EOG Resources, Inc. | 0.0021024604924476343 | 74218659840 | 0.924 | -0.7600021362304600 | 2023-01-10 | 126.8499984741211000 | 127.6100006103515600 | -0.00595566282105957140 | | MPC | NYQ | Marathon Petroleum Corporation | 0.002050747487098508 | 72393146368 | 0.818 | 1.0999984741211000 | 2023-01-10 | 117.5999984741211000 | 116.5000000000000000 | 0.00944204698816394850 | | OXY | NYQ | Occidental Petroleum Corporatio | 0.001695676548826279 | 59858837504 | 0.792 | NULL | NULL | NULL | NULL | NULL | | PXD | NYQ | Pioneer Natural Resources Compa | 0.0016017013932190817 | 56541433856 | 0.641 | NULL | NULL | NULL | NULL | NULL | | VLO | NYQ | Valero Energy Corporation | 0.0015472850935959574 | 54620491776 | 0.593 | NULL | NULL | NULL | NULL | NULL | | HES | NYQ | Hess Corporation | 0.0012744122628788822 | 44987846656 | 0.776 | 1.6300048828125000 | 2023-01-10 | 145.2500000000000000 | 143.6199951171875000 | 0.01134942861878312162 | | PSX | NYQ | Phillips 66 | 0.0012589621320069535 | 44442443776 | 0.799 | NULL | NULL | NULL | NULL | NULL | | KMI | NYQ | Kinder Morgan, Inc. | 0.0012062691273686955 | 42582335488 | 0.354 | 0.0400009155273440 | 2023-01-10 | 18.7800006866455080 | 18.7399997711181640 | 0.00213452059850037319 | | DVN | NYQ | Devon Energy Corporation | 0.0012053375130788024 | 42549448704 | 0.789 | -0.2800025939941400 | 2023-01-10 | 62.8499984741210940 | 63.1300010681152340 | -0.00443533326875800678 | | WMB | NYQ | Williams Companies, Inc. (The) | 0.0011162172177908748 | 39403425792 | 0.091 | NULL | NULL | NULL | NULL | NULL | | HAL | NYQ | Halliburton Company | 0.0010393744130051477 | 36690808832 | 0.388 | 0.0500030517578160 | 2023-01-10 | 40.9900016784668000 | 40.9399986267089840 | 0.00122137404580161222 | | OKE | NYQ | ONEOK, Inc. | 0.0008854096904464799 | 31255721984 | 0.769 | NULL | NULL | NULL | NULL | NULL | | BKR | NMS | Baker Hughes Company | 0.0008807471517968449 | 31091130368 | 0.054 | -0.0400009155273450 | 2023-01-10 | 30.8299999237060550 | 30.8700008392334000 | -0.00129578602008027511 | | FANG | NMS | Diamondback Energy, Inc. | 0.000730020047064916 | 25770334208 | 0.565 | 0.2900085449218600 | 2023-01-10 | 142.2400054931640600 | 141.9499969482422000 | 0.00204303311839874783 | | MRO | NYQ | Marathon Oil Corporation | 0.0005965698099807201 | 21059426304 | 0.731 | 0.0399990081787110 | 2023-01-10 | 27.0499992370605470 | 27.0100002288818360 | 0.00148089625471161593 | | CTRA | NYQ | Coterra Energy Inc. | 0.0005690298110137097 | 20087240704 | 5.204 | -0.3899993896484380 | 2023-01-10 | 24.6900005340576170 | 25.0799999237060550 | -0.01555021494556719473 | | TRGP | NYQ | Targa Resources, Inc. | 0.0004842024080652878 | 17092760576 | 0.773 | NULL | NULL | NULL | NULL | NULL | | APA | NMS | APA Corporation | 0.00039910236086756015 | 14088655872 | 0.374 | 0.5799980163574200 | 2023-01-10 | 43.8199996948242200 | 43.2400016784668000 | 0.01341345961709929139 | | EQT | NYQ | EQT Corporation | 0.0003576031445812937 | 12623697920 | 1.092 | -0.6399993896484380 | 2023-01-10 | 33.4000015258789060 | 34.0400009155273440 | -0.01880139166966068805 | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ 23 rows in set, 1 warning (4.50 sec) ``` + Add `EXPLAIN` at the beginning and check the execution plan of the complex query. (This will be a long query, so run `PAGER less -S` beforehand. To switch it back, run `NOPAGER`.) ```sql PAGER less -S ``` ```sql EXPLAIN WITH index_companies AS ( SELECT c.stock_symbol, c.short_name, c.exchange_symbol, ic.weight, c.market_cap AS market_cap, c.revenue_growth AS revenue_growth FROM companies c JOIN index_compositions ic ON c.stock_symbol = ic.stock_symbol WHERE index_symbol = 'SP500' AND sector = 'Energy' ), companies_latest_price AS ( SELECT stock_symbol, DATE(last_day) AS last_change_day, last_close_price, last_2nd_close_price, last_close_price - last_2nd_close_price AS last_change, (last_close_price - last_2nd_close_price) / last_2nd_close_price AS last_change_percentage FROM ( SELECT LAST_VALUE(stock_symbol) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS stock_symbol, LAST_VALUE(record_date) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS last_day, NTH_VALUE(close, 1) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_close_price, NTH_VALUE(close, 2) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_2nd_close_price, ROW_NUMBER() OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS row_num FROM stock_price_history sph WHERE record_date > DATE_SUB((SELECT MAX(record_date) FROM stock_price_history), INTERVAL 2 DAY) AND sph.stock_symbol IN (SELECT stock_symbol FROM index_companies ic) ) sub WHERE row_num = 2 ) SELECT ic.stock_symbol, ic.exchange_symbol, ic.short_name, ic.weight, ic.market_cap, ic.revenue_growth, clp.last_change, clp.last_change_day, clp.last_close_price, clp.last_2nd_close_price, clp.last_change_percentage FROM index_companies ic LEFT JOIN companies_latest_price clp ON ic.stock_symbol = clp.stock_symbol ORDER BY ic.weight DESC; ``` ```output | id | estRows | task | access object | operator info | +------------------------------------------------------+------------+-----------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Sort_125 | 23.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.weight:desc | | └─Projection_127 | 23.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.exchange_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.short_name, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.weight, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.market_cap, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.revenue_growth, Column#118, Column#117, Column#109, Column#108, Column#119 | | └─HashJoin_129 | 23.00 | root | | left outer join, equal:[eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, Column#111)] | | ├─CTEFullScan_156(Build) | 23.00 | root | CTE:index_companies AS ic | data:CTE_0 | | └─Projection_131(Probe) | 1572.00 | root | | Column#111, date(Column#110)->Column#117, Column#109, Column#108, minus(Column#109, Column#108)->Column#118, div(minus(Column#109, Column#108), Column#108)->Column#119 | | └─Selection_132 | 1572.00 | root | | eq(Column#107, 2), not(isnull(Column#111)) | | └─Window_133 | 1965.00 | root | | last_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol)->Column#111 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date range between unbounded preceding and current row) | | └─Window_134 | 1965.00 | root | | last_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date)->Column#110 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date range between unbounded preceding and current row) | | └─Sort_154 | 1965.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date | | └─Window_136 | 1965.00 | root | | nth_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.close, 1)->Column#109 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date desc range between unbounded preceding and current row) | | └─Window_137 | 1965.00 | root | | nth_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.close, 2)->Column#108 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date desc range between unbounded preceding and current row) | | └─Window_139 | 1965.00 | root | | row_number()->Column#107 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date desc rows between current row and current row) | | └─Sort_151 | 1965.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date:desc | | └─HashJoin_144 | 1965.00 | root | | inner join, equal:[eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol)] | | ├─HashAgg_145(Build) | 18.40 | root | | group by:${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, funcs:firstrow(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol)->${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol | | │ └─Selection_146 | 18.40 | root | | not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol)) | | │ └─CTEFullScan_147 | 23.00 | root | CTE:index_companies AS ic | data:CTE_0 | | └─TableReader_150(Probe) | 1965.00 | root | | data:Selection_149 | | └─Selection_149 | 1965.00 | cop[tikv] | | gt(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date, 2023-01-09), not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol)) | | └─TableFullScan_148 | 1157000.00 | cop[tikv] | table:sph | keep order:false | | CTE_0 | 23.00 | root | | Non-Recursive CTE | | └─Projection_113(Seed Part) | 23.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.short_name, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.exchange_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.weight, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.market_cap, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.revenue_growth | | └─HashJoin_115 | 23.00 | root | | inner join, equal:[eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.stock_symbol)] | | ├─TableReader_118(Build) | 23.00 | root | | data:Selection_117 | | │ └─Selection_117 | 23.00 | cop[tikv] | | eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.sector, "Energy"), not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol)), or(1, not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol))) | | │ └─TableFullScan_116 | 493.00 | cop[tikv] | table:c | keep order:false | | └─TableReader_121(Probe) | 493.00 | root | | data:Selection_120 | | └─Selection_120 | 493.00 | cop[tikv] | | eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.index_symbol, "SP500"), not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.stock_symbol)) | | └─TableFullScan_119 | 493.00 | cop[tikv] | table:ic | keep order:false | +------------------------------------------------------+------------+-----------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 29 rows in set (1.08 sec) ``` + Press `Q` to exit the `PAGER` view. ```output mysql> ``` + Turn off the `PAGER`. ```sql NOPAGER ``` ```output PAGER set to stdout mysql> ``` ### Step 2. TiFlash sync settings + Set the following three tables to synchronize with TiFlash: `stock_price_history`, `index_companies`, and `companies`. ```sql ALTER TABLE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies SET TIFLASH REPLICA 2; ALTER TABLE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions SET TIFLASH REPLICA 2; ALTER TABLE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history SET TIFLASH REPLICA 2; ``` ```output ... Query OK, 0 rows affected, 1 warning (0.20 sec) ``` + You can check the synchronization status with the following query. (Synchronization completes when `PROGRESS` is 1 and `AVAILABLE` is 1.) ```sql SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = '${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}'; ``` + In-progress. ```output +----------------------+---------------------+----------+---------------+-----------------+-----------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS | +----------------------+---------------------+----------+---------------+-----------------+-----------+----------+ | lab_demo_201_2370038 | companies | 114 | 2 | | 0 | 0.5 | | lab_demo_201_2370038 | index_compositions | 116 | 2 | | 0 | 0 | | lab_demo_201_2370038 | stock_price_history | 122 | 2 | | 0 | 0 | +----------------------+---------------------+----------+---------------+-----------------+-----------+----------+ 3 rows in set (0.05 sec) ``` + Completed. ```output +----------------------+---------------------+----------+---------------+-----------------+-----------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS | +----------------------+---------------------+----------+---------------+-----------------+-----------+----------+ | lab_demo_201_2370038 | companies | 114 | 2 | | 1 | 1 | | lab_demo_201_2370038 | index_compositions | 116 | 2 | | 1 | 1 | | lab_demo_201_2370038 | stock_price_history | 122 | 2 | | 1 | 1 | +----------------------+---------------------+----------+---------------+-----------------+-----------+----------+ 3 rows in set (0.05 sec) ``` ### Step 3. Run the analytical query again and check the execution plan + Run the same query again and check the timing and execution plan. ```sql WITH index_companies AS ( SELECT c.stock_symbol, c.short_name, c.exchange_symbol, ic.weight, c.market_cap AS market_cap, c.revenue_growth AS revenue_growth FROM companies c JOIN index_compositions ic ON c.stock_symbol = ic.stock_symbol WHERE index_symbol = 'SP500' AND sector = 'Energy' ), companies_latest_price AS ( SELECT stock_symbol, DATE(last_day) AS last_change_day, last_close_price, last_2nd_close_price, last_close_price - last_2nd_close_price AS last_change, (last_close_price - last_2nd_close_price) / last_2nd_close_price AS last_change_percentage FROM ( SELECT LAST_VALUE(stock_symbol) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS stock_symbol, LAST_VALUE(record_date) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS last_day, NTH_VALUE(close, 1) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_close_price, NTH_VALUE(close, 2) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_2nd_close_price, ROW_NUMBER() OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS row_num FROM stock_price_history sph WHERE record_date > DATE_SUB((SELECT MAX(record_date) FROM stock_price_history), INTERVAL 2 DAY) AND sph.stock_symbol IN (SELECT stock_symbol FROM index_companies ic) ) sub WHERE row_num = 2 ) SELECT ic.stock_symbol, ic.exchange_symbol, ic.short_name, ic.weight, ic.market_cap, ic.revenue_growth, clp.last_change, clp.last_change_day, clp.last_close_price, clp.last_2nd_close_price, clp.last_change_percentage FROM index_companies ic LEFT JOIN companies_latest_price clp ON ic.stock_symbol = clp.stock_symbol ORDER BY ic.weight DESC; ``` + Results return much faster than before. ```output +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | stock_symbol | exchange_symbol | short_name | weight | market_cap | revenue_growth | last_change | last_change_day | last_close_price | last_2nd_close_price | last_change_percentage | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | XOM | NYQ | Exxon Mobil Corporation | 0.013441937585551282 | 474511933440 | 0.689 | 1.2800064086914100 | 2023-01-10 | 111.3700027465820300 | 110.0899963378906200 | 0.01162690935843785832 | | CVX | NYQ | Chevron Corporation | 0.009567226003564158 | 337731289088 | 0.81 | -0.8399963378906000 | 2023-01-10 | 175.1999969482422000 | 176.0399932861328000 | -0.00477162218772232275 | | COP | NYQ | ConocoPhillips | 0.004417814517419704 | 155952644096 | 1.237 | 0.1599960327148400 | 2023-01-10 | 118.2399978637695300 | 118.0800018310546900 | 0.00135497993084178221 | | SLB | NYQ | Schlumberger N.V. | 0.0022377960040345308 | 78996119552 | 0.279 | NULL | NULL | NULL | NULL | NULL | | EOG | NYQ | EOG Resources, Inc. | 0.0021024604924476343 | 74218659840 | 0.924 | -0.7600021362304600 | 2023-01-10 | 126.8499984741211000 | 127.6100006103515600 | -0.00595566282105957140 | | MPC | NYQ | Marathon Petroleum Corporation | 0.002050747487098508 | 72393146368 | 0.818 | 1.0999984741211000 | 2023-01-10 | 117.5999984741211000 | 116.5000000000000000 | 0.00944204698816394850 | | OXY | NYQ | Occidental Petroleum Corporatio | 0.001695676548826279 | 59858837504 | 0.792 | NULL | NULL | NULL | NULL | NULL | | PXD | NYQ | Pioneer Natural Resources Compa | 0.0016017013932190817 | 56541433856 | 0.641 | NULL | NULL | NULL | NULL | NULL | | VLO | NYQ | Valero Energy Corporation | 0.0015472850935959574 | 54620491776 | 0.593 | NULL | NULL | NULL | NULL | NULL | | HES | NYQ | Hess Corporation | 0.0012744122628788822 | 44987846656 | 0.776 | 1.6300048828125000 | 2023-01-10 | 145.2500000000000000 | 143.6199951171875000 | 0.01134942861878312162 | | PSX | NYQ | Phillips 66 | 0.0012589621320069535 | 44442443776 | 0.799 | NULL | NULL | NULL | NULL | NULL | | KMI | NYQ | Kinder Morgan, Inc. | 0.0012062691273686955 | 42582335488 | 0.354 | 0.0400009155273440 | 2023-01-10 | 18.7800006866455080 | 18.7399997711181640 | 0.00213452059850037319 | | DVN | NYQ | Devon Energy Corporation | 0.0012053375130788024 | 42549448704 | 0.789 | -0.2800025939941400 | 2023-01-10 | 62.8499984741210940 | 63.1300010681152340 | -0.00443533326875800678 | | WMB | NYQ | Williams Companies, Inc. (The) | 0.0011162172177908748 | 39403425792 | 0.091 | NULL | NULL | NULL | NULL | NULL | | HAL | NYQ | Halliburton Company | 0.0010393744130051477 | 36690808832 | 0.388 | 0.0500030517578160 | 2023-01-10 | 40.9900016784668000 | 40.9399986267089840 | 0.00122137404580161222 | | OKE | NYQ | ONEOK, Inc. | 0.0008854096904464799 | 31255721984 | 0.769 | NULL | NULL | NULL | NULL | NULL | | BKR | NMS | Baker Hughes Company | 0.0008807471517968449 | 31091130368 | 0.054 | -0.0400009155273450 | 2023-01-10 | 30.8299999237060550 | 30.8700008392334000 | -0.00129578602008027511 | | FANG | NMS | Diamondback Energy, Inc. | 0.000730020047064916 | 25770334208 | 0.565 | 0.2900085449218600 | 2023-01-10 | 142.2400054931640600 | 141.9499969482422000 | 0.00204303311839874783 | | MRO | NYQ | Marathon Oil Corporation | 0.0005965698099807201 | 21059426304 | 0.731 | 0.0399990081787110 | 2023-01-10 | 27.0499992370605470 | 27.0100002288818360 | 0.00148089625471161593 | | CTRA | NYQ | Coterra Energy Inc. | 0.0005690298110137097 | 20087240704 | 5.204 | -0.3899993896484380 | 2023-01-10 | 24.6900005340576170 | 25.0799999237060550 | -0.01555021494556719473 | | TRGP | NYQ | Targa Resources, Inc. | 0.0004842024080652878 | 17092760576 | 0.773 | NULL | NULL | NULL | NULL | NULL | | APA | NMS | APA Corporation | 0.00039910236086756015 | 14088655872 | 0.374 | 0.5799980163574200 | 2023-01-10 | 43.8199996948242200 | 43.2400016784668000 | 0.01341345961709929139 | | EQT | NYQ | EQT Corporation | 0.0003576031445812937 | 12623697920 | 1.092 | -0.6399993896484380 | 2023-01-10 | 33.4000015258789060 | 34.0400009155273440 | -0.01880139166966068805 | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ 23 rows in set (0.62 sec) ``` + You can see that the execution plan uses Tiflash (`mpp[tiflash]`). ```sql PAGER less -S EXPLAIN WITH index_companies AS ( SELECT c.stock_symbol, c.short_name, c.exchange_symbol, ic.weight, c.market_cap AS market_cap, c.revenue_growth AS revenue_growth FROM companies c JOIN index_compositions ic ON c.stock_symbol = ic.stock_symbol WHERE index_symbol = 'SP500' AND sector = 'Energy' ), companies_latest_price AS ( SELECT stock_symbol, DATE(last_day) AS last_change_day, last_close_price, last_2nd_close_price, last_close_price - last_2nd_close_price AS last_change, (last_close_price - last_2nd_close_price) / last_2nd_close_price AS last_change_percentage FROM ( SELECT LAST_VALUE(stock_symbol) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS stock_symbol, LAST_VALUE(record_date) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS last_day, NTH_VALUE(close, 1) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_close_price, NTH_VALUE(close, 2) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_2nd_close_price, ROW_NUMBER() OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS row_num FROM stock_price_history sph WHERE record_date > DATE_SUB((SELECT MAX(record_date) FROM stock_price_history), INTERVAL 2 DAY) AND sph.stock_symbol IN (SELECT stock_symbol FROM index_companies ic) ) sub WHERE row_num = 2 ) SELECT ic.stock_symbol, ic.exchange_symbol, ic.short_name, ic.weight, ic.market_cap, ic.revenue_growth, clp.last_change, clp.last_change_day, clp.last_close_price, clp.last_2nd_close_price, clp.last_change_percentage FROM index_companies ic LEFT JOIN companies_latest_price clp ON ic.stock_symbol = clp.stock_symbol ORDER BY ic.weight DESC; ``` ```output +-------------------------------------------------------+---------+--------------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------------------------------+---------+--------------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Sort_164 | 23.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.weight:desc | | └─Projection_166 | 23.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.exchange_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.short_name, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.weight, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.market_cap, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.revenue_growth, Column#118, Column#117, Column#109, Column#108, Column#119 | | └─HashJoin_168 | 23.00 | root | | left outer join, equal:[eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, Column#111)] | | ├─CTEFullScan_199(Build) | 23.00 | root | CTE:index_companies AS ic | data:CTE_0 | | └─Projection_170(Probe) | 1572.00 | root | | Column#111, date(Column#110)->Column#117, Column#109, Column#108, minus(Column#109, Column#108)->Column#118, div(minus(Column#109, Column#108), Column#108)->Column#119 | | └─Selection_171 | 1572.00 | root | | eq(Column#107, 2), not(isnull(Column#111)) | | └─Window_172 | 1965.00 | root | | last_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol)->Column#111 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date range between unbounded preceding and current row) | | └─Window_173 | 1965.00 | root | | last_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date)->Column#110 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date range between unbounded preceding and current row) | | └─Sort_197 | 1965.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date | | └─Window_175 | 1965.00 | root | | nth_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.close, 1)->Column#109 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date desc range between unbounded preceding and current row) | | └─Window_176 | 1965.00 | root | | nth_value(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.close, 2)->Column#108 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date desc range between unbounded preceding and current row) | | └─Window_178 | 1965.00 | root | | row_number()->Column#107 over(partition by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol order by ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date desc rows between current row and current row) | | └─Sort_194 | 1965.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date:desc | | └─HashJoin_183 | 1965.00 | root | | inner join, equal:[eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol)] | | ├─HashAgg_184(Build) | 18.40 | root | | group by:${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, funcs:firstrow(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol)->${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol | | │ └─Selection_185 | 18.40 | root | | not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol)) | | │ └─CTEFullScan_186 | 23.00 | root | CTE:index_companies AS ic | data:CTE_0 | | └─TableReader_193(Probe) | 1965.00 | root | | MppVersion: 1, data:ExchangeSender_192 | | └─ExchangeSender_192 | 1965.00 | mpp[tiflash] | | ExchangeType: PassThrough | | └─Selection_191 | 1965.00 | mpp[tiflash] | | not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.stock_symbol)) | | └─TableFullScan_190 | 1965.00 | mpp[tiflash] | table:sph | pushed down filter:gt(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.stock_price_history.record_date, 2023-01-09), keep order:false | | CTE_0 | 23.00 | root | | Non-Recursive CTE | | └─Projection_131(Seed Part) | 23.00 | root | | ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.short_name, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.exchange_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.weight, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.market_cap, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.revenue_growth | | └─HashJoin_135 | 23.00 | root | | inner join, equal:[eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol, ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.stock_symbol)] | | ├─TableReader_146(Build) | 23.00 | root | | data:Selection_145 | | │ └─Selection_145 | 23.00 | cop[tikv] | | eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.sector, "Energy"), not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol)), or(1, not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.companies.stock_symbol))) | | │ └─TableFullScan_144 | 493.00 | cop[tikv] | table:c | keep order:false | | └─TableReader_153(Probe) | 493.00 | root | | data:Selection_152 | | └─Selection_152 | 493.00 | cop[tikv] | | eq(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.index_symbol, "SP500"), not(isnull(${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}.index_compositions.stock_symbol)) | | └─TableFullScan_151 | 493.00 | cop[tikv] | table:ic | keep order:false | +-------------------------------------------------------+---------+--------------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 30 rows in set (0.52 sec) ``` + Press `Q` to exit the `PAGER` view. ```output mysql> ``` ### Step 4. Check the result by running the same script and confirm the performance is improved + Exit from mysql client and switch to [Terminal 1](ssh://monitor1-ssh#1). ```sql EXIT ``` + Run the script execute the analytical queries again, the results below show that it is faster. ```bash python test01.py ``` ```output ■■■Query 1: Market capitalization by industry■■■ Query execution start time: 02:25:59.895391 Query execution end time: 02:25:59.910081 +------------------------+------------------+ | sector | total_market_cap | +------------------------+------------------+ | Real Estate | 927820086272 | | Technology | 7906855563776 | | Financial Services | 4661122020864 | | Healthcare | 5122903213056 | | Utilities | 1023797270016 | | Consumer Defensive | 2872367655424 | | Communication Services | 3669841019392 | | Basic Materials | 889871172608 | | Energy | 1788549832704 | | Consumer Cyclical | 3238477340160 | | Industrials | 3199253085184 | +------------------------+------------------+ ========================== ■■■Query 2: Market capitalization ranking in the energy industry■■■ Query execution start time: 02:25:59.911859 Query execution end time: 02:26:00.290869 +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | stock_symbol | exchange_symbol | short_name | weight | market_cap | revenue_growth | last_change | last_change_day | last_close_price | last_2nd_close_price | last_change_percentage | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ | XOM | NYQ | Exxon Mobil Corporation | 0.013441937585551282 | 474511933440 | 0.689 | 1.2800064086914100 | 2023-01-10 | 111.3700027465820300 | 110.0899963378906200 | 0.01162690935843785832 | | CVX | NYQ | Chevron Corporation | 0.009567226003564158 | 337731289088 | 0.81 | -0.8399963378906000 | 2023-01-10 | 175.1999969482422000 | 176.0399932861328000 | -0.00477162218772232275 | | COP | NYQ | ConocoPhillips | 0.004417814517419704 | 155952644096 | 1.237 | 0.1599960327148400 | 2023-01-10 | 118.2399978637695300 | 118.0800018310546900 | 0.00135497993084178221 | | SLB | NYQ | Schlumberger N.V. | 0.0022377960040345308 | 78996119552 | 0.279 | None | None | None | None | None | | EOG | NYQ | EOG Resources, Inc. | 0.0021024604924476343 | 74218659840 | 0.924 | -0.7600021362304600 | 2023-01-10 | 126.8499984741211000 | 127.6100006103515600 | -0.00595566282105957140 | | MPC | NYQ | Marathon Petroleum Corporation | 0.002050747487098508 | 72393146368 | 0.818 | 1.0999984741211000 | 2023-01-10 | 117.5999984741211000 | 116.5000000000000000 | 0.00944204698816394850 | | OXY | NYQ | Occidental Petroleum Corporatio | 0.001695676548826279 | 59858837504 | 0.792 | None | None | None | None | None | | PXD | NYQ | Pioneer Natural Resources Compa | 0.0016017013932190817 | 56541433856 | 0.641 | None | None | None | None | None | | VLO | NYQ | Valero Energy Corporation | 0.0015472850935959574 | 54620491776 | 0.593 | None | None | None | None | None | | HES | NYQ | Hess Corporation | 0.0012744122628788822 | 44987846656 | 0.776 | 1.6300048828125000 | 2023-01-10 | 145.2500000000000000 | 143.6199951171875000 | 0.01134942861878312162 | | PSX | NYQ | Phillips 66 | 0.0012589621320069535 | 44442443776 | 0.799 | None | None | None | None | None | | KMI | NYQ | Kinder Morgan, Inc. | 0.0012062691273686955 | 42582335488 | 0.354 | 0.0400009155273440 | 2023-01-10 | 18.7800006866455080 | 18.7399997711181640 | 0.00213452059850037319 | | DVN | NYQ | Devon Energy Corporation | 0.0012053375130788024 | 42549448704 | 0.789 | -0.2800025939941400 | 2023-01-10 | 62.8499984741210940 | 63.1300010681152340 | -0.00443533326875800678 | | WMB | NYQ | Williams Companies, Inc. (The) | 0.0011162172177908748 | 39403425792 | 0.091 | None | None | None | None | None | | HAL | NYQ | Halliburton Company | 0.0010393744130051477 | 36690808832 | 0.388 | 0.0500030517578160 | 2023-01-10 | 40.9900016784668000 | 40.9399986267089840 | 0.00122137404580161222 | | OKE | NYQ | ONEOK, Inc. | 0.0008854096904464799 | 31255721984 | 0.769 | None | None | None | None | None | | BKR | NMS | Baker Hughes Company | 0.0008807471517968449 | 31091130368 | 0.054 | -0.0400009155273450 | 2023-01-10 | 30.8299999237060550 | 30.8700008392334000 | -0.00129578602008027511 | | FANG | NMS | Diamondback Energy, Inc. | 0.000730020047064916 | 25770334208 | 0.565 | 0.2900085449218600 | 2023-01-10 | 142.2400054931640600 | 141.9499969482422000 | 0.00204303311839874783 | | MRO | NYQ | Marathon Oil Corporation | 0.0005965698099807201 | 21059426304 | 0.731 | 0.0399990081787110 | 2023-01-10 | 27.0499992370605470 | 27.0100002288818360 | 0.00148089625471161593 | | CTRA | NYQ | Coterra Energy Inc. | 0.0005690298110137097 | 20087240704 | 5.204 | -0.3899993896484380 | 2023-01-10 | 24.6900005340576170 | 25.0799999237060550 | -0.01555021494556719473 | | TRGP | NYQ | Targa Resources, Inc. | 0.0004842024080652878 | 17092760576 | 0.773 | None | None | None | None | None | | APA | NMS | APA Corporation | 0.00039910236086756015 | 14088655872 | 0.374 | 0.5799980163574200 | 2023-01-10 | 43.8199996948242200 | 43.2400016784668000 | 0.01341345961709929139 | | EQT | NYQ | EQT Corporation | 0.0003576031445812937 | 12623697920 | 1.092 | -0.6399993896484380 | 2023-01-10 | 33.4000015258789060 | 34.0400009155273440 | -0.01880139166966068805 | +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+ ========================== MySQL connection is closed ``` ### Step 5. Go to the TiDB Cloud Console to start your own journey + You can now start your TiDB Cloud journey [here](https://tidbcloud.com/clusters). If you had been using your own free-tier cluster for this lab, you'll find it listed on the same page, the cluster name is ${LAB:SERVERLESS_CLUSTER_NAME}. ## Conclusion TiDB Cloud Serverless takes the capability of TiDB Cloud a step further by offering an effortless, auto-scaling, MySQL-compatible database. This fully managed, multi-tenant offering allows users to focus purely on their applications without the need to worry about the infrastructure that supports them. ```hbsptform { "ui": { "titleLevel": 3 }, "portalId": "4466002", "formId": "89fa8368-eba1-4b84-b91e-eb9eb6613ddf", "region": "na1", "sfdcCampaignId": "701RC00000VqCODYA3" } ```