Re: Large table designissue
Posted by: Rick James
Date: November 24, 2009 11:49PM


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.
Well, sort of. Your second example is using only the col1 part of the index. When designing an compound index, the first fields need to be "=" things in the WHERE clause. So...
WHERE col3 = x -- needs a simple or compound index _beginning_ with col3 (not col1)
WHERE col1 = 1 AND col3 = 2 -- needs a compound index of either (col1, col3, ...) or (col3, col1, ...); the col2 in the middle makes it much less efficient.

BIGINT vs INT -- If you even might get billions of items, use BIGINT. If you will never get that many, use INT. 100K rows per day = 37M rows per year = overflowing INT UNSIGNED in a little over 100 years.


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
The former will be MUCH faster. But it will clog up replication. For big tables, I recommend a compromise -- do a loop, but change 1000 rows per iteration.

Do not do this:
update tabel set col1 =1 where category='Active' limit 10
You cannot predict which 10 will be updated. Better to use id ranges.

Making the change on a slave, then failing over...
Read about SQL_LOG_BIN=0.
And be sure to have the binlog otherwise turned on, so that when you failover, the newly promoted machine will be pushing data to the newly demoted machine.

Most datatypes are happy to be in an index, whether simple or compound. Mixing them is fine.

Options: ReplyQuote

Written By
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
November 19, 2009 12:55PM
Re: Large table designissue
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.