Issues with TiFlash Indexes

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tiflash索引问题

| username: 青木科技-熊猛

Under what circumstances does TiFlash use indexes?
Currently, it is found that in most cases, TiFlash performs a TableFullScan. Even if indexes are built on the table, they are ignored.

| username: 啦啦啦啦啦 | Original post link

TiFlash itself cannot use indexes, right? Have you ever found it using indexes? :joy:

| username: 青木科技-熊猛 | Original post link

Yes, clustered index.
If it can’t use the index, it’s a bug. Aggregating large tables will be very slow.

| username: 青木科技-熊猛 | Original post link

The default value of the tidb_enable_clustered_index parameter is INT_ONLY, which means that only tables with integer primary keys will use clustered indexes by default.

| username: MrSylar | Original post link

This can’t be considered a bug, tilash does not synchronize index data.

| username: 有猫万事足 | Original post link

You need to understand column storage. TiFlash uses column storage.

https://zhuanlan.zhihu.com/p/127823207

To quote a line from “The God of Cookery,” in column storage, there are basically no indexes, or you could say every column has an index. :rofl:

| username: tidb菜鸟一只 | Original post link

TiFlash uses what index? It is a columnar database, so it essentially has its own index on each column.

| username: ShawnYan | Original post link

Even if an index is created, it is only created on TiKV, not on TiFlash. TiFlash performs table scans, which is different from traditional row storage.

| username: 青木科技-熊猛 | Original post link

Ok. Then why does it sometimes perform a TableRangeScan? Normally, it should be a TableFullScan.

| username: ShawnYan | Original post link

Indeed, there is. By adding a range query on the primary key in the WHERE condition, for example:

mysql> explain select /*+ read_from_storage(tiflash[t]) */ * from t where uid > 1;
+------------------------+---------+--------------+---------------+----------------------------------+
| id                     | estRows | task         | access object | operator info                    |
+------------------------+---------+--------------+---------------+----------------------------------+
| TableReader_7          | 1002.00 | root         |               | data:TableRangeScan_6            |
| └─TableRangeScan_6     | 1002.00 | cop[tiflash] | table:t       | range:(1,+inf], keep order:false |
+------------------------+---------+--------------+---------------+----------------------------------+
2 rows in set (0.01 sec)
| username: TiDBer_iCdTOZ1r | Original post link

Did not use the index.

| username: redgame | Original post link

On TiFlash, indexes are usually not as good as full scans.

| username: TiDBer_iCdTOZ1r | Original post link

The index has become invalid.

| username: Kongdom | Original post link

Yes, TiFlash generally does not use indexes. Although it performs a full table scan, the speed is still impressive!

| username: ShawnYan | Original post link

This should be helpful to you as well

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.