Why is it very slow even when partitioning by a single value?

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

Original topic: 为什么只单独分区值也会非常慢呢

| username: TiDBer_pIbgN3Km

I have a question. My TiDB uses school_id for partitioning. When I query with school_id, it is very fast. However, when I want to query all school_ids (select school_id from xxx), I find that it doesn’t use the index and needs to query each partition one by one, which is very slow. Is there any way to solve this problem?

| username: zhanggame1 | Original post link

Of course, a full table scan is slow. Adding DISTINCT should be much faster.

| username: TiDBer_pIbgN3Km | Original post link

However, adding it is also slow. Shouldn’t there be an index tree to store the partition key? Why is a full table scan necessary?

| username: zhanggame1 | Original post link

TiDB defaults to clustered tables, meaning the data is ordered by key, and the primary key does not actually have an index.

| username: zhang_2023 | Original post link

Full table scan is slow.

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

There is no such default index tree. You can create an index for the partition key, which will make queries a bit faster. However, since TiDB only has partitioned indexes, it will still scan all partitioned indexes, making the speed somewhat slow compared to global indexes.

| username: zhaokede | Original post link

Without a partition key, it’s similar to broadcasting, so the speed will definitely be slower.

| username: TiDBer_rvITcue9 | Original post link

Thank you for sharing, I have gained a lot!

| username: GreenGuan | Original post link

Currently, none of the versions of TiDB have global index functionality for partitioned tables. You can test it and see that even querying through the primary key will scan all partitions. This feature will be improved in TiDB 8.x.

| username: zhanggame1 | Original post link

What is the primary key of your table?

| username: TiDBer_小阿飞 | Original post link

Does adding a primary key “clustered index” work?

| username: h5n1 | Original post link

Is school_id the primary key? Your SQL has no conditions, so it will definitely do a full table scan. A full table scan of the primary key is the same as a full index scan. If it’s a secondary index, you can try doing an ANALYZE TABLE to see if it can perform a full index scan. Also, upload the execution plan from EXPLAIN ANALYZE. TiDB uses local partitioning, so a secondary index will need to scan all partitions.

| username: Lystorm | Original post link

Is the index effective?

| username: TiDBer_嘎嘣脆 | Original post link

Try upgrading the configuration.