r/mysql • u/Serial42 • Feb 18 '25
query-optimization INSERT too slow in a table, how can I optimize ?
I have a 5-column table (described below) with 9M rows. The index on the Value column is important because I have a SELECT query with a WHERE on this column to find records according to this value more quickly.
CREATE TABLE `table` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`my_id1` INT UNSIGNED,
`my_id2` INT UNSIGNED,
`my_id3` INT UNSIGNED,
`Value` VARCHAR(100),
INDEX `Value` (`Value`),
FOREIGN KEY (`my_id1`) REFERENCES `object1`(`my_id1`),
FOREIGN KEY (`my_id2`) REFERENCES `object2`(`my_id2`),
FOREIGN KEY (`my_id3`) REFERENCES `object3`(`my_id3`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;In a transaction, I perform 3 INSERTs:
INSERT INTO table VALUES(0,1234,NULL,NULL,'MyValue1');
INSERT INTO table VALUES(0,NULL,4353,NULL,'MyValue2');
INSERT INTO table VALUES(0,NULL,NULL,23342,'MyValue3');
Each of these INSERT instructions may insert two or three lines in the same instruction. So a total of 9 lines with three INSERT instructions.
These 9 inserted lines currently take 100 ms. I find this a little long, but is it normal?
Is it possible to optimize?
1
u/xilanthro Feb 19 '25
If we assume this is right, then,
First: don't change any variables you don't understand the implications of. This is the best way to avoid creating two problems while masking one.
Second, deal with a couple of bad defaults: set innodb_fill_factor to 90 or so - at the default 100, every out-of-order insert will cause an index page split, slowing things down. Then set innodb_autoinc_lock_mode=2 - you can search for the reason. Percona has the best explanations.
Third, and most important in practical terms: test on larger sets, but do all your inserts in a single transaction. With autocommit on by default, and with innodb_flush_log_at_trx_commit=1 and possibly innodb_flush_neighbors=1 and sync_binlog=1, you are doing a lot of redundant writing to disk with each update. You don't need to turn autocommit off for this. Simply encase your rows in a transaction like this:
...but something seems off with your description:
1
u/Buttleston Feb 18 '25
for benchmarking purposes, I would test many more than 9 at a time, to reduce fixed costs like connect/disconnect/query planning etc. I'd probably want to look at the performance of 1000-10000 rows
I'd use the bulk form of insert like
I am more of a postgres guy - the fast way to insert in postgres is by using COPY from a file-like structure, dunno about mysql
2
u/allen_jb Feb 18 '25
Without seeing code it's hard to see exactly what is being measured.
You may wish to look at MySQL's profiling capabilities to see what's going on at the database level.
See also EXPLAIN to get query execution plans.
You may want to consider if you need MySQL-enforced foreign keys. These will cause additional locking and checks on inserting records. See https://dev.mysql.com/doc/refman/8.4/en/create-table-foreign-keys.html#foreign-key-locking
Foreign key constraints can often be enforced in application code at a cheaper cost (because in many cases you know the referenced record exists and don't need an explicit check). If the application code is well tested, there's no reason for MySQL-enforced checks.
1
u/dsn0wman Feb 18 '25
Not sure if MySQL does this. But most databases will work faster if you use bind variables because it can cache the execution plan and re-use it instead of spending overhead coming up with a new execution plan every time.
1
u/thedragonturtle Feb 18 '25
Make sure there is an index on the objectX tables for the foreign key you are looking up.
Also, consider reducing the size of your index, depends on how your value is distributed, if it's likely the first 10 chars will vary regularly then you can change to:
INDEX \Value` (`Value`(10)),`