php - Mysql: Delete duplicate records based on minimum amount -

-

i need delete duplicate records based on minimum amount same order number. table has 100k records , structure this. tmp_id primary key in table.

------------------------------------ user_id   order_number amount  tmp_id ------------------------------------- 15         12364        25      1 20         454544       75      2 4          12364        100     3 6          45487        45      4 8          454544       330     5

i tried delete minimum amount of duplicate records using 

$qb_duplicate_data_query="select user_id,order_number,amount tmp_qbappraiser limit 10";  $qb_duplicate_data_sql=mysql_query($qb_duplicate_data_query) or die(mysql_error());  while($row=mysql_fetch_array($qb_duplicate_data_sql)) {      $amount=$row['amount'];      $order_no=$row['order_number'];       $sql="select c1.tmp_id tmp_qbappraiser c1 inner join (select tmp_id tmp_qbappraiser order_number='$order_no' order amount asc) c  on c.tmp_id=c1.tmp_id group c1.order_number having count(order_number)>1";       $query=mysql_query($sql) or die(mysql_error());       $result=mysql_fetch_array($query);       $db_tmp_id=$result['tmp_id'];       if($db_tmp_id!='') {          $delete_sql="delete tmp_qbappraiser order_number='$order_no' , tmp_id='$db_tmp_id'";           $delete_query=mysql_query($delete_sql) or die(mysql_error());      }  }

when execute code lock wait timeout exceeded try restarting transaction error. how optimize query or there other way delete thousands of records?

you not need write multiple queries deleting duplicate entry, done 1 single query.

delete t1 tmp_qbappraiser t1 join tmp_qbappraiser t2 on t1.order_number = t2.order_number , t1.amount < t2.amount ;

the above query keep max amount per order_number , delete rest.

you may need add indexes in table making faster 

alter table tmp_qbappraiser add index ord_amt_idx(order_number,amount);

note: make sure take backup of table before applying index , running delete query.


Comments

Post a Comment

Popular posts from this blog

python - No exponential form of the z-axis in matplotlib-3D-plots -

-
Image
i have similar problem described in how prevent numbers being changed exponential form in python matplotlib figure : i don't want (in special case) weird scientific formatting of axis. problem different have problem @ z -axis. 2-d plots can use ax.get_yaxis().get_major_formatter().set_useoffset(false) . , there no function ax.get_zaxis() what use format z -axis same way? edit: example: from mpl_toolkits.mplot3d import axes3d import numpy np import sys import matplotlib import matplotlib.pyplot pyplot def func(xi, ti): res = 10e3 + np.cos(ti) * np.sin(xi) return res if __name__ == '__main__': timespacing = 20 timestart = 0 timeend = 1 time = np.linspace(timestart, timeend, timespacing) widthspacing = 50 widthstart = 0 widthend = 3 width = np.linspace(widthstart, widthend, widthspacing) reslist = [] matplotlib.rcparams['legend.fontsize'] = 10 fig = pyplot.figure() ax = fig.gca(projection = '3d...
Read more

php - Best Light server (Linux + Web server + Database) for Raspberry Pi -

-
i install web server database on raspberry pi (little computer). computer has 1gb ram. i want know best combination: linux distribution , web server , dbms run local server multiple users minimal latency, use php on server. , best settings performance , not have bugs (memory usage, disable plugin, disable service, etc)? i thought light debian , lighttpd server , sqlite database. solution? i think lighttpd + sqlite great choice. linux distro, debian @ centos or tiny core linux , although i'm not sure of compatibility pi. obviously, can't go wrong raspbian if want use in production , more stable performance, few more pi's , set them in cluster .
Read more

c# - "Newtonsoft.Json.JsonSerializationException unable to find constructor to use for types" error when deserializing class -

-
i developing app in xamarin android. have splash screen serialize class , pass mainactivity using intent . when try deserialize in mainactivity error message : "serializationexception unable find constructor use types" serialization : void loaddata() { currency currency = new currency(this); intent = new intent(this, typeof(mainactivity)); intent.putextra("currency", newtonsoft.json.jsonconvert.serializeobject(currency)); startactivity(intent); } deserialization : cur = newtonsoft.json.jsonconvert.deserializeobject<currency> (intent.getstringextra("currency")); // error here class constructor : public currency(context context) { thiscontext = context; } i began experiencing problem after added parameter context context constructor. has caused this? how can solve it? possible serialize/deserialize class has parameters? you need have empty constructor in currency class in order deserialize it. ...
Read more