How much slower is int compared to varchar for a table with 10,000 rows?

Translated
1

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

Original topic: 咨询一个问题1w条数据的表int会比varchar慢多少？

| username: tidb狂热爱好者 
CREATE TABLE `bot_order111` (
  `id` bigint(20) NOT NULL COMMENT 'Order list primary key ID',
  `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT 'User ID',
  `symbol` varchar(255) NOT NULL DEFAULT '' COMMENT 'Trading ID',
  `order_id` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Order ID',
  `strategy_type` varchar(255) NOT NULL DEFAULT 'DEAL' COMMENT 'Strategy type',
  `order_status` varchar(255) NOT NULL DEFAULT 'INIT' COMMENT 'Order type: initial order, new order, partially filled, fully filled, canceled, pending cancellation, abnormal order',
  `trade_type` varchar(255) NOT NULL DEFAULT 'BID' COMMENT 'Trade type: buy, sell',
  `limit_price` decimal(36,18) NOT NULL DEFAULT '1.000000000000000000' COMMENT 'Limit trade price',
  `limit_quantity` decimal(36,18) NOT NULL DEFAULT '1.000000000000000000' COMMENT 'Limit trade quantity',
  `denominated_quantity` decimal(36,18) NOT NULL DEFAULT '1.000000000000000000' COMMENT 'Denominated trade quantity',
  `trade_quantity` decimal(36,18) NOT NULL DEFAULT '1.000000000000000000' COMMENT 'Trade quantity',
  `trade_avg_price` decimal(36,18) NOT NULL DEFAULT '1.000000000000000000' COMMENT 'Trade average price',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `created_by` varchar(255) NOT NULL DEFAULT 'SYSTEM' COMMENT 'Created by',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
  `updated_by` varchar(255) NOT NULL DEFAULT 'SYSTEM' COMMENT 'Updated by',
  `strategy_type_child` varchar(30) NOT NULL COMMENT 'Strategy subtype',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_limit_price` (`limit_price`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_symbol` (`symbol`),
  KEY `idx_strategy_type` (`strategy_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='Order table'




 
【 TiDB Usage Environment】Production
【 TiDB Version】
【Encountered Problem】
【Reproduction Path】`What operations were performed that led to the problem`
【Problem Phenomenon and Impact】
SELECT
  id,
  user_id,
  symbol,
  order_id,
  strategy_type,
  strategy_type_child,
  order_status,
  trade_type,
  limit_price,
  limit_quantity,
  denominated_quantity,
  trade_quantity,
  trade_avg_price,
  created_at,
  created_by,
  updated_at,
  updated_by
FROM
  bot_order
WHERE
  (
    symbol = 'rmb'
    AND order_status = '0'
    AND trade_type = 'SELL'
    AND strategy_type = '2'
  )
【Attachments】

> Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.
2
| username: TiDBer_jYQINSnf | Original post link

I don’t understand the connection between the SQL you posted and the title.
You can use sysbench to test the issue mentioned in the title.

3
| username: tidb狂热爱好者 | Original post link

The problem has been found. Data is being inserted and deleted simultaneously, with 5 million records being inserted per minute during the stress test.

4
| username: tidb狂热爱好者 | Original post link

Training class development engineer

5
| username: Mark | Original post link

Character types can only be set to varchar, but if it’s purely numerical, int is more efficient.

6
| username: tidb狂热爱好者 | Original post link

Yes, use varchar for storing 0 and 1 types.

7
| username: system | Original post link

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