[MDEV-6355] slow update with partitioned myisam table - JIRA

Details

Type: Bug
Status: Closed
Priority: Trivial
Resolution: Incomplete
Affects Version/s: None
Fix Version/s: N/A
Component/s: None
Labels:
None

Description

hi guys, i'm checking a slow query (UPDATE) , 5 seconds, with a partitioned table

the query:

UPDATE nf SET cte_remetente_informacoes='nfe' WHERE (emitente_tipo,emitente_id,emitente_propriedade,nf_serie,nf)=("j", "110823", "0", "1cte", '-18')

the table:

CREATE TABLE `nf` (
  `emitente_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `emitente_id` int(10) unsigned NOT NULL DEFAULT '0',
  `emitente_propriedade` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `nf_serie` char(5) NOT NULL DEFAULT 'A',
  `nf` bigint(20) NOT NULL DEFAULT '0',
  `lote_arquivo_nfe` bigint(20) unsigned NOT NULL DEFAULT '0',
  `chave_nfe` varchar(45) NOT NULL DEFAULT '',
  `dpec_registro` varchar(45) NOT NULL DEFAULT '',
  `movimenta_estoque` enum('Y','N') NOT NULL DEFAULT 'N',
  `status` enum('ok','c','d','nf','i') NOT NULL DEFAULT 'ok',
  `nf_numero_compra` bigint(20) NOT NULL DEFAULT '0',
  `financeiro` enum('Y','N') NOT NULL DEFAULT 'Y',
  `remessa` enum('Y','N') NOT NULL DEFAULT 'N',
  `armazem` enum('Y','N') NOT NULL DEFAULT 'N',
  `armazem_tipo` enum('','entrada','retorno','terceiro','terceiro_co') NOT NULL DEFAULT '',
  `documento_auxiliar` enum('Y','N') NOT NULL DEFAULT 'N',
  `consumidor_final` enum('Y','N') NOT NULL DEFAULT 'N',
  `presenca_consumidor` enum('0','1','2','3','9') NOT NULL DEFAULT '0',
  `utilizar_coluna_desconto` enum('Y','N') NOT NULL DEFAULT 'N',
  `emitente` enum('c','f') NOT NULL DEFAULT 'c',
  `entrada_saida` enum('e','s') NOT NULL DEFAULT 's',
  `distribuidor_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `distribuidor_id` int(10) unsigned NOT NULL DEFAULT '0',
  `distribuidor_propriedade` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `distribuidor_org` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `distribuidor_org_red` int(11) NOT NULL DEFAULT '0',
  `gerente_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `gerente_id` int(11) NOT NULL DEFAULT '0',
  `supervisor_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `supervisor_id` int(11) NOT NULL DEFAULT '0',
  `vendedor_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `vendedor_id` int(10) unsigned NOT NULL DEFAULT '0',
  `cliente_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `cliente_id` int(10) unsigned NOT NULL DEFAULT '0',
  `cliente_propriedade` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `cliente_org` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `cliente_org_red` int(11) NOT NULL DEFAULT '0',
  `entrada_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `entrada_id` int(10) unsigned NOT NULL DEFAULT '0',
  `entrada_propriedade` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `entrada_org` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `entrada_org_red` int(11) NOT NULL DEFAULT '0',
  `saida_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `saida_id` int(10) unsigned NOT NULL DEFAULT '0',
  `saida_org` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `saida_org_red` int(11) NOT NULL DEFAULT '0',
  `saida_propriedade` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `sacado_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `sacado_id` int(10) unsigned NOT NULL DEFAULT '0',
  `local_retirada_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `local_retirada_id` int(10) unsigned NOT NULL DEFAULT '0',
  `local_entrega_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `local_entrega_id` int(10) unsigned NOT NULL DEFAULT '0',
  `referenciar_pedido` enum('Y','N') NOT NULL DEFAULT 'N',
  `unidade_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `lote_tipo` enum('v','c') NOT NULL DEFAULT 'v',
  `lote_spa` decimal(12,1) NOT NULL DEFAULT '0.0',
  `oe_tipo` enum('oe','fec') NOT NULL DEFAULT 'oe',
  `oe` bigint(20) NOT NULL DEFAULT '0',
  `oe_seq` mediumint(9) NOT NULL DEFAULT '0',
  `impressoes` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `digitador_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `digitador_id` int(10) unsigned NOT NULL DEFAULT '0',
  `sit_nf` enum('s','p','a','mov','nfes','nfep','nfea') NOT NULL DEFAULT 's',
  `data_emissao` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_emissao_emitente` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_saida_entrada` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_saida_entrada_emitente` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `data_saida_entrada_nemitente` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `cfop_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `cfop_id_red` mediumint(9) NOT NULL DEFAULT '0',
  `cfop_numero` char(5) NOT NULL DEFAULT '0.000',
  `cfop_descricao` varchar(100) NOT NULL DEFAULT '',
  `inscr_est_tributario` varchar(20) NOT NULL DEFAULT '',
  `cnpj_cpf` varchar(18) NOT NULL DEFAULT '',
  `inscr_est` varchar(20) NOT NULL DEFAULT '',
  `razao_nome` varchar(100) NOT NULL DEFAULT '',
  `fone` varchar(15) NOT NULL DEFAULT '',
  `fax` varchar(15) NOT NULL DEFAULT '',
  `pais` char(2) NOT NULL DEFAULT 'BR',
  `cep` char(9) NOT NULL DEFAULT '00000-000',
  `uf` char(2) NOT NULL DEFAULT 'SP',
  `municipio` varchar(50) NOT NULL DEFAULT '',
  `bairro` varchar(50) NOT NULL DEFAULT '',
  `endereco` varchar(100) NOT NULL DEFAULT '',
  `endereco_numero` varchar(25) NOT NULL DEFAULT '',
  `endereco_complemento` varchar(25) NOT NULL DEFAULT '',
  `valor_frete` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `valor_seguro` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `valor_outras_despesas` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `valor_total_produtos` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `desconto_nf` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `impostos_retidos` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
  `valor_total_nf` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `valor_total_nf_fatura` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
  `troco` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
  `transp_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `transp_id` int(10) unsigned NOT NULL DEFAULT '0',
  `transp_nome` varchar(100) NOT NULL DEFAULT '',
  `transp_pgt` enum('c','f','t','s') NOT NULL DEFAULT 'c',
  `transp_veiculo_id` int(11) NOT NULL DEFAULT '0',
  `transp_placa_uf` char(2) NOT NULL DEFAULT 'SP',
  `transp_placa` char(8) NOT NULL DEFAULT 'XXX-9999',
  `transp_cnpj_cpf` varchar(25) NOT NULL DEFAULT '',
  `transp_inscr_est` varchar(25) NOT NULL DEFAULT '',
  `transp_uf` char(2) NOT NULL DEFAULT 'SP',
  `transp_municipio` varchar(50) NOT NULL DEFAULT '',
  `transp_endereco` varchar(100) NOT NULL DEFAULT '',
  `transp_quant` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `transp_especie` varchar(50) NOT NULL DEFAULT '',
  `transp_marca` varchar(50) NOT NULL DEFAULT '',
  `transp_numero` varchar(50) NOT NULL DEFAULT '',
  `quant` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `pecas` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `pbruto` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `pliq` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `vbruto` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `vliq` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `informacoes_complementares` blob NOT NULL,
  `recebido` enum('','ok','dev','devpar','roubo','perdido') NOT NULL,
  `sit_recebido` enum('d','s','p','a','mov') NOT NULL DEFAULT 'd',
  `data_recebimento` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `recebedor` varchar(100) NOT NULL DEFAULT '',
  `recebimento_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `recebimento_id_red` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `obs_recebimento` blob NOT NULL,
  `data_digitacao_nf` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `icms_base_calculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `icms_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `icms_subst_base_calculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `icms_subst_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `ipi_base_calculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `ipi_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `iss_base_calculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `iss_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `sn_base_calculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `sn_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `pis_base_calculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `pis_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `cofins_base_calculo` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `cofins_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `devolucao` enum('Y','N') NOT NULL DEFAULT 'N',
  `devolucao_sem_controle` enum('Y','N') NOT NULL DEFAULT 'N',
  `devolvido` enum('N','P','T','OVER') NOT NULL DEFAULT 'N',
  `tipo_nota` enum('C','N','CI','NF','CTE-A','CTE-SUBST') NOT NULL DEFAULT 'N' COMMENT 'Complementar, Normal, Complementar imposto, Não fiscal',
  `nf_fatura_dp_tipo` enum('m','s') NOT NULL DEFAULT 'm',
  `nf_fatura_dp` bigint(20) unsigned NOT NULL DEFAULT '0',
  `agrupar_descricoes` enum('Y','N') NOT NULL DEFAULT 'N',
  `obs_cancelamento` longblob NOT NULL,
  `link_externo` longblob NOT NULL,
  `nota_fiscal_ecf_emitir` enum('Y','N','emitida') NOT NULL DEFAULT 'N',
  `nota_fiscal_retorno_simbolico_emitir` enum('Y','N','emitida') NOT NULL DEFAULT 'N',
  `cte_anulada` enum('Y','N') NOT NULL DEFAULT 'N',
  `cte_modal` enum('rodoviario','aquaviario','aereo','ferroviario','dutoviario') NOT NULL DEFAULT 'rodoviario',
  `cte_tipo_servico` enum('normal','subcontratacao','redespacho','redespacho intermediario') NOT NULL DEFAULT 'normal',
  `cte_tipo_pagamento` enum('pago','apagar','outros') NOT NULL DEFAULT 'pago',
  `cte_cte_referenciada` varchar(44) NOT NULL DEFAULT '',
  `cte_local_emissao_uf` varchar(2) NOT NULL DEFAULT '',
  `cte_local_emissao_cidade` varchar(255) NOT NULL DEFAULT '',
  `cte_local_inicio_uf` varchar(2) NOT NULL DEFAULT '',
  `cte_local_inicio_cidade` varchar(255) NOT NULL DEFAULT '',
  `cte_local_fim_uf` varchar(2) NOT NULL DEFAULT '',
  `cte_local_fim_cidade` varchar(255) NOT NULL DEFAULT '',
  `cte_tomador` enum('remetente','expedidor','recebedor','destinatario','outros') NOT NULL DEFAULT 'remetente' COMMENT 'sera copiado para o campo de cliente',
  `cte_tomador_outro_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `cte_tomador_outro_id` int(11) NOT NULL DEFAULT '0',
  `cte_remetente_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `cte_remetente_id` int(11) NOT NULL DEFAULT '0',
  `cte_remetente_informacoes` enum('nf','nfe','outras') NOT NULL DEFAULT 'nfe',
  `cte_expedidor_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `cte_expedidor_id` int(11) NOT NULL DEFAULT '0',
  `cte_recebedor_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `cte_recebedor_id` int(11) NOT NULL DEFAULT '0',
  `cte_destinatario_tipo` enum('f','j') NOT NULL DEFAULT 'f',
  `cte_destinatario_id` int(11) NOT NULL DEFAULT '0',
  `cte_obs_gerais` longblob NOT NULL,
  `cte_compl_carac_adicional_transp` varchar(255) NOT NULL DEFAULT '',
  `cte_compl_carac_adicional_serv` varchar(255) NOT NULL DEFAULT '',
  `cte_compl_frete_mun_origem` varchar(255) NOT NULL DEFAULT '',
  `cte_compl_frete_mun_destino` varchar(255) NOT NULL DEFAULT '',
  `cte_compl_rota_entrega` varchar(255) NOT NULL DEFAULT '',
  `cte_compl_codigo_origem` varchar(255) NOT NULL DEFAULT '',
  `cte_compl_codigo_destino` varchar(255) NOT NULL DEFAULT '',
  `cte_compl_passagens` longblob NOT NULL COMMENT 'JSON das passagens',
  `cte_compl_prev_data` enum('','sem','na data','ate data','apos data','periodo') NOT NULL DEFAULT '',
  `cte_compl_prev_hora` enum('','sem','na hora','ate hora','apos hora','periodo') NOT NULL DEFAULT '',
  `cte_compl_prev_data_1` date NOT NULL DEFAULT '0000-00-00',
  `cte_compl_prev_data_2` date NOT NULL DEFAULT '0000-00-00',
  `cte_compl_prev_hora_1` time NOT NULL DEFAULT '00:00:00',
  `cte_compl_prev_hora_2` time NOT NULL DEFAULT '00:00:00',
  `cte_compl_recebedor_retira` enum('Y','N') NOT NULL DEFAULT 'N',
  `cte_compl_recebedor_obs` longblob NOT NULL,
  `cte_carga_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `cte_carga_produto_pred` varchar(50) NOT NULL DEFAULT '',
  `cte_carga_produto_outras_car` varchar(50) NOT NULL DEFAULT '',
  `cte_rodoviario_rntrc` varchar(50) NOT NULL DEFAULT '',
  `cte_rodoviario_data_prev_entrega` date NOT NULL DEFAULT '0000-00-00',
  `cte_rodoviario_indicador_lotacao` enum('Y','N') NOT NULL DEFAULT 'N',
  `cte_rodoviario_ciot` varchar(255) NOT NULL DEFAULT '',
  `cte_rodoviario_lacres` longblob NOT NULL COMMENT 'JSON DOS LACRES',
  `cte_rodoviario_veiculos` longblob NOT NULL COMMENT 'JSON veiculo_id',
  `cte_rodoviario_motoristas` longblob NOT NULL COMMENT 'JSON motoristas_id',
  `cte_aquaviario_bc_afrmm` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `cte_aquaviario_valor_afrmm` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `cte_aquaviario_navio_id` varchar(50) NOT NULL DEFAULT '',
  `cte_aquaviario_navio_irin` varchar(50) NOT NULL DEFAULT '',
  `cte_aquaviario_numero_booking` varchar(50) NOT NULL DEFAULT '',
  `cte_aquaviario_numero_controle` varchar(50) NOT NULL DEFAULT '',
  `cte_aquaviario_numero_viagem` varchar(50) NOT NULL DEFAULT '',
  `cte_aquaviario_direcao` enum('N','S','L','O') NOT NULL DEFAULT 'N',
  `cte_aquaviario_tipo_navegacao` enum('','cabotagem','interior') NOT NULL DEFAULT '',
  `cte_aquaviario_porto_emb` varchar(255) NOT NULL DEFAULT '',
  `cte_aquaviario_porto_transbordo` varchar(255) NOT NULL DEFAULT '',
  `cte_aquaviario_porto_destino` varchar(255) NOT NULL DEFAULT '',
  `cte_aquaviario_balsas` longblob NOT NULL COMMENT 'JSON BALSAS',
  `cte_auto_tipo_valor` enum('%','pbruto','ct') NOT NULL DEFAULT '%',
  `cte_auto_valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
  `cte_auto_item_id` int(11) NOT NULL DEFAULT '0',
  `cte_auto_item_id_red` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`emitente_tipo`,`emitente_id`,`nf`,`nf_serie`,`emitente_propriedade`),
  KEY `oe` (`unidade_id`,`oe_tipo`,`oe`,`oe_seq`),
  KEY `spamov` (`unidade_id`,`lote_tipo`,`lote_spa`),
  KEY `dp` (`saida_tipo`,`saida_id`,`nf_fatura_dp_tipo`,`nf_fatura_dp`),
  KEY `spavendas` (`unidade_id`,`data_emissao_emitente`),
  KEY `recebimento` (`nf`,`sit_nf`,`sit_recebido`,`recebido`,`oe`,`emitente_id`),
  KEY `nf_serie` (`emitente_tipo`,`emitente_id`,`nf_serie`,`nf`),
  KEY `tmp_nf2` (`emitente_tipo`,`emitente_id`,`emitente_propriedade`,`nf_serie`,`nf`),
  KEY `compra` (`cliente_tipo`,`cliente_id`,`lote_tipo`,`financeiro`,`distribuidor_tipo`,`distribuidor_id`),
  KEY `venda` (`cliente_tipo`,`cliente_id`,`data_emissao_emitente`),
  KEY `chave_nfe` (`chave_nfe`),
  KEY `digitacao` (`data_digitacao_nf`,`nf_serie`),
  KEY `distribuidor` (`distribuidor_tipo`,`distribuidor_id`,`data_emissao_emitente`),
  KEY `oe2` (`unidade_id`,`oe`,`oe_seq`,`oe_tipo`),
  KEY `ecf` (`nota_fiscal_ecf_emitir`,`sit_nf`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (nf)
(PARTITION s0 VALUES LESS THAN (0) ENGINE = MyISAM,
 PARTITION s1 VALUES LESS THAN (100000) ENGINE = MyISAM,
 PARTITION s2 VALUES LESS THAN (450000) ENGINE = MyISAM,
 PARTITION s3 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

the partition count:

select 
IF(nf<0,'s0',
IF(nf<100000,'s1',
IF(nf<450000,'s2','s3'))) as partition_id,
COUNT(*)
from nf
group by partition_id

partition_id	COUNT
s0	723
s1	562100
s2	363599
s3	104934

could anyone explain why a table with only one unique key (primary key), too slow when updating a column not indexed, with a partition with only 723 rows?

the query execution time is 5seconds
the table size is :

show table status like 'nf'

Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
nf	MyISAM	10	Dynamic	1031356	898	926877532	0	324238336	0		2014-06-17 16:31:50	2014-06-17 17:19:46	2014-06-17 16:34:49	latin1_swedish_ci		partitioned

observations:

1) MARIADB VERSION 10.0.1 (i could upgrade if needed)
2) IOSTAT show only 54kb write, 0kb read
maybe a MDL lock ?
3) at show processlist the query status = "updating"
4) no process running just the UPDATE
5) i run optimize table nf, before this test
6) no hardware error, no md raid 1 error, no disk slow speed, no smartctl error, no xfs error, no disk full error

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

