Performance/efficiency issues when using subquery in INSERT?
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;