On Sun, Sep 6, 2009 at 6:01 PM, mos <mo...@fastmail.fm> 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
