List:General Discussion« Previous MessageNext Message »
From:Hank Date:September 7 2009 4:55am
Subject:Re: Speeding up a pretty simple correlated update query
View as plain text  
On Sun, Sep 6, 2009 at 6:01 PM, mos <mos99@stripped> wrote:

>
>  So I've disabled all the keys on the "item_trans" table -- since I am
>> updating every row, it wouldn't (shouldn't) be using the index anyway.
>>
>
> You can't disable unique indexes or primary keys. They are always active.
> You can only deactivate non-unique indexes.
>
> Here are a couple of suggestions.
>
> For now drop the index on item_seq.seq and desc.seq.
> Are you sure you have a compound index on item_seq.itemid and
> item_seq.category??
>
> do a "Show create table item_seq" and also "Show create table desc" to see
> what you've got.
>
> Make sure your my.cnf file has
> key_buffer_size=500M
>
> equal to about 30% of your available memory. You can always reduce it
> later.
>
> Of course there is another way of doing it, if you are willing to have the
> tail wag the dog. You may kick yourself for not discovering it yourself. :)
>
> set @num:=0;
> set @last:='';
> create table new_item_trans select IF(concat(itemid,category),@last,
> @num:=@num+1,@num) Seq, itemid, category, transid, ...
> ,@last:=concat(itemid,category) as TMPLast from item_trans order by
> concat(itemid,category);
>
> Now you can use the Alter statement to add your indexes and get rid of the
> TMPLast column.
>
> To build the  item_seq table you would now use:
>
> create table item_seq select seq, itemid, category from new_item_trans
> group by seq, itemid, category;
> And of course build your indexes on seq and rename the new_item_trans.
>
> I guarantee you this last solution will not take 9 days to complete! :-)
>
> Mike
>
>
Hi Mike,

 Thanks for your reply.  First, in my tests, I've created the target table
(item_trans) as a copy of the source table with no indexes at all (even no
primary key). Once I get the item_seq field populated, I'll go back and
re-create the indexes in batch using myisamchk (I've posted about this
recently).

 Second, I like your second creative solution (I never would have come up
with that), but in order for it to work, mysql would have to sort 180
million records before creating the table or retrieve them out of the table
via the contactenated index, both of which I think will take a long time...
but I'll certainly give it a shot tomorrow and let you know how it goes.
Thanks again.

-Hank

Thread
Speeding up a pretty simple correlated update queryHank3 Sep
  • RE: Speeding up a pretty simple correlated update queryGavin Towey3 Sep
    • Re: Speeding up a pretty simple correlated update queryHank3 Sep
  • Re: Speeding up a pretty simple correlated update queryHank6 Sep
    • Re: Speeding up a pretty simple correlated update querymos7 Sep
      • Re: Speeding up a pretty simple correlated update queryHank7 Sep