How to utilize both KV and TiFlash to solve a single query?

[den-crane]

I have a wide table: 50 varchar(50) columns, 100 million rows.
I use TiFlash to fullscan data using like in these columns, but I need all columns.

If I query all columns using TiFlash

select * from benchmark_table where attr_14 like '%70404 Port Viachester%';
1 row in set (8.140 sec)

If I query the key column only using TiFlash

select key_col from benchmark_table where attr_14 like '%70404 Port Viachester%';
+---------------------+
| key_col             |
+---------------------+
| 5452530124722999447 |
+---------------------+
1 row in set (0.631 sec)

If I query the whole row using this key (TiKV)

select * from benchmark_table where key_col = '5452530124722999447';
1 row in set (0.006 sec)

So theoretically query can be executed for 0.631 sec + 0.006 sec = 0.637 sec

But it works for 52.929 sec

select * from benchmark_table 
where key_col in ( select key_col from benchmark_table where attr_14 like '%70404 Port Viachester%');

1 row in set (52.929 sec)

So for now my options are:

• use TiFlash -> 8.140 sec
• use two separate queries -> 0.637 sec

[windtalker]

TiFlash support later materialization, so in the ideal case

select * from benchmark_table where attr_14 like '%70404 Port Viachester%';

will first only read the attr_14 column(and of cause the mvcc column, which is relatively small), evaluate the like filter, then read the other columns after that. However, when to enable later materialization is total based on cost model, which i think in your case later materialization is not enabled. So TiFlash has to read all the columns before evaluate the like filter.
You can check the pushed down filter field to see if later materialization is enabled or not.(empty means not enabled)

+-------------------------+----------+--------------+---------------+-------------------------------------------------------+
| id                      | estRows  | task         | access object | operator info                                         |
+-------------------------+----------+--------------+---------------+-------------------------------------------------------+
| TableReader_12          | 12288.00 | root         |               | MppVersion: 1, data:ExchangeSender_11                 |
| └─ExchangeSender_11     | 12288.00 | mpp[tiflash] |               | ExchangeType: PassThrough                             |
|   └─TableFullScan_9     | 12288.00 | mpp[tiflash] | table:t1      | pushed down filter:lt(test.t1.a, 1), keep order:false |
+-------------------------+----------+--------------+---------------+-------------------------------------------------------+

For the second query

select * from benchmark_table 
where key_col in ( select key_col from benchmark_table where attr_14 like '%70404 Port Viachester%');

I believe that TiDB's optimize convert the in subquery into join, can you try the no_decorrelate hint?

select * from benchmark_table 
where key_col in ( select /*+ NO_DECORRELATE() */ key_col from benchmark_table where attr_14 like '%70404 Port Viachester%');

[den-crane]

I use default settings, it's a fresh installation - v8.5.3.

MySQL [test]> SHOW VARIABLES LIKE 'tidb_opt_enable_late_materialization';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| tidb_opt_enable_late_materialization | ON    |
+--------------------------------------+-------+

MySQL [test]> explain select * from benchmark_table where attr_14 like '%98144 East Viachester%';
+----------------------------+-------------+--------------+-----------------------+----------------------------------------------------------------------------------+
| id                         | estRows     | task         | access object         | operator info                                                                    |
+----------------------------+-------------+--------------+-----------------------+----------------------------------------------------------------------------------+
| TableReader_16             | 15847940.80 | root         |                       | MppVersion: 2, data:ExchangeSender_15                                            |
| └─ExchangeSender_15        | 15847940.80 | mpp[tiflash] |                       | ExchangeType: PassThrough                                                        |
|   └─Selection_14           | 15847940.80 | mpp[tiflash] |                       | like(test.benchmark_table.attr_14, "%98144 East Viachester%", 92)                |
|     └─TableFullScan_13     | 19809926.00 | mpp[tiflash] | table:benchmark_table | pushed down filter:empty, keep order:false, stats:partial[attr_14:unInitialized] |
+----------------------------+-------------+--------------+-----------------------+----------------------------------------------------------------------------------+

can you try the no_decorrelate hint?

it does not help:

-- no hint
select * from benchmark_table  
where key_col in ( select key_col from benchmark_table where attr_14 like '%98144 East Viachester%');

1 row in set (11.681 sec)

-- hint
select * from benchmark_table  
where key_col in ( select /*+ NO_DECORRELATE() */ key_col from benchmark_table where attr_14 like '%98144 East Viachester%');

1 row in set, 1 warning (11.791 sec)


