- Original Message -
> From: "Hank"
>
> Just an update. Using the "load index into cache" statement for the
> 200 million row indexed "source" table, my correlated update
> statement ran in 1 hour, 45 minutes to update 144 million rows. A 50%
> increase in performance!
Good to hear :-
2011/09/23 12:56 +0200, Johan De Meersman
What it says, is "If you are going to run many operations". You are updating
many rows, but you are only doing ONE operation: a single update statement.
For what it's worth, the downloading HTML help claims this only for MyISAM
tables,
Hello Johan,
Just an update. Using the "load index into cache" statement for the
200 million row indexed "source" table, my correlated update statement
ran in 1 hour, 45 minutes to update 144 million rows. A 50% increase
in performance!
Thank you very much,
-Hank
On Fri, Sep 23, 2011 at 6:
Hello Johan,
 Thanks for your comprehensive reply. I'll try to answer each of your
questions below.
-Hank
> > But if seeing some SQL will make you happy, here is just one example:
> >
> > UPDATE dest d straight_join source s set d.seq=s.seq WHERE d.key=s.key;
>
> See, this is why people ask to se
- Original Message -
> From: "Hank"
>
> (please read my ORIGINAL post with all this information).
Welcome on the Internet, where everyone will tell you everything you need to
know, except for what you want to know :-)
> I am trying to find a logical or reasonable explanation WHY this
On Thu, Sep 22, 2011 at 3:11 PM, Hassan Schroeder <
hassan.schroe...@gmail.com> wrote:
> On Thu, Sep 22, 2011 at 11:51 AM, Hank wrote:
> > Like I said, the problem is not just one particular SQL statement. It is
> > several dozen statements operating on tables with several hundred million
> > rec
do u have index on dest,key
On Fri, Sep 23, 2011 at 12:21 AM, Hank wrote:
>
> Like I said, the problem is not just one particular SQL statement. It is
> several dozen statements operating on tables with several hundred million
> records. The problem is that I am finding that when I use LOCK TAB
Like I said, the problem is not just one particular SQL statement. It is
several dozen statements operating on tables with several hundred million
records. The problem is that I am finding that when I use LOCK TABLES,
these queries run slower (please read my ORIGINAL post with all this
information
May be if u can let the audience know a sip-net of ur sql, some can help u
On Thu, Sep 22, 2011 at 11:43 PM, Hank wrote:
>
> Sorry, but you do not understand my original issue or question.
>
> -Hank
>
>
>
> On Thu, Sep 22, 2011 at 2:10 PM, Ananda Kumar wrote:
>
>> No,
>> Use a cursor(select to
Sorry, but you do not understand my original issue or question.
-Hank
On Thu, Sep 22, 2011 at 2:10 PM, Ananda Kumar wrote:
> No,
> Use a cursor(select 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
No,
Use a cursor(select 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 wrote:
>
> Actually, that would be orders of magnitude slower.
>
> I'm using MYISAM tables, so there's no commit.
>
>
>
>
> On Thu, S
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 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 individ
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 wrote:
> That is what I'm doing. I'm doing a correlated update on 200 million
> records. One U
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
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,
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
mil
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 usef
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 ES
18 matches
Mail list logo