roberto spadim added a comment - 2014-06-18 06:04

please close
using field=const AND field=const .... worked, with (fields...)=(conts...) got a big problem check:

using field=const and field=const:

Variable_name	Value
Handler_commit	0
Handler_delete	0
Handler_discover	0
Handler_external_lock	0
Handler_icp_attempts	0
Handler_icp_match	0
Handler_mrr_init	0
Handler_mrr_key_refills	0
Handler_mrr_rowid_refills	0
Handler_prepare	0
Handler_read_first	0
Handler_read_key	1
Handler_read_last	0
Handler_read_next	1
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
Handler_rollback	0
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_tmp_update	0
Handler_tmp_write	0
Handler_update	0
Handler_write	0

using ()=():

Variable_name	Value
Handler_commit	0
Handler_delete	0
Handler_discover	0
Handler_external_lock	0
Handler_icp_attempts	0
Handler_icp_match	0
Handler_mrr_init	0
Handler_mrr_key_refills	0
Handler_mrr_rowid_refills	0
Handler_prepare	0
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	659
Handler_rollback	0
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_tmp_update	0
Handler_tmp_write	656
Handler_update	0
Handler_write	0

Show

roberto spadim added a comment - 2014-06-18 06:04 please close using field=const AND field=const .... worked, with (fields...)=(conts...) got a big problem check: using field=const and field=const: Variable_name Value Handler_commit 0 Handler_delete 0 Handler_discover 0 Handler_external_lock 0 Handler_icp_attempts 0 Handler_icp_match 0 Handler_mrr_init 0 Handler_mrr_key_refills 0 Handler_mrr_rowid_refills 0 Handler_prepare 0 Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 1 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 Handler_rollback 0 Handler_savepoint 0 Handler_savepoint_rollback 0 Handler_tmp_update 0 Handler_tmp_write 0 Handler_update 0 Handler_write 0 using ()=(): Variable_name Value Handler_commit 0 Handler_delete 0 Handler_discover 0 Handler_external_lock 0 Handler_icp_attempts 0 Handler_icp_match 0 Handler_mrr_init 0 Handler_mrr_key_refills 0 Handler_mrr_rowid_refills 0 Handler_prepare 0 Handler_read_first 0 Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 659 Handler_rollback 0 Handler_savepoint 0 Handler_savepoint_rollback 0 Handler_tmp_update 0 Handler_tmp_write 656 Handler_update 0 Handler_write 0

