Re: Large table designissue

[omkar prabhu]

Thanks again Rick for your response.

I will try to alter BIGINT.For auto increment id column should i convert to INT,as there are 100k records added daily to this table.

I will try to use compount indexes. Say there are 3 columns index (col1,col2,col3). If i use query like select * from table where col3 = x then i noticed it is not using index, but if i use something like select * from table where col1 = 1 and col3 = 2 then it uses index.

Regarding alter this BIG table , i thinking of following approach.

Have master/salve replication setup. Add/drop required indexes on slave.
once indexes are created and replication is sync. promote salve as master
Existing master will become slave. start replication.
repeated above steps on new slave

What i mean by mass updated.
update table set col1=3 where category='active'

individual update table

select id from table where category='active'
for each loop
update table set col1=3 where id='active'
end loop