Mysql – Inserting into multiple tables simultanteously (HEIDISQL)

MySQL

I have three tables.

flatpack_apn

components_apn

flatpackcomponents_apn

CREATE TABLE `component_apn` (
`componentno` INT(11) NOT NULL AUTO_INCREMENT,
`description` VARCHAR(30) NOT NULL,
PRIMARY KEY (`componentno`)
)
COLLATE='hp8_english_ci'
ENGINE=InnoDB
;


CREATE TABLE `flatpackcomponents_apn` (
`flatpackid` INT(11) NOT NULL,
`componentno` INT(11) NOT NULL,
`quantity` INT(10) NOT NULL,
PRIMARY KEY (`componentno`, `flatpackid`),
INDEX `flatpackid` (`flatpackid`),
INDEX `componentno` (`componentno`),
CONSTRAINT `flatpackcomponents_apn_ibfk_1` FOREIGN KEY (`flatpackid`) REFERENCES `flatpack_apn` (`flatpackid`),
CONSTRAINT `flatpackcomponents_apn_ibfk_2` FOREIGN KEY (`componentno`) REFERENCES `component_apn` (`componentno`)
)
COLLATE='hp8_english_ci'
ENGINE=InnoDB
;


CREATE TABLE `flatpack_apn` (
    `flatpackid` INT(11) NOT NULL AUTO_INCREMENT,
    `name` TINYTEXT NOT NULL,
    `colour` TEXT NULL DEFAULT NULL,
    `flatpacktype` ENUM('Office','Kitchen','Bedroom','General') NOT NULL,
    `unitprice` DECIMAL(5,2) NULL DEFAULT NULL,
    PRIMARY KEY (`flatpackid`)
)
COLLATE='hp8_english_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2123
;

I am asked to:

Populate the tables with sample data using suitable data values and choosing values which are not offensive.
Flatpack: at least 5 flatpacks with at least 2 having more than 3 components
Component: at least 10 components

I am a little uncertain as to how to this.

I thought that:

INSERT INTO flatpack_apn 
SELECT *FROM components_apn


INSERT INTO flatpack_apn

SELECT quantity FROM flatpackcomponents_apn;

Is it possible to run them simultanteously, or would I need to execute them as two seperate events? Is there any downside to simply running them as two seperate insert events? In truth, I am not entirely sure I am interpreting the question above correctly.

As a sidebar; and apologies if I am asking too much in one question, but, I figured that I would be required to create/insert new columns within the table before adding the rows/records. Would this be a correct assumption?

Best Answer

The overhead and complication of spinning off two threads to run the queries simultaneously are almost always more costly than doing them sequentially.

Copying an entire table over is normally a rare task. As such, the performance is often not a big deal. Are you doing this as part of the normal flow? If so, let's look at it in more detail to see if there is a better way to achieve the ultimate goal.

Don't use TINYTEXT. VARCHAR(255) (or some smaller number) is virtually equivalent, yet more efficient in some situations.

Redundancy:

PRIMARY KEY (`componentno`, `flatpackid`),
INDEX `componentno` (`componentno`),    -- DROP, since the PK handles it.
Related Question