要插入MySQL数据库的每一行所用的时间

Question

Hi I have data to upload to a Mysql database and I need to get the time taken for each row to be inserted so that I can pass that number through to a progress bar. I have alreay tried accomplishing this by determining the number of rows affetced by the insertion then find the percentage of that number which is not the correct manner to do this.

here is the code

     $result3=mysql_query("INSERT INTO dest_table.create_info SELECT * from Profusion.source_cdr") or      die(mysql_error());
     $progress=mysql_affected_rows();
     // Total processes
     $total = $progress;
     // Loop through process
     for($i=1; $i<=$total; $i++){
     // Calculate the percentage
     $percent = intval($i/$total * 100)."%";
     echo $percent;

this actually divides the total number of rows by 1 and multiplies by 100 to get the percentage and this is wrong .

I need the time taken for each row to be inserted and then find the percentage of that.

Your help will be highly appreciated.

Answer 1

It is not easy to determine the time of one insertion in this case; because normal insertion INSERT INTO my_table VALUES(2,4,5)is different from INSERT INTO my_Table SELECT foo,bar,zoo FROM my_other_table.

In your case, the mysql server will try to keep both tables in memory, which need a lot of memory if tables are big. Check this blog post: Even faster: loading half a billion rows in MySQL revisited for some details.

Anyway, in the above script, after returning from mysql_query; the query is ALREADY EXECUTED which means you percentage will count after the query is executed.

EDIT: A solution it to chunk the entries in the table into chunks. Pseudo code of the solution shall be like this:

 mysql_query("SELECT count(*) FROM my_other_table");
 $total_count = get_count_from_query_result();
 $one_percent = intval($total_count/100);
 for($i=0; $i<$total_count; $i += $one_percent)
 {
   mysql_query("INSERT INTO my_Table SELECT foo,bar,zoo FROM my_other_table LIMIT $i, $one_percent");
   increment_progress_bar();
 }