Hide

Permalink

Elena Stepanova added a comment - 2014-06-18 12:19

Hi Roberto,

Could you please provide
EXPLAIN EXTENDED <query> ; SHOW WARNINGS;
and
EXPLAIN PARTITIONS <query>
for the problematic query and fixed query?

Thanks.

And yes, please upgrade from 10.0.1. There have been enormous number of changes, merges and fixes since that version, partitioning included.

Show

Elena Stepanova added a comment - 2014-06-18 12:19 Hi Roberto, Could you please provide EXPLAIN EXTENDED <query> ; SHOW WARNINGS; and EXPLAIN PARTITIONS <query> for the problematic query and fixed query? Thanks. And yes, please upgrade from 10.0.1. There have been enormous number of changes, merges and fixes since that version, partitioning included.

Hide

Permalink

roberto spadim added a comment - 2014-06-18 15:52

hi Elena, i will upgrade the server, and try the explain, since 10.0.1 don't have explain update queries

Show

roberto spadim added a comment - 2014-06-18 15:52 hi Elena, i will upgrade the server, and try the explain, since 10.0.1 don't have explain update queries

Hide

Permalink

roberto spadim added a comment - 2014-06-18 15:55

with version 10.0.1 , select execute as expected, just update have this problem

select result ()=(), less than 1 second, as fast as "field=const and ...":

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	teste	const	PRIMARY,recebimento,nf_serie,tmp_nf2	PRIMARY	19	const,const,const,const,const	1

"field=const and ...":

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	teste	const	PRIMARY,recebimento,nf_serie,tmp_nf2	PRIMARY	19	const,const,const,const,const	1

Show

roberto spadim added a comment - 2014-06-18 15:55 with version 10.0.1 , select execute as expected, just update have this problem select result ()=(), less than 1 second, as fast as "field=const and ...": id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE teste const PRIMARY,recebimento,nf_serie,tmp_nf2 PRIMARY 19 const,const,const,const,const 1 "field=const and ...": id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE teste const PRIMARY,recebimento,nf_serie,tmp_nf2 PRIMARY 19 const,const,const,const,const 1

Hide

Permalink

Elena Stepanova added a comment - 2014-07-19 18:39

Closing for now as incomplete, please comment to re-open if you have new information on this issue.

Show

Elena Stepanova added a comment - 2014-07-19 18:39 Closing for now as incomplete, please comment to re-open if you have new information on this issue.

slow update with partitioned myisam table

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile