Priya Rajaram February 2016

How to Update 1,000,000 data in my sql database in less time?

I need to update and insert around 1 Million data in mysql data base, when I am using the following code It takes more time. please suggest how can i update and insert the data fastly?

include('db.php');
include('functions.php');
$functions=new functions();
 set_time_limit(0);
 $column="rank"."_".date("Y-m-d");
 $count=$functions->get_row("SELECT COUNT(id) as ct FROM alexa_filename WHERE status=1");
 if($count->ct==100){
      $alexas=$functions->get_result("SELECT DISTINCT (`sitename`),`$column` FROM `top-2m` WHERE  `status`=0 LIMIT 100" );
      if(!empty($alexas)){
          foreach($alexas as $alexa){
              $site_name=$alexa->sitename;
              echo $site_name;
              $rank=$alexa->$column;
              $table=$functions->find_table_name($site_name);
              $count=$functions->get_row("SELECT COUNT(site_name) as ct FROM `$table` WHERE site_name='$site_name'");
              if($count->ct==0){
                    $functions->set_query("INSERT INTO `$table`( `site_name`, `other_id`, `response`, `category`, `updated`, `site_update`, `wot_update`, `social_update`,                                                                    `google_update`, `server_update`, `alexa_update`, `backlinks_update`, `antivirus_update`, `key`, `desc`, `google_backlink`, `images_url`,                                                                                                                         `images`, `tag`, `view_count`, `title`, `api_update_time`, `table_name`, `user_added_similar`, `auto_similar`, `comments`, `status`) VALUES                                                                    ('$site_name',0,0,0,0,0,0,0,0,0,$rank,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)");
                $functions->set_query("UPDATE `top-2m` SET `status`=1 WHERE sitename ='$site_name'");
              }else{
                 $functions->set_query("UPDATE  `$table` SET `alexa_update`=$rank WHERE site_name='$site_name'"        

Answers


Rahul Dambare February 2016

  • You can insert/update multiple rows using INSERT ... ON DUPLICATE KEY UPDATE.
  • Reindex your database.
  • Use Prepared Mysql Statements.
  • Also If you are using Linux/ubuntu try to use terminal instead of browser. It will make a lot difference.


Frayne Konok February 2016

Concatenate your INSERT and UPDATE Query to $qry and apply

$functions->set_query($qry);

once your looping done. This will take less time.

Edited:

Example:

$qry = "Insert into table values('', '', '','', '')"; 
$qry .= "insert into table2 values('', '', '','', '')"; 
$qry .= "insert into table3 values('', '', '','', '')"; 
$qry .= "update table3 set field = 'something' "; 

and out of condition or loop.

$functions->set_query($qry);

Post Status

Asked in February 2016
Viewed 3,836 times
Voted 5
Answered 2 times

Search




Leave an answer