Re: Large table designissue
Posted by: omkar prabhu
Date: November 19, 2009 12:55PM

Ok I will try removing unwanted BIGINT. we have almost 100k record added in this table on daily basis.
For ID autoincrement column should i still keep as BIGNT?

Thanks for explaining with example on compound indexes.Say i have index(col1,col2,col3)
If i am using only col3 in where clause , notice it is not using compound index.
What if i have different datatype like datetime, enum, char, is it fine for compound index to have column of various data types?
I will see if i can use compound index here.

For alter to avoid downtime , i am thinking of following option.

Add/drop index on slave. wait till both master/slave are in sync.
Promote slave as master and original master as slave.start replication.
Add/drop index on new slave

For mass update i mean
update tabel set col1 =1 where category='Active'
When i do this and if this column col1 have index, will it lock complete table or all selected rows?
Will it better to do like
select * from table where category='Active'
for each record
update tabel set col1 =1 where id = $i
end record
or something like
for loop
update tabel set col1 =1 where category='Active' limit 10
end loop

Options: ReplyQuote


Subject
Written By
Posted
October 24, 2009 02:46AM
October 25, 2009 09:11AM
October 29, 2009 09:36PM
October 29, 2009 10:34PM
November 13, 2009 01:05PM
November 18, 2009 12:15AM
November 19, 2009 12:38PM
Re: Large table designissue
November 19, 2009 12:55PM
November 24, 2009 11:49PM
November 25, 2009 11:23AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.