when we load data from mysql with default indexes like
KEYhistory_uint_1(itemid,clock)
Performance was very poor, for example from table history_uint (300GB table-size) select for one month execute 3 days and i am interrupt them.
So its important to create indexes for clock column:
create index clock_idx on history_uint(clock);
create index clock_idx on history(clock);
create index clock_idx on trends(clock);
create index clock_idx on trends_uint(clock);
For trends* table we can use pt-online-schema-change for online index create without lock, but for history* table we can only use create index, because primary keys for this table doesnt exist (pt-online-schema-change can alter table when exist primary key)
when we load data from mysql with default indexes like
KEY
history_uint_1(
itemid,
clock)
Performance was very poor, for example from table history_uint (300GB table-size) select for one month execute 3 days and i am interrupt them.So its important to create indexes for clock column: create index clock_idx on history_uint(clock); create index clock_idx on history(clock); create index clock_idx on trends(clock); create index clock_idx on trends_uint(clock);
For trends* table we can use pt-online-schema-change for online index create without lock, but for history* table we can only use create index, because primary keys for this table doesnt exist (pt-online-schema-change can alter table when exist primary key)