Is it necessary to execute additional SELECT and INSERT IGNORE queries each time while inserting data into MySQL table that depends on FOREIGN KEYs?

Question

I have 4 MySQL tables with depending on each other via FOREIGN KEYs.

Please checkout this below schema for tables structure:

CREATE DATABASE IF NOT EXISTS courses
    CHARACTER SET latin1
    COLLATE latin1_bin;

CREATE TABLE IF NOT EXISTS courses.institution
(
    icode INT UNSIGNED NOT NULL AUTO_INCREMENT,
    iname VARCHAR(255) NOT NULL,
    PRIMARY KEY (icode),
    UNIQUE (iname)
)
    ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS courses.cities
(
    ccode INT UNSIGNED NOT NULL AUTO_INCREMENT,
    cname VARCHAR(255) NOT NULL,
    PRIMARY KEY (ccode),
    UNIQUE (cname)
)
    ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS courses.skills
(
    scode INT UNSIGNED NOT NULL AUTO_INCREMENT,
    sname VARCHAR(255) NOT NULL,
    PRIMARY KEY (scode),
    UNIQUE (sname)
)
    ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS courses.relation
(
    icode INT UNSIGNED NOT NULL,
    scode INT UNSIGNED NOT NULL,
    ccode INT UNSIGNED NOT NULL,
    UNIQUE KEY ucols (icode, scode, ccode),
    FOREIGN KEY (icode) REFERENCES courses.institution (icode),
    FOREIGN KEY (scode) REFERENCES courses.skills (scode),
    FOREIGN KEY (ccode) REFERENCES courses.cities (ccode)
)
    ENGINE = InnoDB;

Currently I'm executing this below queries to insert just one record in relation table.

It costs 4 INSERT queries and 3 SELECT subqueries for just one insert on each time.

INSERT IGNORE INTO institution(iname) VALUES ('ABC Learners');
INSERT IGNORE INTO skills(sname) VALUES ('PHP');
INSERT IGNORE INTO cities(cname) VALUES ('Bangalore');

INSERT IGNORE INTO relation (icode, scode, ccode) VALUES (
    (SELECT icode FROM institution WHERE iname = 'ABC Learners'),
    (SELECT scode FROM skills WHERE sname = 'PHP'),
    (SELECT ccode FROM cities WHERE cname = 'Bangalore')
);

Is it necessary to execute all this queries every time? or is there any better way to perform this action in single or few queries?

Checkout this below simple PHP code. In this code for just inserting 7 records on relation table it executes 4 INSERT queries with 3 SELECT subqueries on each record.

It costs total 7 * 7 = 49 queries for 7 records. How to solve this?

<?php

$db = new mysqli('localhost', 'user', '****', 'courses');

$records = [
    ['ABC Learners', 'CSS', 'Bangalore'],
    ['ABC Learners', 'PHP', 'Bangalore'],
    ['ABC Learners', 'HTML', 'Bangalore'],
    ['ABC Learners', 'PHP', 'Hyderabad'],
    ['XYZ Solutions', 'PHP', 'Hyderabad'],
    ['XYZ Solutions', 'JAVA', 'Hyderabad'],
    ['XYZ Solutions', 'JAVA', 'Bangalore'],
];

foreach ($records as $record) {

    list($institute, $skill, $city) = $record;

    $db->query("INSERT IGNORE INTO institution (iname) VALUES ('{$institute}')");
    $db->query("INSERT IGNORE INTO skills (sname) VALUES ('{$skill}')");
    $db->query("INSERT IGNORE INTO cities (cname) VALUES ('{$city}')");

    $db->query(
        "INSERT IGNORE INTO relation (icode, scode, ccode) VALUES (" .
        "(SELECT icode FROM institution WHERE iname = '{$institute}'), " .
        "(SELECT scode FROM skills WHERE sname = '{$skill}'), " .
        "(SELECT ccode FROM cities WHERE cname = '{$city}'))"
    );
}

$db->close();

NOTE: Above script is example purpose. Using batch mode or disabling auto commit is not useful for me. Because many times I need to add single new record to relation table (based on user request via web panel)

---

UPDATE 1:

After some research and benchmarks I have created a MySQL stored function to speed up this process and it increased performance by 250 - 300%

Please checkout function here:

