MySQL SELECT Query on Only 300k Rows Running Very Slow

Question

I have issue with MySQL SELECT Query time. This is my table:

CREATE TABLE `order_items` (
  `id_order_item` bigint(20) NOT NULL,
  `order_id` bigint(20) NOT NULL,
  `order_item_id` bigint(20) NOT NULL,
  `seller_sku` varchar(100) NOT NULL,
  `shop_sku` varchar(100) NOT NULL,
  `product_name` varchar(255) DEFAULT NULL,
  `item_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `paid_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `tax_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `shipping_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `shipping_service_cost` decimal(10,2) NOT NULL DEFAULT '0.00',
  `voucher_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `status` varchar(50) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `order_items`
  ADD PRIMARY KEY (`id_order_item`),
  ADD KEY `seller_sku` (`seller_sku`,`shop_sku`),
  ADD KEY `order_id` (`order_id`,`order_item_id`);

I have only 300k rows on this table, and Query like:

SELECT * FROM `order_items` WHERE order_item_id=584706188389248

took 8 - 12 second.

I use 16GB RAM and 8vCPU VPS. and this is my my.cnf:

symbolic-links=0

performance-schema=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=100000

max_connections = 500
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
innodb_buffer_pool_size=12G
innodb_log_file_size=512M
innodb_buffer_pool_instances=6
slow_query_log=1
long_query_time=1
log_output=FILE
slow_query_log_file=/var/lib/mysql/slow.log

Any suggestion how to fix this issue? Thank You before.

I got the query time from mysql log file. I try executed query from phpmyadmin and got the same result. — Hengky Mulyono, Apr 16 '21 at 11:08
Reverse the column order in `ADD KEY order_id (order_id,order_item_id);` — Strawberry, Apr 16 '21 at 13:05
Incidentally a table containing columns named `id_order_item` and `order_item_id` is a wondrous thing to behold. Why not have an `item_id_order` column, too? — Strawberry, Apr 16 '21 at 13:08

Martin · Accepted Answer · 2021-04-16T11:09:11.273

Try setting an index on just the order_item_id column because this is the only column referenced in your select statement.

You currently have an index on two combined columns, the order_item_id and order_id.

ALTER TABLE `order_items`
      ADD KEY `order_item_id` (`order_item_id`);

Sitenote: You should probably be using the charset/collations for utf8mb4 rather than basic utf8 as this will actually encompass all UTF-8 characters. Why?

MySQL SELECT Query on Only 300k Rows Running Very Slow

1 Answers1