MySQL [test]> explain select * from benchmark_table  where key_col in ( select key_col from benchmark_table where attr_14 like '%98144 East Viachester%');
+----------------------------------------+-------------+--------------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                     | estRows     | task         | access object         | operator info                                                                                                                                            |
+----------------------------------------+-------------+--------------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| TableReader_76                         | 15847940.80 | root         |                       | MppVersion: 2, data:ExchangeSender_75                                                                                                                    |
| └─ExchangeSender_75                    | 15847940.80 | mpp[tiflash] |                       | ExchangeType: PassThrough                                                                                                                                |
|   └─Projection_71                      | 15847940.80 | mpp[tiflash] |                       | test.benchmark_table.key_col, test.benchmark_table.attr_01, test.benchmark_table.attr_02, test.benchmark_table.attr_03, test.benchmark_table.attr_04, ...|
|     └─HashJoin_70                      | 15847940.80 | mpp[tiflash] |                       | inner join, equal:[eq(test.benchmark_table.key_col, test.benchmark_table.key_col)], stream_count: 24                                                     |
|       ├─ExchangeReceiver_44(Build)     | 15847940.80 | mpp[tiflash] |                       | stream_count: 24                                                                                                                                         |
|       │ └─ExchangeSender_43            | 15847940.80 | mpp[tiflash] |                       | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.benchmark_table.key_col, collate: utf8mb4_bin], stream_count: 24                  |
|       │   └─Selection_42               | 15847940.80 | mpp[tiflash] |                       | like(test.benchmark_table.attr_14, "%98144 East Viachester%", 92)                                                                                        |
|       │     └─TableFullScan_41         | 19809926.00 | mpp[tiflash] | table:benchmark_table | pushed down filter:empty, keep order:false, stats:partial[attr_14:unInitialized, ID 65:unInitialized]                                                    |
|       └─ExchangeReceiver_47(Probe)     | 19809926.00 | mpp[tiflash] |                       |                                                                                                                                                          |
|         └─ExchangeSender_46            | 19809926.00 | mpp[tiflash] |                       | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.benchmark_table.key_col, collate: utf8mb4_bin]                                    |
|           └─TableFullScan_45           | 19809926.00 | mpp[tiflash] | table:benchmark_table | keep order:false, stats:partial[attr_14:unInitialized, ID 65:unInitialized]                                                                              |
+----------------------------------------+-------------+--------------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+

[windtalker]

Although tidb_opt_enable_late_materialization is on by default, as I mentioned before, which predicate to pushdown to TableScan is totally cost decided, and in you case the optimizer decide not to pushdown to TableScan.
One of the reason is that in TiDB, we does estimate the filter rate for complex filter like like, currently we simply use 0.8 as the match rate. There is another variable to control the math rate: tidb_default_string_match_selectivity, as workaround, you can also try this query

select /*+ set_var(tidb_default_string_match_selectivity=0.00001) */ * from benchmark_table where attr_14 like '%70404 Port Viachester%';

[den-crane]

I see. Thanks. It helps.

select /*+ set_var(tidb_default_string_match_selectivity='0.00001') */ * from benchmark_table_save where attr_14 like '%98144 East Viachester%';

1 row in set (0.101 sec)


MySQL [test]> explain select /*+ set_var(tidb_default_string_match_selectivity='0.00001') */ * from benchmark_table_save where attr_14 like '%98144 East Viachester%';
+--------------------------+---------+--------------+----------------------------+-------------------------------------------------------------------------------------------------------------+
| id                       | estRows | task         | access object              | operator info                                                                                               |
+--------------------------+---------+--------------+----------------------------+-------------------------------------------------------------------------------------------------------------+
| TableReader_16           | 199.10  | root         |                            | MppVersion: 2, data:ExchangeSender_15                                                                       |
| └─ExchangeSender_15      | 199.10  | mpp[tiflash] |                            | ExchangeType: PassThrough                                                                                   |
|   └─TableFullScan_13     | 199.10  | mpp[tiflash] | table:benchmark_table_save | pushed down filter:like(test.benchmark_table_save.attr_14, "%98144 East Viachester%", 92), keep order:false |
+--------------------------+---------+--------------+----------------------------+-------------------------------------------------------------------------------------------------------------+


select * from benchmark_table_save where attr_14 like '%98144 East Viachester%';

1 row in set (1.274 sec)

[terry1purcell]

