I want to add a lot of lines in my mysql database. And for each of those lines I need to do a search in the database. I will describe how I am doing it right now, but there must be a better way for this.
In a while loop I loop trough an array in which I have the data for that I need to insert the values for value1, value2, value3.
Then I do a SELECT id FROM table WHERE column='value3';
And after that I INSERT INTO insertTable VALUES (value1, value2, id);
This method results in a lot of queries. A possible improvement could be to instead of queuing the inserts, put the insert values in a array again and create a multi insert at the end. But there are still a lot of SELECT queries.
Any clues how to improve this?
here's one way,
you could build a table to contain all your value1,value2,value3 values like in a loop build up a statement that is similar to below:
first time through the loop make your sqlstatement to something like
CREATE TABLE T SELECT value1 as value1 ,value2 as value2,value3 as value3
Then everytime after that through the loop set your sqlstatement to append
UNION SELECT value1, value2, value3
Then execute that sql statement so that you have the table T with your values.
Then you can run a single INSERT like this.
INSERT INTO insertTable(value1,value2,id)
SELECT T.value1,T.value2,`table`.id
FROM T INNER JOIN `table` ON `table`.`column` = T.value3;
here's an sqlfiddle example http://sqlfiddle.com/#!9/58daf/1
then you can drop table T afterwards.
Or if you want, you can just build the SELECT UNION query without the create Table T and just put that query in brackets and put it after the FROM and Before the T directly in the INSERT statement and so you have one query.