MySQL Insert if not Updated

Question

I have three tables in MySQL

wi_indv_training(trn_id,ind_id,is_deleted)
wi_trn_org(trn_id,grp_id,is_deleted)
wi_indv_org(ind_id,grp_id,is_deleted)

Here, Multiple groups can be assigned to a training which is handled by wi_trn_org table. Multiple Individuals can be assigned to many groups. And Many Individuals are involved in training. Here, we are provided individuals are already associated with groups. Now while Inserting a group to training, individuals in that group must be associated with the training. There might already be the group and individuals(some or all) in the given training flagged with is_deleted=yes.

Now,While Inserting, I want to flag the deleted group and individual in training with is_deleted=no, if the data is already in respective tables else the data should be inserted as new data. For This I have implemented following approach which is very slow in performance;

foreach ($grp_id as $key => $value) {
    # code...
    $rs=$this->db->pdoQuery("SELECT * FROM wi_trn_org 
                             WHERE trn_id='$trn_id' 
                             AND grp_id='$value'")->results();            
    if(count($rs)>0)
    {
        $this->db->pdoQuery("UPDATE wi_trn_org SET is_deleted=0 
                            WHERE trn_id='$trn_id' AND grp_id='$value'");
        $grp_indv=$this->db->pdoQuery("SELECT ind_id 
                                       FROM wi_indv_org 
                                       WHERE grp_id='$value' 
                                       AND is_deleted=0")->results();
        foreach ($grp_indv as $ke => $va) 
        {
            $ind_id=$va['ind_id'];
            $rows=$this->db->pdoQuery("SELECT * FROM wi_indv_training 
                                      WHERE trn_id='$trn_id' 
                                      AND ind_id='$ind_id'")->results();
            if(count($rows)>0)
            {
                $this->db->pdoQuery("UPDATE wi_indv_training 
                                     SET is_deleted=0,indv_source='Group' 
                                     WHERE trn_id='$trn_id' 
                                       AND ind_id='$ind_id'");
            }
            else
            {
                $this->db->insert("wi_indv_training",
                                   array("ind_id"=>$ind_id,
                                         "indv_source"=>'Group',
                                         "trn_id"=>$trn_id,
                                         "attendance"=>"yes",
                                         "active"=>"yes"));
            }                
        }
    }
    else
    {
        $this->db->insert("wi_trn_org",array('trn_id'=>$trn_id,'grp_id'=>$value));
        $grp_indv=$this->db->pdoQuery("SELECT ind_id 
                                       FROM wi_indv_org 
                                       WHERE grp_id='$value' 
                                       AND is_deleted=0")->results();
                foreach ($grp_indv as $ke => $va) 
                {
                    $ind_id=$va['ind_id'];
                    $rows=$this->db->pdoQuery("SELECT * 
                                               FROM wi_indv_training 
                                               WHERE trn_id='$trn_id' 
                                               AND ind_id='$ind_id'")->results();
                    if(count($rows)>0)
                    {
                        $this->db->pdoQuery("UPDATE wi_indv_training 
                                             SET is_deleted=0,
                                                 indv_source='Group' 
                                             WHERE trn_id='$trn_id' 
                                               AND ind_id='$ind_id'");
                    }
                    else
                    {
                        $this->db->insert("wi_indv_training",
                                          array("ind_id"=>$ind_id,
                                                "indv_source"=>'Group',  
                                                "trn_id"=>$trn_id,
                                                "attendance"=>"yes",
                                                "active"=>"yes"));
                    }                
                }
            }
        }

Can anyone help regarding the best algorithm for performance enhancement?

Answer 1

I researched and found a solution to my query i.e.

        $this->db->pdoQuery("UPDATE wi_trn_org SET is_deleted=1 WHERE trn_id='$trn_id'");
        $this->db->pdoQuery("UPDATE wi_indv_training SET is_deleted=1 WHERE trn_id='$trn_id'");
        $this->db->pdoQuery("INSERT INTO wi_trn_org( trn_id, grp_id ) SELECT $trn_id AS trn_id, grp_id FROM wi_group WHERE grp_id IN ($grp) AND grp_id NOT IN (SELECT DISTINCT (grp_id) FROM wi_trn_org)");
        $this->db->pdoQuery("UPDATE wi_trn_org SET is_deleted=0 WHERE trn_id='$trn_id' AND grp_id IN ($grp)");
        $this->db->pdoQuery("INSERT INTO wi_indv_training( ind_id, trn_id ) SELECT ind_id, $trn_id AS trn_id FROM wi_indv_org WHERE grp_id IN ($grp) AND ind_id NOT IN ( SELECT DISTINCT (ind_id) FROM wi_indv_training WHERE trn_id =$trn_id)");
        $this->db->pdoQuery("UPDATE wi_indv_training SET is_deleted=0,indv_source='Group',attendance='yes',active='yes' WHERE trn_id='$trn_id' AND ind_id IN (SELECT DISTINCT(ind_id) FROM wi_indv_org WHERE grp_id IN ($grp))");

This provided me the solution with the performance enhancement in some extent. Does this algorithm really enhances the Query Performance?

Answer 2

The main problem here is you have a multiple queries in a loop:

foreach ($grp_id as $key => $value) {
            # code...
            $rs=$this->db->pdoQuery("SELECT * FROM wi_trn_org WHERE trn_id='$trn_id' AND grp_id='$value'")->results();            
...
}

There is a performance hit for every SQL query, so if your loop ran 1,000 times, you've hit that performance bottleneck 1,001 times.

The way to solve this is to restructure your query inside the loop as part of the query outside the loop. The first query could be rewritten as a JOIN with the original query, and that may give you enough performance to solve your problem. If that isn't enough, you could portnetialy collapse all of this into a Stored Routine.