Home Ask Login Register

Developers Planet

Your answer is one click away!

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?

$functions=new functions();
 $count=$functions->get_row("SELECT COUNT(id) as ct FROM alexa_filename WHERE status=1");
      $alexas=$functions->get_result("SELECT DISTINCT (`sitename`),`$column` FROM `top-2m` WHERE  `status`=0 LIMIT 100" );
          foreach($alexas as $alexa){
              echo $site_name;
              $count=$functions->get_row("SELECT COUNT(site_name) as ct FROM `$table` WHERE site_name='$site_name'");
                    $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'");
                 $functions->set_query("UPDATE  `$table` SET `alexa_update`=$rank WHERE site_name='$site_name'"        


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


once your looping done. This will take less time.



$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.


Post Status

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


Leave an answer

Quote of the day: live life