No, Use a cursor(select <column_name> to be used in where condition of update stmt), loop through it for each update.
regards anandkl On Thu, Sep 22, 2011 at 11:36 PM, Hank <hes...@gmail.com> wrote: > > Actually, that would be orders of magnitude slower. > > I'm using MYISAM tables, so there's no commit. > > > > > On Thu, Sep 22, 2011 at 1:48 PM, Ananda Kumar <anan...@gmail.com> wrote: > >> Hi, >> Why dont u use a stored proc to update rows ,where u commit for every 1k >> or 10k rows. >> This will be much faster than ur individual update stmt. >> >> regards >> anandkl >> >> On Thu, Sep 22, 2011 at 8:24 PM, Hank <hes...@gmail.com> wrote: >> >>> That is what I'm doing. I'm doing a correlated update on 200 million >>> records. One UPDATE statement. >>> >>> Also, I'm not asking for a tutorial when not to use LOCK TABLES. I'm >>> trying >>> to figure out why, despite what the documentation says, using LOCK TABLES >>> hinders performance for large update statements on MYISAM tables when it >>> is >>> supposed to increase performance on exactly the type of queries I am >>> performing. >>> >>> If you can't help answer *that* question, please stop lecturing me on the >>> reasons not to use LOCK TABLES. Thanks. >>> >>> -Hank >>> >>> >>> On Thu, Sep 22, 2011 at 10:19 AM, Antony T Curtis >>> <antonycur...@verizon.net>wrote: >>> >>> > Even for MyISAM tables, LOCK TABLES is not usually the best solution >>> for >>> > increasing performance. When there is little to no contention, LOCK >>> TABLES >>> > doesn't offer much value. >>> > >>> > MyISAM works best when you can get more work done in a statement: >>> Instead >>> > of executing a bunch of insert statements, combine them into a single >>> > multi-row insert statement, as an example. >>> > >>> > >>> > On 22 Sep 2011, at 06:13, Hank wrote: >>> > >>> > Thanks for your reply. I failed to mention that these are MYISAM >>> tables, >>> > so no transactions. And like I said, this is not a production box nor >>> is >>> > there any application running, so there's no contention for the tables >>> being >>> > locked. I'm trying to update a database design on two tables with 200 >>> > million records each, so anything I can do to increase the performance >>> of >>> > these long running queries will shorten the migration running time. >>> > >>> > What I was referring to was that in the documentation, that when using >>> > LOCK TABLES, mysql does not update the key cache until the lock is >>> released, >>> > versus when not using LOCK TABLES it does update the key cache on each >>> > insert/update/delete. >>> > >>> > see: http://tuxradar.com/practicalphp/18/2/22 >>> > >>> > In my testing, I'm seeing a slow down when I use LOCK TABLES versus >>> running >>> > the same queries without it. I'm just trying to find a reason why that >>> > might be the case. >>> > >>> > -Hank >>> > >>> > >>> > On Thu, Sep 22, 2011 at 12:42 AM, Antony T Curtis < >>> > antonycur...@verizon.net> wrote: >>> > >>> >> LOCK TABLES...WRITE is very likely to reduce performance if you are >>> using >>> >> a transactional storage engine, such as InnoDB/XtraDB or PBXT. The >>> reason is >>> >> that only one connection is holding the write lock and no other >>> concurrent >>> >> operation may occur on the table. >>> >> >>> >> LOCK TABLES is only really useful for non-transactional tables and >>> maybe a >>> >> few specialized operations where it has its advantages but for 99.9% >>> of >>> >> cases, it should not be used. >>> >> >>> >> What does increase performance is the proper use of transactions with >>> >> appropriate use of SELECT...FOR UPDATE and SELECT...LOCK IN SHARE >>> MODE. >>> >> >>> >> Regards, >>> >> >>> >> Antony. >>> >> >>> >> >>> >> >>> >> On 21 Sep 2011, at 20:34, Hank wrote: >>> >> >>> >> According to everything I've read, using LOCK TABLES...WRITE for >>> updates, >>> >>> inserts and deletes should improve performance of mysql server, but I >>> >>> think >>> >>> I've been seeing the opposite effect. >>> >>> >>> >>> I've been doing quite a bit of testing on a 64bit install of CentOS >>> 5.5 >>> >>> installed as a guest OS on a VMWare ESXi 4.0 hypervisor on a Dell >>> R610. >>> >>> There are no other VMs on this box, and there are no other users or >>> >>> threads >>> >>> running on the OS. Just me. I'm using this box strictly for testing >>> of >>> >>> large database migration scripts. >>> >>> >>> >>> It seems like when I execute some of these long running statements >>> >>> without >>> >>> locking the tables, the code runs quite a bit faster than when I do >>> lock >>> >>> the >>> >>> tables. And before testing each run, I do restart the server so >>> there is >>> >>> no >>> >>> query caching and I also use FLUSH TABLES between each test run. >>> >>> >>> >>> All I'm asking is this: Can anything think of a scenario on a single >>> >>> user-box and mysql instance, that locking tables would cause these >>> DML >>> >>> statements to slow down compared to not locking the tables? >>> >>> >>> >>> Thanks, >>> >>> >>> >>> -Hank >>> >>> >>> >> >>> >> >>> > >>> > >>> >> >> >
