pingcap / pingcap/tidb

create table with foreign key is slower than normal table

Open
#59,889 0 comments 0 reactions 0 assignees View on GitHub
type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
tidb 16c32g
create table with foreign key:
![Image](https://github.com/user-attachments/assets/efcf779b-06c9-43ba-82b2-901197181d9d)

without foreign key:

![Image](https://github.com/user-attachments/assets/37bdafe7-27c6-45b5-93d7-ca8d5953c67a)

table:
```go

var (
TableSQL1 = "CREATE TABLE `%s` (" +
" product_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT," +
" product_name VARCHAR(255) NOT NULL," +
" description TEXT," +
" price DECIMAL(10, 2) NOT NULL," +
" stock_quantity INT UNSIGNED NOT NULL," +
" category_id INT UNSIGNED," +
" created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
" updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
");"
TableSQL2 = "CREATE TABLE `%s` (" +
" order_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT," +
" customer_id BIGINT UNSIGNED NOT NULL," +
" order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
" total_amount DECIMAL(10, 2) NOT NULL," +
" shipping_address VARCHAR(255)," +
" order_status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') NOT NULL DEFAULT 'pending'," +
" created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
" updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" +
");"
TableSQL3 = "CREATE TABLE `%s` (" +
" order_item_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT," +
" order_id BIGINT UNSIGNED NOT NULL," +
" product_id BIGINT UNSIGNED NOT NULL," +
" quantity INT UNSIGNED NOT NULL," +
" item_price DECIMAL(10, 2) NOT NULL," +
" created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
" updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP," +
" FOREIGN KEY (order_id) REFERENCES `%s`(order_id)," +
" FOREIGN KEY (product_id) REFERENCES `%s`(product_id)," +
" INDEX idx_order_id (order_id)," +
" INDEX idx_product_id (product_id)" +
");"

DatabaseSQL = "CREATE DATABASE `%s`"
)
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.