Re: Index with partitioning
Hi Rick, thanks for your reply.
The insert performance is not a problem. Sometimes takes 1.7 MM records on a single partition and takes 20 minutes to do so.
Should I try inserting with indexes on?
Here is the create table:
CREATE TABLE `vc_fat_processo_de_venda` (
`ID_PROCESSO_DE_VENDA` int(11) NOT NULL AUTO_INCREMENT,
`ID_EQUIPAMENTO` int(11) DEFAULT NULL,
`ID_ESTADO_PEDIDO` int(11) DEFAULT NULL,
`ID_DDD_UF` int(11) NOT NULL DEFAULT '0',
`ID_TIPO_SOLICITACAO` int(11) DEFAULT NULL,
`ID_OPERADORA_PORTIN` int(11) DEFAULT NULL,
`ID_FORMA_PGTO` int(11) DEFAULT NULL,
`ID_USUARIO_VENDA` int(11) DEFAULT NULL,
`ID_DATA_INPUT` int(11) DEFAULT NULL,
`ID_DATA_ULT_AT` int(11) DEFAULT NULL,
`ID_DATA_AT_ITEM` int(11) DEFAULT NULL,
`ID_DIA_FOTO` int(11) NOT NULL DEFAULT '0',
`ID_DATA_PORTIN` int(11) DEFAULT NULL,
`ID_VENDEDOR` int(11) DEFAULT NULL,
`ID_ADABAS` int(11) DEFAULT NULL,
`ID_CARTEIRA` int(11) DEFAULT NULL,
`ID_MAIL_BL_PORT` int(11) DEFAULT NULL,
`ID_PROD_SERV` int(11) DEFAULT NULL,
`ID_STATUS_PORTIN` int(11) DEFAULT NULL,
`ID_CLIENTE` int(11) DEFAULT NULL,
`ID_ALCADA_SMP` int(11) DEFAULT NULL,
`ID_DATA_CRIACAO` int(11) DEFAULT NULL,
`NUMERO_PEDIDO` varchar(20) DEFAULT NULL,
`REVISAO` int(11) DEFAULT NULL,
`NUMERO_LINHA` bigint(20) DEFAULT NULL,
`CONTA_SERVICO` varchar(15) DEFAULT NULL,
`NUMERO_PORTABILIDADE` bigint(20) DEFAULT NULL,
`PROTOCOLO_PORTABILIDADE` int(11) DEFAULT NULL,
`NUMERO_OV` int(11) DEFAULT NULL,
`NOTA_FISCAL` int(11) DEFAULT NULL,
`PRECO` decimal(19,2) DEFAULT NULL,
PRIMARY KEY (`ID_PROCESSO_DE_VENDA`,`ID_DIA_FOTO`,`ID_DDD_UF`),
KEY `idx_codex1` (`ID_PROD_SERV`,`ID_TIPO_SOLICITACAO`,`ID_ESTADO_PEDIDO`,`ID_DDD_UF`,`ID_VENDEDOR`,`ID_DIA_FOTO`,`ID_DATA_INPUT`,`ID_MAIL_BL_PORT`,`ID_DATA_ULT_AT`,`ID_DATA_PORTIN`,`ID_EQUIPAMENTO`,`ID_OPERADORA_PORTIN`,`ID_CLIENTE`,`ID_ADABAS`,`ID_STATUS_PORTIN`,`ID_CARTEIRA`)
) ENGINE=MyISAM AUTO_INCREMENT=28692020 DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(ID_DIA_FOTO,ID_DDD_UF)
(PARTITION p0_20120429_TODAS_REGIONAIS VALUES LESS THAN (120,69) ENGINE = MyISAM,
PARTITION p1_201204_SP_RJ_ES_MG_PR_SC_RS VALUES LESS THAN (121,35) ENGINE = MyISAM,
PARTITION p2_201204_CO VALUES LESS THAN (121,44) ENGINE = MyISAM,
PARTITION p3_201204_BA_SE_NE_N VALUES LESS THAN (121,68) ENGINE = MyISAM,
PARTITION p4_201205_SP VALUES LESS THAN (152,10) ENGINE = MyISAM,
PARTITION p5_201205_RJ_ES VALUES LESS THAN (152,15) ENGINE = MyISAM,
PARTITION p6_201205_MG VALUES LESS THAN (152,22) ENGINE = MyISAM,
PARTITION p7_201205_PR_SC VALUES LESS THAN (152,31) ENGINE = MyISAM,
PARTITION p8_201205_RS VALUES LESS THAN (152,35) ENGINE = MyISAM,
PARTITION p9_201205_CO VALUES LESS THAN (152,44) ENGINE = MyISAM,
PARTITION p10_201205_BA_SE VALUES LESS THAN (152,50) ENGINE = MyISAM,
PARTITION p11_201205_NE VALUES LESS THAN (152,59) ENGINE = MyISAM,
PARTITION p12_201205_N VALUES LESS THAN (152,68) ENGINE = MyISAM,
PARTITION p13_201205_OUTROS VALUES LESS THAN (152,69) ENGINE = MyISAM,
PARTITION p14_201206_SP VALUES LESS THAN (182,10) ENGINE = MyISAM,
PARTITION p15_201206_RJ_ES VALUES LESS THAN (182,15) ENGINE = MyISAM,
PARTITION p16_201206_MG VALUES LESS THAN (182,22) ENGINE = MyISAM,
PARTITION p17_201206_PR_SC VALUES LESS THAN (182,31) ENGINE = MyISAM,
PARTITION p18_201206_RS VALUES LESS THAN (182,35) ENGINE = MyISAM,
PARTITION p19_201206_CO VALUES LESS THAN (182,44) ENGINE = MyISAM,
PARTITION p20_201206_BA_SE VALUES LESS THAN (182,50) ENGINE = MyISAM,
PARTITION p21_201206_NE VALUES LESS THAN (182,59) ENGINE = MyISAM,
PARTITION p22_201206_N VALUES LESS THAN (182,68) ENGINE = MyISAM,
PARTITION p23_201206_OUTROS VALUES LESS THAN (182,69) ENGINE = MyISAM,
PARTITION p24_201206_SP VALUES LESS THAN (213,10) ENGINE = MyISAM,
PARTITION p25_201206_RJ_ES VALUES LESS THAN (213,15) ENGINE = MyISAM,
PARTITION p26_201206_MG VALUES LESS THAN (213,22) ENGINE = MyISAM,
PARTITION p27_201206_PR_SC VALUES LESS THAN (213,31) ENGINE = MyISAM,
PARTITION p28_201206_RS VALUES LESS THAN (213,35) ENGINE = MyISAM,
PARTITION p29_201206_CO VALUES LESS THAN (213,44) ENGINE = MyISAM,
PARTITION p30_201206_BA_SE VALUES LESS THAN (213,50) ENGINE = MyISAM,
PARTITION p31_201206_NE VALUES LESS THAN (213,59) ENGINE = MyISAM,
PARTITION p32_201206_N VALUES LESS THAN (213,68) ENGINE = MyISAM,
PARTITION p33_201206_OUTROS VALUES LESS THAN (213,69) ENGINE = MyISAM,
PARTITION p34_201206_SP VALUES LESS THAN (244,10) ENGINE = MyISAM,
PARTITION p35_201206_RJ_ES VALUES LESS THAN (244,15) ENGINE = MyISAM,
PARTITION p36_201206_MG VALUES LESS THAN (244,22) ENGINE = MyISAM,
PARTITION p37_201206_PR_SC VALUES LESS THAN (244,31) ENGINE = MyISAM,
PARTITION p38_201206_RS VALUES LESS THAN (244,35) ENGINE = MyISAM,
PARTITION p39_201206_CO VALUES LESS THAN (244,44) ENGINE = MyISAM,
PARTITION p40_201206_BA_SE VALUES LESS THAN (244,50) ENGINE = MyISAM,
PARTITION p41_201206_NE VALUES LESS THAN (244,59) ENGINE = MyISAM,
PARTITION p42_201206_N VALUES LESS THAN (244,68) ENGINE = MyISAM,
PARTITION p43_201206_OUTROS VALUES LESS THAN (244,69) ENGINE = MyISAM,
PARTITION p44_201206_SP VALUES LESS THAN (274,10) ENGINE = MyISAM,
PARTITION p45_201206_RJ_ES VALUES LESS THAN (274,15) ENGINE = MyISAM,
PARTITION p46_201206_MG VALUES LESS THAN (274,22) ENGINE = MyISAM,
PARTITION p47_201206_PR_SC VALUES LESS THAN (274,31) ENGINE = MyISAM,
PARTITION p48_201206_RS VALUES LESS THAN (274,35) ENGINE = MyISAM,
PARTITION p49_201206_CO VALUES LESS THAN (274,44) ENGINE = MyISAM,
PARTITION p50_201206_BA_SE VALUES LESS THAN (274,50) ENGINE = MyISAM,
PARTITION p51_201206_NE VALUES LESS THAN (274,59) ENGINE = MyISAM,
PARTITION p52_201206_N VALUES LESS THAN (274,68) ENGINE = MyISAM,
PARTITION p54_201206_SP VALUES LESS THAN (305,10) ENGINE = MyISAM,
PARTITION p55_201206_RJ_ES VALUES LESS THAN (305,15) ENGINE = MyISAM,
PARTITION p56_201206_MG VALUES LESS THAN (305,22) ENGINE = MyISAM,
PARTITION p57_201206_PR_SC VALUES LESS THAN (305,31) ENGINE = MyISAM,
PARTITION p58_201206_RS VALUES LESS THAN (305,35) ENGINE = MyISAM,
PARTITION p59_201206_CO VALUES LESS THAN (305,44) ENGINE = MyISAM,
PARTITION p60_201206_BA_SE VALUES LESS THAN (305,50) ENGINE = MyISAM,
PARTITION p61_201206_NE VALUES LESS THAN (305,59) ENGINE = MyISAM,
PARTITION p62_201206_N VALUES LESS THAN (305,68) ENGINE = MyISAM,
PARTITION p63_201206_OUTROS VALUES LESS THAN (305,69) ENGINE = MyISAM,
PARTITION p64_201211_SP VALUES LESS THAN (335,10) ENGINE = MyISAM,
PARTITION p65_201211_RJ_ES VALUES LESS THAN (335,15) ENGINE = MyISAM,
PARTITION p66_201211_MG VALUES LESS THAN (335,22) ENGINE = MyISAM,
PARTITION p67_201211_PR_SC VALUES LESS THAN (335,31) ENGINE = MyISAM,
PARTITION p68_201211_RS VALUES LESS THAN (335,35) ENGINE = MyISAM,
PARTITION p69_201211_CO VALUES LESS THAN (335,44) ENGINE = MyISAM,
PARTITION p70_201211_BA_SE VALUES LESS THAN (335,50) ENGINE = MyISAM,
PARTITION p71_201211_NE VALUES LESS THAN (335,59) ENGINE = MyISAM,
PARTITION p72_201211_N VALUES LESS THAN (335,68) ENGINE = MyISAM,
PARTITION p73_201211_OUTROS VALUES LESS THAN (335,69) ENGINE = MyISAM,
PARTITION p74_201212_SP VALUES LESS THAN (366,10) ENGINE = MyISAM,
PARTITION p75_201212_RJ_ES VALUES LESS THAN (366,15) ENGINE = MyISAM,
PARTITION p76_201212_MG VALUES LESS THAN (366,22) ENGINE = MyISAM,
PARTITION p77_201212_PR_SC VALUES LESS THAN (366,31) ENGINE = MyISAM,
PARTITION p78_201212_RS VALUES LESS THAN (366,35) ENGINE = MyISAM,
PARTITION p79_201212_CO VALUES LESS THAN (366,44) ENGINE = MyISAM,
PARTITION p80_201212_BA_SE VALUES LESS THAN (366,50) ENGINE = MyISAM,
PARTITION p81_201212_NE VALUES LESS THAN (366,59) ENGINE = MyISAM,
PARTITION p82_201212_N VALUES LESS THAN (366,68) ENGINE = MyISAM,
PARTITION p83_201212_OUTROS VALUES LESS THAN (366,69) ENGINE = MyISAM,
PARTITION p84_201301_SP VALUES LESS THAN (762,10) ENGINE = MyISAM,
PARTITION p85_201301_RJ_ES VALUES LESS THAN (762,15) ENGINE = MyISAM,
PARTITION p86_201301_MG VALUES LESS THAN (762,22) ENGINE = MyISAM,
PARTITION p87_201301_PR_SC VALUES LESS THAN (762,31) ENGINE = MyISAM,
PARTITION p88_201301_RS VALUES LESS THAN (762,35) ENGINE = MyISAM,
PARTITION p89_201301_CO VALUES LESS THAN (762,44) ENGINE = MyISAM,
PARTITION p90_201301_BA_SE VALUES LESS THAN (762,50) ENGINE = MyISAM,
PARTITION p91_201301_NE VALUES LESS THAN (762,59) ENGINE = MyISAM,
PARTITION p92_201301_N VALUES LESS THAN (762,68) ENGINE = MyISAM,
PARTITION p93_201301_OUTROS VALUES LESS THAN (762,69) ENGINE = MyISAM) */