What is the fastest way to insert a row into two tables

Question

I have two mysql tables and I want to insert a letter into them.

I need to split the email into nameand domain nameand paste each part of them into one table.

My table domainis as follows:

+----+--------+
| id | domain | primary(id)
+----+--------+

and my table email:

+-------+----------+
| eMail | domainId | primary key(eMail, domainId)
+-------+----------+

ie: [e-mail] > example.commust be inserted into the table domainand infomust be added to the table emailwith domainId.

The domain must be unique.

My attempt:

foreach($emails as $email)
{
    list($account,$hostname) = explode('@',$email,2);

    $query = $dbh->prepare("SELECT id FROM domain WHERE domain LIKE :domain LIMIT 0,1");
    $query->execute(array(':domain'=>trim($hostname)));

    if($query->rowCount())
    {
        $id = $query->fetch();
        $id = $id['id'];
    }else{
        $insert = $dbh->prepare("INSERT INTO domain (domain) VALUES (:domain)");
        $insert->execute(array(':domain'=>trim($hostname)));
        $id = $dbh->lastInsertId();
    }

    $name = $dbh->prepare("INSERT INTO email (eMail, domainId) VALUES (:eMail, :domainId)");
    $name->execute(array(':eMail'=>trim($account),':domainId'=>$id));
}

but this method is very slow when I want to add bulk emails:

Is there any faster way or maybe I can do this in one request ..?

Answer 1

, php, db.

, $insert $name .

$domains = array();

foreach($dbh->query("SELECT id, domain FROM domain") as $domain) {
    $domains[$domain['domain']] = $domain['id'];
}

$insert = $dbh->prepare("INSERT INTO domain (domain) VALUES (:domain)");
$name = $dbh->prepare("INSERT INTO email (eMail, domainId) VALUES (:eMail,:domainId)");

foreach($emails as $email)
{
    list($account,$hostname) = explode('@',$email,2);
    $hostname = trim($hostname);

    if(!isset($domains[$hostname])) {
        $insert->execute(array(':domain'=>$hostname));
        $id = $dbh->lastInsertId();
        $domains[$hostname] = $id;
    }
    else {
        $id = $domains[$hostname];
    }    

    $name->execute(array(':eMail'=>trim($account),':domainId'=>$id));
}

Answer 2

email, domain

DELIMITER |
CREATE TRIGGER insert_domain_trigger BEFORE INSERT ON email 
FOR EACH ROW 
begin
  set @domainname = substring(NEW.email, instr(NEW.email, '@') + 1);
  if(select count(*) from domain where domain = @domainname) = 0
  then
     insert into domain (domain) values (@domainname);
  end if;
end
|

Answer 3

, ?

, UNIQUE INSERT IGNORE INTO.

DELIMITER $$

CREATE PROCEDURE `sp_InsertEmail`(IN p_Email VARCHAR(100))
BEGIN

-- Declare variables
DECLARE v_Email VARCHAR(100);
DECLARE v_Domain VARCHAR(100);

-- Set the variables
SET v_Email = SUBSTRING(p_Email,1,INSTR(p_email,'@')-1);
SET v_Domain =SUBSTRING(p_Email,INSTR(p_Email,'@'));

-- Now insert ignore into domain table

INSERT IGNORE INTO db.domain (domain) VALUES(v_Domain);

-- Then insert like this.

INSERT IGNORE INTO db.email SELECT v_Email,DomainId FROM domain WHERE domain=v_Domain;

END

CALL db.sp_InsertEmail('[e-mail]')