sql - MySQL update join from select very slow -

sql - MySQL update join from select very slow -

we have stored procedure used prepare info report. note schema isn't normalized should be, , cannot modify it, hence building temporary table report. mysql version 5.1.70.

there update statement updated temp table bring together select. note columns in query should have index do, including temp table (oi):

update `tmporderinquiry` oi inner bring together ( select `salesorderno`, group_concat(inv.`invoiceno` order `invoiceno` asc separator '~|~') `invoicenogrp`, group_concat(date_format(date(`shipdate`), ' %c/%d/%y') order `shipdate` asc separator '~|~') `shipdategrp`, group_concat(date_format(date(`lastpmtdate`), ' %c/%d/%y') order `lastpmtdate` asc separator '~|~') `lastpmtdategrp` `insynchinvoicehistoryheader` inv grouping inv.`salesorderno` ) ordinv on oi.`salesorderno` = ordinv.`salesorderno` set oi.`invoiceno` = ordinv.`invoicenogrp`, oi.`shipdate` = ordinv.`shipdategrp`, oi.`lastpmtdate` = ordinv.`lastpmtdategrp`

this query takes approximately 70 seconds on average complete. select on own executes sub-second. after much head banging, on lark replaced above with:

create temporary table `tempworking` select `salesorderno`, group_concat(inv.`invoiceno` order `invoiceno` asc separator '~|~') `invoicenogrp`, group_concat(date_format(date(`shipdate`), ' %c/%d/%y') order `shipdate` asc separator '~|~') `shipdategrp`, group_concat(date_format(date(`lastpmtdate`), ' %c/%d/%y') order `lastpmtdate` asc separator '~|~') `lastpmtdategrp` `insynchinvoicehistoryheader` inv grouping inv.`salesorderno`; update `tmporderinquiry` oi inner bring together tempworking ordinv on oi.`salesorderno` = ordinv.`salesorderno` set oi.`invoiceno` = ordinv.`invoicenogrp`, oi.`shipdate` = ordinv.`shipdategrp`, oi.`lastpmtdate` = ordinv.`lastpmtdategrp`

this query runs in 2 seconds. since select fast, @ loss explain why first query slow. because problem acute released alter production don't introducing prepare things beingness equal expect create things slower.

any insight why first update statement slow appreciated.

mysql sql sql-update inner-join database-performance