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

Question

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'");     
                  $functions->set_query("UPDATE `top-2m` SET `status`=2 WHERE sitename ='$site_name'");
              }
          }
      }else{
          mail("aaa@aaa.com","Alexa_Cron_Update_Status","aaaRank Is Succes fully Updated");
      }
 }

Duplicate http://stackoverflow.com/questions/6286452/mysql-bulk-insert-or-update — Karthikeyan, Feb 08 '16 at 06:49
You should really try to explain what you are trying to do. How do your tables look like? What are the relations? — maxhb, Feb 08 '16 at 06:53
IMO you should also post the `functions` class. If some function like `get_row` opens a new connection each time, this will slow down your program. — BackSlash, Feb 08 '16 at 07:04
@karthikeyen, its not about multiple insert. i am asking that, how to do it fastly and efficiently. — Priya Rajaram, Feb 08 '16 at 07:05
@maxhb,I tried to update the alexa rank for websites when i run the alexa api it will return 1 million data so i will update the data if alredy exist in my database ,if new data is coming i have to insert in my databse, my tables are look like a1_sites,a2_sites,a3_sites.....z3_sites — Priya Rajaram, Feb 08 '16 at 07:08
can you fetch million data into chunks. like in chunks of hundreds or thousands, that would be better to update or insert i think. — Jigar, Feb 08 '16 at 07:17
First do re-index your database properly if its taking time to execute also use prepared statements. http://dev.mysql.com/doc/refman/5.6/en/sql-syntax-prepared-statements.html — Rahul, Feb 08 '16 at 07:27

score 0 · Answer 1 · answered Feb 08 '16 at 07:36

0

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.

answered Feb 08 '16 at 07:36

Rahul

763
1
12
45

Murad Hasan · Answer 2 · 2016-02-08T12:20:35.477

0

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);

edited Feb 08 '16 at 12:20

answered Feb 08 '16 at 07:40

Murad Hasan

9,565
2
21
42

@ Frayne, i tried as you mentioned but its, updated last row only – Priya Rajaram Feb 08 '16 at 11:34
did you concatenate correctly?? I mean every query must have to concatenate with a semicolon. see the update in answer which is blocked as Edited. – Murad Hasan Feb 08 '16 at 12:18

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

2 Answers2