DELIMITER $$
CREATE FUNCTION courses.record(i_name VARCHAR(255), s_name VARCHAR(255), c_name VARCHAR(255)) RETURNS INT
BEGIN
    DECLARE _icode, _scode, _ccode INT UNSIGNED;

    SELECT icode INTO _icode FROM institution WHERE iname = i_name;
    SELECT scode INTO _scode FROM skills WHERE sname = s_name;
    SELECT ccode INTO _ccode FROM cities WHERE cname = c_name;

    IF _icode IS NULL THEN
        INSERT IGNORE INTO institution (iname) VALUES (i_name);
        SELECT icode INTO _icode FROM institution WHERE iname = i_name;
    END IF;

    IF _scode IS NULL THEN
        INSERT IGNORE INTO skills (sname) VALUES (s_name);
        SELECT scode INTO _scode FROM skills WHERE sname = s_name;
    END IF;

    IF _ccode IS NULL THEN
        INSERT IGNORE INTO cities (cname) VALUES (c_name);
        SELECT ccode INTO _ccode FROM cities WHERE cname = c_name;
    END IF;

    INSERT IGNORE INTO relation (icode, scode, ccode) VALUES (_icode, _scode, _ccode);

    RETURN ROW_COUNT();
END $$
DELIMITER ;

Now, this below PHP script can insert one record in relation table with just one query

<?php

$db = new mysqli('localhost', 'user', '***', 'courses');

$records = [
    ['ABC Learners', 'CSS', 'Bangalore'],
    ['ABC Learners', 'PHP', 'Bangalore'],
    ['ABC Learners', 'HTML', 'Bangalore'],
    ['ABC Learners', 'PHP', 'Hyderabad'],
    ['XYZ Solutions', 'PHP', 'Hyderabad'],
    ['XYZ Solutions', 'JAVA', 'Hyderabad'],
    ['XYZ Solutions', 'JAVA', 'Bangalore'],
];

$query = $db->prepare("SELECT record (?, ?, ?)");
$query->bind_param('sss', $institute, $skill, $city);

foreach ($records as $record) {
    list($institute, $skill, $city) = $record;
    $query->execute();
}

$db->close();

This MySQL stored function increased the performance. But, still I'm using multiple INSERT and SELECT statements in this function. Is it possible to optimize this function with few statements to gain more performance?

Answer 1

1. Change UNIQUE to PRIMARY key:

In absence of an explicit Primary Key defined, InnoDB will create its own hidden primary key for every row. But in your case, in the table relation, the current UNIQUE key is a better candidate to be defined as PRIMARY key instead (NOT NULL and Uniqueness satisfied). So change that to PRIMARY KEY instead.

2. Utilize Batch Inserts/Selects:

We can basically break this into 4 Inserts and 3 Selects overall, by using batch insert and select, and utilizing some application catching. Check the code below (with comments):

$records = [
    ['ABC Learners', 'CSS', 'Bangalore'],
    ['ABC Learners', 'PHP', 'Bangalore'],
    ['ABC Learners', 'HTML', 'Bangalore'],
    ['ABC Learners', 'PHP', 'Hyderabad'],
    ['XYZ Solutions', 'PHP', 'Hyderabad'],
    ['XYZ Solutions', 'JAVA', 'Hyderabad'],
    ['XYZ Solutions', 'JAVA', 'Bangalore'],
];

// Create a copy of records to avoid changing the original
$records_copy = $records;

// Get unique institutions, skills and cities
$i = array_unique(array_map('array_shift', $records_copy)); 
$s = array_unique(array_map('array_shift', $records_copy)); 
$c = array_unique(array_map('array_shift', $records_copy)); 

// Prepare batch insert and select SQL for institution table
$i_ins_sql = "INSERT IGNORE INTO institution (iname) VALUES ";
$i_sel_sql = "SELECT icode, iname FROM institution WHERE iname IN (";

foreach ($i as $v) {

    $i_ins_sql .= "('" . $db->real_escape_string($v) . "'),";
    $i_sel_sql .= "'" . $db->real_escape_string($v) . "',";
}

// Execute the INSERT query
$db->query( rtrim($i_ins_sql, ',') );

// Execute the SELECT query and fetch the query result and store (cache) it
// Key of the cache array would be name (string) and the value would be 
// the code (integer)
$i_cache = array();
$i_sel_q = $db->query( rtrim($i_sel_sql, ',') . ")" );
while ( $row = $i_sel_q->fetch_assoc() ) {
    $i_cache[$row['iname']] = $row['icode'];
}

/**
 * REPEAT the INSERT and SELECT steps for the skills and cities tables, 
 * using $s and $c arrays respectively, with appropriate field names
 * Eventually, you would have cache arrays, $s_cache and $c_cache.
 **/

// Now let's do a batch INSERT INTO relation table 
$sql = "INSERT IGNORE INTO relation (icode, scode, ccode) VALUES ";

// Loop over original records array
foreach ($records as $record) {

    $sql .= "(" . (int)$i_cache[$record[0]] . ","
                . (int)$i_cache[$record[1]] . "," 
                . (int)$i_cache[$record[2]] . "),";
}

// Execute the Batch INSERT query into relation table
$db->query( rtrim($sql, ',') );

Last, but not least: Your code is open to SQL injection related attacks. Even real_escape_string cannot secure it completely. Please learn to use Prepared Statements instead.