## 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. {% if TIDB_ENVIRONMENT == 'TiDB Cloud Starter' %} ```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/ssl/certs/ca-certificates.crt \ -D ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME} ``` {% elif TIDB_ENVIRONMENT == 'TiDB Cloud Essential' %} ```bash mysql \ --comments -u ${SERVERLESS_CLUSTER_USERNAME} -p${SERVERLESS_CLUSTER_PASSWORD} \ -h ${SERVERLESS_CLUSTER_HOST} -P ${SERVERLESS_CLUSTER_PORT} \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt \ -D lab_demo_${LAB:LAB_INSTANCE_ID} ``` {% endif %} ```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; ``` ```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) ``` + The next query output will be a long query, so run `PAGER less -S` beforehand. To switch it back, run `NOPAGER`. ```sql PAGER less -S ``` + Add `EXPLAIN` at the beginning and check the execution plan of the complex query. > [!NOTE] > Pay attention to the task column. It shows the query will be executed by `root` and `[cop]tikv`, which means the TiDB server and TiKV row store. ```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 companies SET TIFLASH REPLICA 2; ALTER TABLE index_compositions SET TIFLASH REPLICA 2; ALTER TABLE 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.) {% if TIDB_ENVIRONMENT == 'TiDB Cloud Starter' %} ```sql SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = '${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}'; ``` {% elif TIDB_ENVIRONMENT == 'TiDB Cloud Essential' %} ```sql SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'lab_demo_${LAB:LAB_INSTANCE_ID}'; ``` {% endif %} + 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 {% if TIDB_ENVIRONMENT == 'TiDB Cloud Starter' %} + Double check the row count for each table. You will find that table `stock_price_history` has much more rows than the other two tables. By doing this, you are also triggering TiDB Cloud to dynamically assign the TiFlash resource to your TiDB Cloud Starter cluster. This is NOT a required step in TiDB Cloud Dedicated, TiDB Cloud Essential, and TiDB Cloud Premium in order to utilize the columnar store. TiDB Cloud Starter delays TiFlash allocation until first query access. ```sql SELECT COUNT(*) FROM stock_price_history UNION ALL SELECT COUNT(*) FROM index_compositions UNION ALL SELECT COUNT(*) FROM companies; ``` ```output +----------+ | COUNT(*) | +----------+ | 493 | | 493 | | 1157000 | +----------+ 3 rows in set (6.71 sec) ``` {% endif %} + 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; ``` + You will get the result instantly. ```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) ``` + The execution plan uses `mpp[tiflash]` for `stock_price_history`, but for the other two tables, the optimizer chooses `cop[tikv]` due to their small row count which making TiFlash unnecessary. ```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. {% if TIDB_ENVIRONMENT == 'TiDB Cloud Starter' %} ```bash ~/.venv/bin/python test01.py ``` {% elif TIDB_ENVIRONMENT == 'TiDB Cloud Essential' %} ```bash ~/.venv/bin/python test01.py \ $SERVERLESS_CLUSTER_USERNAME \ $SERVERLESS_CLUSTER_PASSWORD \ $SERVERLESS_CLUSTER_HOST \ lab_demo_${LAB:LAB_INSTANCE_ID} \ $SERVERLESS_CLUSTER_PORT ``` {% endif %} ```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 {% if TIDB_ENVIRONMENT == 'TiDB Cloud Starter' %} + 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}. {% elif TIDB_ENVIRONMENT == 'TiDB Cloud Essential' %} + You can now begin your TiDB Cloud journey [here](https://tidbcloud.com/clusters). After completing this lab, you may delete the cluster you created in the `tidblabs-project` project via the TiDB Cloud Console. {% endif %}