MySQL Forums
Forum List  »  Newbie

Performance/efficiency issues when using subquery in INSERT?
Posted by: Michael Botsko
Date: January 23, 2022 03:03PM

I have an `activity` table that could potentially see hundreds of thousands of new rows per day. It contains several foreign keys to establish relationships with other tables. For example, `action_id` maps to an `actions` table.

Until now, my java application has loaded all `actions` on start and cached their models so I can lookup the id from a `Map` when doing an insert into activity. No big deal when you have 100 values.

But it occurs to me that it's pointless. Why use up memory and a HashMap lookup for data that's already present in the database? Especially because I now have a table that has 10k records - why load all of that into memory and lookup when I can just query the db.

I feel like it would be smarter and no less efficient to query those ids by a subquery:

INSERT INTO activity (action_id, product_id) VALUES (
(SELECT action_id FROM actions WHERE action= ...)
(SELECT product_id FROM products WHERE product = ... AND state = ...)
)



Some example schema:

CREATE TABLE `activity` (
`activity_id` INT NOT NULL AUTO_INCREMENT,
`action_id,` INT NOT NULL,
`product_id` INT NOT NULL,
`user_id` INT NOT NULL,
PRIMARY KEY (`activity_id`));

CREATE TABLE `actions` (
`action_id` INT NOT NULL AUTO_INCREMENT,
`action,` varchar(25) NOT NULL,
PRIMARY KEY (`action_id`));

CREATE TABLE `product_states` (
`product_id` mediumint(5) NOT NULL AUTO_INCREMENT,
`product` varchar(63) NOT NULL,
`states` varchar(255) DEFAULT NULL,
PRIMARY KEY (`product_id`),
UNIQUE KEY `productstate` (`product`,`states`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Options: ReplyQuote


Subject
Written By
Posted
Performance/efficiency issues when using subquery in INSERT?
January 23, 2022 03:03PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.