prefix 검색이 왜이리 오래 걸리지..

카테고리 없음

prefix 검색이 왜이리 오래 걸리지..

gw1 2026. 2. 13. 22:55

CREATE TABLE `orders` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `order_status` varchar(30) NOT NULL DEFAULT 'CREATED',
  `order_date` datetime DEFAULT NULL,
  `price` bigint DEFAULT '0',
  `amount` bigint DEFAULT '0',
  `sum_price` bigint GENERATED ALWAYS AS ((`price` * `amount`)) STORED,
  PRIMARY KEY (`id`),
  KEY `order_date` (`order_date`),
  KEY `sum_price` (`sum_price`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8388611 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

order 수 838만개

CREATE TABLE `user` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `user_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8847222 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

user 수 884만개

SELECT
	count(*)
FROM
    orders AS o
JOIN
    user AS u
        ON o.user_id = u.id
WHERE
    o.user_id LIKE '123456%'
    
-------------------------------
SELECT
	o.* , u.*
FROM
    orders AS o
JOIN
    user AS u
        ON o.user_id = u.id
WHERE
    o.user_id LIKE '123456%'
ORDER BY
    o.id DESC
LIMIT 30;