MySQL Forums
Forum List  »  Partitioning

Re: Index with partitioning
Posted by: Hélio Hélio
Date: February 04, 2013 08:03AM

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) */

Options: ReplyQuote


Subject
Views
Written By
Posted
2523
January 30, 2013 06:23AM
1488
February 01, 2013 10:20PM
Re: Index with partitioning
1699
February 04, 2013 08:03AM
1504
February 07, 2013 10:09PM
1466
February 08, 2013 06:55AM
1329
February 09, 2013 01:22PM
2034
February 14, 2013 12:26PM


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.