MySQL Query optimization needed

[renierdbruyn]

the following function "ranks" the applicants and writes them to the rank_results table in the database:

function rank() {
        $rank = 0;
        $sql1 = "SELECT * FROM applicant_details";
        $results = $this->db->query($sql1)->result();
        $sql3 = "SELECT * FROM job_advert";
        $job_advert = $this->db->query($sql3)->result();

        foreach ($results as $applicant) {
            $applicant_age = $applicant->age;
            $applicant_id_number = $applicant->id_number;
            $applicant_job_experience = $applicant->job_experience;
            $applicant_industry_type = $applicant->industry_type;
            $applicant_qualification_type = $applicant->qualification_type;
            $applicant_city = $applicant->city;
            $applicant_relocate = $applicant->relocate;
            $applicant_first_name = $applicant->firstname;
            $applicant_phone = $applicant->upro_phone;
            $applicant_min_salary = $applicant->minimum_salary;
            $applicant_prefered_salary = $applicant->preferred_salary;
            $applicant_licence = $applicant->license;
            $applicant_employment_equity = $applicant->employment_equity;
            $applicant_email = $applicant->uacc_email;
            $applicants = array();

            foreach ($job_advert as $advert) {
                $advert_id = $advert->advert_id;
                $job_title = $advert->job_title;
                $salary_offered = $advert->salary_offered;
                $is_negotiable = $advert->negotiable;
                $company_location = $advert->company_location;
                $Job_experience = $advert->required_experience;
                $age = $advert->age_group;
                $gender = $advert->gender;
                $job_industry = $advert->industry_type;
                $emplyment_equity = $advert->employment_equity;
                $qualification_type = $advert->qualification_type;
                $qualification_name = $advert->qualification_name;
                $drivers_licence = $advert->drivers_licence;
                $status = $advert->status;
                $agegroup = explode("-", $age);

                if ($age === "any" || $applicant_age >= $agegroup[0] || $applicant_age <= $agegroup[1]) {
                    $applicants[$applicant_id_number] = $rank;
                } elseif ($applicant_age < $age) {
                    $applicants[$applicant_id_number] = $rank + 5;
                }
                if ($applicant_industry_type == $job_industry) {
                    $applicants[$applicant_id_number]+=0;
                    if ($applicant_job_experience >= $Job_experience) {
                        $applicants[$applicant_id_number]+=0;
                    } else {
                        $applicants[$applicant_id_number]+=10;
                    }
                } else {
                    $applicants[$applicant_id_number]+=5;
                }
                if ($applicant_qualification_type == $qualification_type) {
                    $applicants[$applicant_id_number]+=0;
                } else {
                    $applicants[$applicant_id_number]+=25;
                }

                if ($applicant_licence == $drivers_licence || $applicant_licence != NULL && $drivers_licence != NULL || $applicant_licence == NULL && $drivers_licence == NULL) {
                    $applicants[$applicant_id_number]+=0;

                    if ($applicant_licence != NULL && $drivers_licence = NULL) {
                        $applicants[$applicant_id_number]+=0;
                    }
                } else {
                    $applicants[$applicant_id_number]+=5;
                }
                if($applicant_industry_type == $job_industry){
                    $applicants[$applicant_id_number]+=0;
                }else {
                    $applicants[$applicant_id_number]+=30;
                }
                asort($applicants);
                if ($applicants[$applicant_id_number] <= 30) {
                    $sql = "INSERT INTO rank_results (applicant_id,job_id,rank)
                    VALUES('{$applicant_id_number}','{$advert_id}','{$applicants[$applicant_id_number]}')
                        ON DUPLICATE KEY UPDATE 
                        applicant_id = '{$applicant_id_number}',
                       job_id = '{$advert_id}',
                       rank = '{$applicants[$applicant_id_number]}'";
                    $this->db->query($sql);
                    mysql_error();
                }

                $sql = "DELETE FROM rank_results 
                            WHERE job_id in (SELECT advert_id FROM job_advert WHERE status =0)";
                $this->db->query($sql);
                mysql_error();

            }
        }
    }

My Queastion:
When There are many applicants to rank this function takes a bit long, can I optimize this query if so How would I do so?

    $sql = "INSERT INTO rank_results (applicant_id,job_id,rank)
                    VALUES('{$applicant_id_number}','{$advert_id}','{$applicants[$applicant_id_number]}')
                        ON DUPLICATE KEY UPDATE 
                        applicant_id = '{$applicant_id_number}',
                       job_id = '{$advert_id}',
                       rank = '{$applicants[$applicant_id_number]}'";
                    $this->db->query($sql);

[pritaeas]

Is applicant_id your key? If so, try removing it from the UPDATE part. If job_id doesn't change either, you can remove that from the UPDATE too.

[renierdbruyn]

Thanks, it is much faster :), can I optimize it even more?

[pritaeas]

You can try to index on applicant_id and job_id, but I doubt it will make a difference.

[renierdbruyn]

I indexed it... will see if its faster as the db grows. thank you