MySQL INSERT or SELECT

Question

I have 2 tables, table_a and table_b.

CREATE TABLE `table_a` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `val` varchar(64) NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `val` (`val`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `table_b` (
 `ref_a` int(11) unsigned NOT NULL,
 `data` int(11) unsigned NOT NULL,
 UNIQUE KEY `unique_index` (`ref_a`,`data`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I would like to MASS INSERT into Table B with ref_a value referring to ID of Table A.

This is what I'm trying to accomplish:

SELECT ID FROM table_a WHERE val = "123"

If the value doesn't exist, then insert the value

INSERT INTO table_a(val) VALUES("123")

Now that I got the ID (assume it's "1"), I want to insert into table_b:

INSERT INTO table_b(ref_a, data) VALUES(1, 75)

The problem comes in when I want to do it in bulk. Will my performance go down if I alternate between SELECTS and INSERTS instead of doing a bulk insert, then bulk select?

I could do:

INSERT INTO table_b(ref_a, data) VALUES((SELECT ID FROM table_a WHERE value="123"), 75)

but, what if the value is missing and I need to do an insert prior.

I cannot BEGIN TRANSACTION and COMMIT giving that I need to retrieve the ID of table A after an insert.

I can also do:

• Bulk insert into table A.
• Select all IDs from table A.
• Insert into table B using selected IDs.

But im looking for an alternative way.

What would be the MOST EFFICIENT way to accomplish what im trying to do?

Answer 1

First make sure you have a UNIQUE INDEX on value column in table_a, then do a bulk INSERT IGNORE

INSERT IGNORE INTO table_a(value) VALUES("123"), ("345"), ("afasd"), ("#$#$%"), ...

Now you are sure that all the values are in table_a and you can safely use your bulk table_b insert method.

Answer 2

I suggest you to use a stored procedure that will do the checking for a right insert: stored routine

It will be something like

CREATE PROCEDURE insertdata(IN i_value VARCHAR(32), IN i_data INT(11) )
BEGIN
DECLARE v_insertID INT DEFAULT -1;

SELECT ID INTO v_insertID  FROM table_a WHERE value = i_value;

IF -1 = v_insertID
THEN
   INSERT INTO table_a(value) VALUES(i_value);
   SELECT ID INTO v_insertID  FROM table_a WHERE value = i_value;
END IF;

IF -1 != v_insertID
THEN
   INSERT INTO table_b(ref_a, data) VALUES(v_insertID, i_data);
END IF;

END

We check if an entry with given value exists, if not we create it, we retrieve the create entry ID and then insert the data in the table_b (if the entry was really created)

So you only have to call the routine from php for each entry. It will be far more optimized than doing all those test in php (which will require more than one mysql call)