optimize the cost of compile on table with a large number of columns

[dbsid]

Enhancement

On a table t with 549 columns, running query like select * from t limit 1, high cpu cost(45%) on planner.buildLogicalPlan is observed.
Would like to optimizer the cpu cost for this case.

[Rustin170506]

I cannot reproduce this issue. I tried following steps:

1. Create a v8.2.0 cluser: tiup cluster deploy upstream v8.2.0 upstream.yaml -p &&tiup cluster start upstream
2. Create a table with 1000 columns:

---cargo
[dependencies]
clap = { version = "4.2", features = ["derive"] }
sqlx = { version = "0.7", features = ["runtime-tokio-rustls", "mysql"] }
tokio = { version = "1", features = ["full", "rt-multi-thread"] }
---

use clap::Parser;
use sqlx::mysql::MySqlPoolOptions;
use tokio::time::Instant;

#[derive(Parser, Debug)]
#[command(version)]
struct Args {
    #[arg(short, long, help = "MySQL connection string")]
    database_url: String,
}

async fn create_large_table(pool: &sqlx::MySqlPool) -> Result<(), sqlx::Error> {
    let mut query = String::from("CREATE TABLE IF NOT EXISTS large_table (");

    for i in 1..=1000 {
        query.push_str(&format!("column_{} VARCHAR(255)", i));
        if i < 1000 {
            query.push_str(", ");
        }
    }

    query.push_str(")");

    sqlx::query(&query)
        .execute(pool)
        .await?;

    println!("Created table with 1000 columns.");
    Ok(())
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let args = Args::parse();
    let pool = MySqlPoolOptions::new()
        .max_connections(5)
        .connect(&args.database_url)
        .await?;

    let start_time = Instant::now();

    create_large_table(&pool).await?;

    let duration = start_time.elapsed();
    println!("Finished creating the table in {:?}.", duration);

    Ok(())
}

3. Try running the SQL: select * from large_table limit 1; couple of times.
4. Check the profile:

MySQL [test]> select * from large_table limit 1;
Empty set (0.03 sec)

5. Check the metrics:

I agree that it is somewhat slow, but not excessively slow.

[Rustin170506]

@dbsid I am going to close this issue. If you can still reproduce this issue please feel free to open it with more detailed information. Thank you!

[Rustin170506]

I can still try to make it better.