Are there any optimization solutions for slow select queries on the information_schema.tables table in the TiDB database?

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

Original topic: TIDB数据库查询information_schema.tables表时,select慢,有什么优化方案吗

| username: 每天当牛马

Are there any optimization solutions for slow SELECT queries on the information_schema.tables table?
The query is:

SELECT 1 FROM information_schema.tables WHERE table_schema='core' and table_name ='ifc34041' LIMIT 1;
| username: dba远航 | Original post link

This is querying database metadata, there’s really no good method.

| username: 每天当牛马 | Original post link

In the same scenario, MySQL queries are much faster. Is there no optimization plan?

| username: zhanggame1 | Original post link

How about trying without LIMIT 1?

| username: 江湖故人 | Original post link

Is the cluster load high, and how slow is the select?
Can you provide the explain analyze output for us to take a look?

| username: dba远航 | Original post link

There is a noticeable slowness when querying metadata or some memory tables, and this cannot be adjusted.

| username: 路在何chu | Original post link

Are there many tables?

| username: 哈喽沃德 | Original post link

Are there too many objects?

| username: 江湖故人 | Original post link

You can check with the following query:

SELECT
    db_name,
    table_name,
    ROUND(SUM(total_size / cnt), 2) Approximate_Size,
    ROUND(SUM(total_size / cnt / (SELECT
                    ROUND(AVG(value), 2)
                FROM
                    METRICS_SCHEMA.store_size_amplification
                WHERE
                    value > 0)),
            2) Disk_Size
FROM
    (SELECT
        db_name,
            table_name,
            region_id,
            SUM(Approximate_Size) total_size,
            COUNT(*) cnt
    FROM
        information_schema.TIKV_REGION_STATUS
    WHERE  table_name IN ('TABLES')
    GROUP BY db_name , table_name , region_id) tabinfo
GROUP BY db_name , table_name;
| username: 托马斯滑板鞋 | Original post link

I remember someone asked this question before. At that time, I traced the SQL and interacted with tens of thousands of regions, which shocked me :joy:

| username: linnana | Original post link

Metadata queries are indeed slower than MySQL.

| username: 连连看db | Original post link

I tried it myself, and it’s quite slow on a single machine, with a difference of more than 10 times. TiDB really needs to be optimized in this area. :joy:

| username: 烂番薯0 | Original post link

This won’t be fast.

| username: kelvin | Original post link

This can’t be fast, right? There’s nothing much to optimize for a simple query statement.

| username: 江湖故人 | Original post link

What you mentioned is probably similar :thinking:
Querying metadata tables is extremely slow - :ringer_planet: TiDB Technical Issues / Performance Tuning - TiDB Q&A Community (asktug.com)

| username: 等一分钟 | Original post link

Okay, I’ll give it a try.

| username: 等一分钟 | Original post link

The post you shared is the one I posted.

| username: 江湖故人 | Original post link

Hahaha :joy:

| username: linnana | Original post link

The metadata table should not be slow since there is a table cache.

| username: 每天当牛马 | Original post link

TIDB has enabled this configuration tidb_enable_prepared_plan_cache: true