From: Ananda Kumar Date: May 14 2012 2:06pm Subject: Re: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this List-Archive: http://lists.mysql.com/mysql/227424 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=20cf3040e36ce4d68304bfff987b --20cf3040e36ce4d68304bfff987b Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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=E9s Tello wrot= e: > > > 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 wrote: > >> is accountid a number or varchar column >> >> >> On Sat, May 12, 2012 at 7:38 PM, Andr=E9s Tello wr= ote: >> >>> While doning a batch process... >>> >>> show full processlist show: >>> >>> | 544 | prod | 90.0.0.51:51262 | tmz2012 | Query | 6 | >>> end | update `account` set `balance`=3D 0.00 + >>> '-4000' where accountid=3D'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`=3D 0.00 + '-4000' where accountid=3D'258= 3092' >>> 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... >>> >> >> > --20cf3040e36ce4d68304bfff987b--