If numeric, then why are u using quotes. With quotes, mysql will ignore the index and do a full table scan
On Mon, May 14, 2012 at 7:31 PM, Andrés Tello <mr.crip...@gmail.com> wrote: > > > Yes, I'm using indexes, accountid is the primary key, and is numeric and > autoincrement. The process doing the deadlock is no longer done... > > The structure of the inserted database has changed. > Originaly it was a single table with 219millions rows, now I partitioned > the hable in... 60 tables, 1 for each month for 5 years.. > > I gain a lot of speed with the partitioning and sql adjustments, but I > think I lost a lot of speed at the insert... > > The database stats are like 95% reading 5% writting...but that 5% is > mostly done in a batch process who happends to be done inside a time > window... > > I know I need to do more profiling, but... at least for now dev team is > updating the batch process from long secuencial process with huge slow > inserts, to small parallel task with burst of inserts... > > > > > On Mon, May 14, 2012 at 8:18 AM, Ananda Kumar <anan...@gmail.com> wrote: > >> is accountid a number or varchar column >> >> >> On Sat, May 12, 2012 at 7:38 PM, Andrés Tello <mr.crip...@gmail.com>wrote: >> >>> While doning a batch process... >>> >>> show full processlist show: >>> >>> | 544 | prod | 90.0.0.51:51262 | tmz2012 | Query | 6 | >>> end | update `account` set `balance`= 0.00 + >>> '-4000' where accountid='2583092' >>> >>> No other process, lo locking no nothing... >>> >>> so you take this same query... run it isolated, and the mu....fu... is >>> just...f.... fast!!!!! >>> >>> >>> update `account` set `balance`= 0.00 + '-4000' where accountid='2583092' >>> Query OK, 0 rows affected (0.00 sec) >>> Rows matched: 1 Changed: 0 Warnings: 0 >>> >>> >>> ARRRG! I have seen this type of query take as long as 100+ seconds.. >>> and I >>> don't have a F*** clue... >>> >> >> >