In 8.5.3 - I expect that TiDB has tidb_analyze_column_options=PREDICATE. Which means - if you run ANALYZE (either via auto-analyze or manually) before TiDB is registered which columns are involved in local or join predicates, statistics may not have been collected on the column. When set to PREDICATE - statistics are only collected on indexed columns (regardless of their predicate usage) or columns registered as having predicates in queries. This can be an issue for OLAP benchmark tests with TiFlash, but less so for OLTP workload benchmarks (since critical columns are typically indexed), or for "mature" applications (where all predicates have been seen.

You can confirm if you have statistics collected on a column by issuing a SHOW STATS_HISTOGRAMS;

If you don't have statistics on the attr_14 column - you can change the variable to ALL (and rerun ANALYZE), or you can use "ALL COLUMNS" option of ANALYZE.

So before trying to hint or change the query - I would check the statistics on the columns. Once that is ruled out - then I would look at other reasons/issues.

[den-crane]

(I renamed the table to benchmark_table_save)

SHOW VARIABLES LIKE 'tidb_opt_enable_late_materialization';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| tidb_opt_enable_late_materialization | ON    |
+--------------------------------------+-------+



select * from benchmark_table_save where attr_14 like '%98144 East Viachester%';

1 row in set (1.270 sec)



ANALYZE TABLE benchmark_table_save PREDICATE COLUMNS;

Query OK, 0 rows affected, 1 warning (11.589 sec)



SHOW STATS_HISTOGRAMS;
+---------+---------------------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-----------------------+-------------+-----------------+----------------+----------------+---------------+
| Db_name | Table_name                | Partition_name | Column_name | Is_index | Update_time         | Distinct_count | Null_count | Avg_col_size | Correlation           | Load_status | Total_mem_usage | Hist_mem_usage | Topn_mem_usage | Cms_mem_usage |
+---------+---------------------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-----------------------+-------------+-----------------+----------------+----------------+---------------+
| test    | benchmark_table_save      |                | attr_14     |        0 | 2025-12-18 20:09:11 |       18032640 |          0 |        18.81 | 0.0021469113191354555 | allLoaded   |           26152 |          26152 |              0 |             0 |
| test    | benchmark_table_save      |                | key_col     |        0 | 2025-12-18 20:09:11 |       19909633 |          0 |        20.41 |                     1 | allLoaded   |           28968 |          28968 |              0 |             0 |
| test    | benchmark_table_save      |                | PRIMARY     |        1 | 2025-12-18 20:09:11 |       19909633 |          0 |            0 |                     0 | allLoaded   |           33064 |          33064 |              0 |             0 |
+---------+---------------------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-----------------------+-------------+-----------------+----------------+----------------+---------------+


select * from benchmark_table_save where attr_14 like '%98144 East Viachester%';

1 row in set (1.394 sec)


explain select * from benchmark_table_save where attr_14 like '%98144 East Viachester%';
+----------------------------+-------------+--------------+----------------------------+------------------------------------------------------------------------+
| id                         | estRows     | task         | access object              | operator info                                                          |
+----------------------------+-------------+--------------+----------------------------+------------------------------------------------------------------------+
| TableReader_16             | 15927706.40 | root         |                            | MppVersion: 2, data:ExchangeSender_15                                  |
| └─ExchangeSender_15        | 15927706.40 | mpp[tiflash] |                            | ExchangeType: PassThrough                                              |
|   └─Selection_14           | 15927706.40 | mpp[tiflash] |                            | like(test.benchmark_table_save.attr_14, "%98144 East Viachester%", 92) |
|     └─TableFullScan_13     | 19909633.00 | mpp[tiflash] | table:benchmark_table_save | pushed down filter:empty, keep order:false                             |
+----------------------------+-------------+--------------+----------------------------+------------------------------------------------------------------------+


select * from benchmark_table_save 
where key_col in ( select key_col from benchmark_table_save where attr_14 like '%98144 East Viachester%');

1 row in set (11.812 sec)

meanwhile

select key_col from benchmark_table_save where attr_14 like '%98144 East Viachester%';

+----------------------+
| key_col              |
+----------------------+
| 11309164472340419145 |
+----------------------+

1 row in set (0.130 sec)

select * from benchmark_table_save where key_col = '11309164472340419145';

1 row in set (0.002 sec)

[windtalker]

@den-crane
Can you show the result of

explain select * from benchmark_table  
where key_col in ( select /*+ NO_DECORRELATE() */ key_col from benchmark_table where attr_14 like '%98144 East Viachester%');

And also, for

select * from benchmark_table  
where key_col in ( select /*+ NO_DECORRELATE() */ key_col from benchmark_table where attr_14 like '%98144 East Viachester%');

1 row in set, 1 warning (11.791 sec)

I notice that there is a warning, can you use show warnings the see what is the warning?

[den-crane]

MySQL [test]> explain select * from benchmark_table_save   where key_col in ( select /*+ NO_DECORRELATE() */ key_col from benchmark_table_save where attr_14 like '%98144 East Viachester%');
+----------------------------------------+-------------+--------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                     | estRows     | task         | access object              | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+----------------------------------------+-------------+--------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TableReader_76                         | 15927706.40 | root         |                            | MppVersion: 2, data:ExchangeSender_75                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| └─ExchangeSender_75                    | 15927706.40 | mpp[tiflash] |                            | ExchangeType: PassThrough                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|   └─Projection_71                      | 15927706.40 | mpp[tiflash] |                            | test.benchmark_table_save.key_col, test.benchmark_table_save.attr_01, test.benchmark_table_save.attr_02, test.benchmark_table_save.attr_03, test.benchmark_table_save.attr_04, test.benchmark_table_save.attr_05, test.benchmark_table_save.attr_06, test.benchmark_table_save.attr_07, test.benchmark_table_save.attr_08, test.benchmark_table_save.attr_09, test.benchmark_table_save.attr_10, test.benchmark_table_save.attr_11, test.benchmark_table_save.attr_12, test.benchmark_table_save.attr_13, test.benchmark_table_save.attr_14, test.benchmark_table_save.attr_15, test.benchmark_table_save.attr_16, test.benchmark_table_save.attr_17, test.benchmark_table_save.attr_18, test.benchmark_table_save.attr_19, test.benchmark_table_save.attr_20, test.benchmark_table_save.attr_21, test.benchmark_table_save.attr_22, test.benchmark_table_save.attr_23, test.benchmark_table_save.attr_24, test.benchmark_table_save.attr_25, test.benchmark_table_save.attr_26, test.benchmark_table_save.attr_27, test.benchmark_table_save.attr_28, test.benchmark_table_save.attr_29, test.benchmark_table_save.attr_30, test.benchmark_table_save.attr_31, test.benchmark_table_save.attr_32, test.benchmark_table_save.attr_33, test.benchmark_table_save.attr_34, test.benchmark_table_save.attr_35, test.benchmark_table_save.attr_36, test.benchmark_table_save.attr_37, test.benchmark_table_save.attr_38, test.benchmark_table_save.attr_39, test.benchmark_table_save.attr_40, test.benchmark_table_save.attr_41, test.benchmark_table_save.attr_42, test.benchmark_table_save.attr_43, test.benchmark_table_save.attr_44, test.benchmark_table_save.attr_45, test.benchmark_table_save.attr_46, test.benchmark_table_save.attr_47, test.benchmark_table_save.attr_48, test.benchmark_table_save.attr_49 |
|     └─HashJoin_70                      | 15927706.40 | mpp[tiflash] |                            | inner join, equal:[eq(test.benchmark_table_save.key_col, test.benchmark_table_save.key_col)], stream_count: 24                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|       ├─ExchangeReceiver_44(Build)     | 15927706.40 | mpp[tiflash] |                            | stream_count: 24                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|       │ └─ExchangeSender_43            | 15927706.40 | mpp[tiflash] |                            | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.benchmark_table_save.key_col, collate: utf8mb4_bin], stream_count: 24                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|       │   └─Selection_42               | 15927706.40 | mpp[tiflash] |                            | like(test.benchmark_table_save.attr_14, "%98144 East Viachester%", 92)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|       │     └─TableFullScan_41         | 19909633.00 | mpp[tiflash] | table:benchmark_table_save | pushed down filter:empty, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|       └─ExchangeReceiver_47(Probe)     | 19909633.00 | mpp[tiflash] |                            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|         └─ExchangeSender_46            | 19909633.00 | mpp[tiflash] |                            | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.benchmark_table_save.key_col, collate: utf8mb4_bin]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|           └─TableFullScan_45           | 19909633.00 | mpp[tiflash] | table:benchmark_table_save | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+----------------------------------------+-------------+--------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
11 rows in set, 1 warning (0.006 sec)

MySQL [test]> select * from benchmark_table
    -> where key_col in ( select /*+ NO_DECORRELATE() */ key_col from benchmark_table where attr_14 like '%98144 East Viachester%');
Empty set, 1 warning (0.329 sec)

MySQL [test]> show warnings;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1815 | NO_DECORRELATE() is inapplicable because there are no correlated columns. |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.000 sec)