I have broken my head to try to create indices, or change mysql configuration to improve a query but I'm not getting it. Could someone help me?
I am creating a system that has more than four million products and to improve the response time for the User, I am creating a materialized view.
To create this view I use the query below
INSERT into
consulta_atual
SELECT
fc.cd_categoria,
tbc.cd_categoria as cd_categoria_site,
tbc.nm_categoria,
fc.ds_subcategoria, ppf.cd_produto_price,
ppf2.cd_seq,
f.tp_fornecedor as tp_fornecedor ,
pp.nm_produto as name,
pp.nm_slug as nm_slug,
pf.cd_fornecedor as fornecedor,
f.url_img_fornecedor as url_img_fornecedor,
f.url_raiz_fornecedor as url_raiz_fornecedor,
pf.url_imagem as url_imagem,
concat(IFNULL(pf.url_produto_fornecedor_prefix,''),pf.url_produto_fornecedor,IFNULL(pf.url_produto_fornecedor_sufix,'')) as url_produto,
CONCAT('R$ ', REPLACE(REPLACE(REPLACE(FORMAT(ppf.vlr_produto, 2),'.',';'),',','.'),';',',')) as vlr_produto,
CONCAT(REPLACE(REPLACE(REPLACE(FORMAT(ppf.vlr_produto, 0),'.',';'),',','.'),';',','),' pontos') as pnt_produto,
ppf.vlr_produto as vlr_produto_original,
null,
null,
null
FROM
produto_price pp,
produto_fornecedor pf,
preco_produto_fornecedor ppf,
fornecedores f,
fornecedores_categorias fc,
vw_preco_atual ppf2,
tb_st_category tbc
WHERE
pp.cd_produto = pf.cd_produto_price
AND pf.cd_categoria = fc.cd_categoria
AND fc.cd_categoria_site = tbc.cd_categoria
AND pp.cd_produto = ppf2.cd_produto_price
AND pf.cd_fornecedor = f.cd_fornecedor
AND pf.cd_fornecedor = ppf2.cd_fornecedor
AND ppf2.cd_seq = ppf.cd_seq
However, with the passage of time, and increasing product it is taking about 4 hours to this table being created. Is it possible to improve shifting performance indices of the tables or configuration?
script create tables
CREATE TABLE IF NOT EXISTS `consulta_atual` (
`cd_categoria` int(11) NOT NULL DEFAULT '0',
`cd_categoria_site` int(11) DEFAULT NULL,
`ds_categoria` varchar(100) NOT NULL,
`ds_subcategoria` varchar(200) NOT NULL,
`cd_produto_price` bigint(11) NOT NULL,
`cd_seq` bigint(20) NOT NULL DEFAULT '0',
`tp_fornecedor` int(11) NOT NULL DEFAULT '0',
`name` varchar(300) NOT NULL,
`nm_slug` varchar(200) DEFAULT NULL,
`fornecedor` int(11) NOT NULL,
`url_img_fornecedor` varchar(200) NOT NULL,
`url_raiz_fornecedor` varchar(200) NOT NULL,
`url_imagem` varchar(500) NOT NULL,
`url_produto` varchar(500) NOT NULL,
`vlr_produto` varchar(49) NOT NULL DEFAULT '',
`pnt_produto` varchar(53) NOT NULL DEFAULT '',
`vlr_produto_original` float(10,2) NOT NULL,
`menor_valor` float DEFAULT NULL,
`maior_valor` float DEFAULT NULL,
`qtd_lojas` int(11) DEFAULT NULL,
PRIMARY KEY (`cd_produto_price`,`fornecedor`),
KEY `nm_slug_2` (`nm_slug`),
KEY `ds_categoria` (`ds_categoria`),
KEY `vlr_produto_original` (`vlr_produto_original`),
KEY `cd_categoria_site` (`cd_categoria_site`),
KEY `fornecedor` (`fornecedor`),
KEY `tp_fornecedor` (`tp_fornecedor`),
FULLTEXT KEY `name` (`name`),
FULLTEXT KEY `ds_categoria_2` (`ds_categoria`),
FULLTEXT KEY `ds_categoria_3` (`ds_categoria`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
and
CREATE TABLE IF NOT EXISTS `fornecedores` (
`cd_fornecedor` int(11) NOT NULL AUTO_INCREMENT,
`nm_fornecedor` varchar(50) NOT NULL,
`tp_fornecedor` int(11) NOT NULL DEFAULT '0',
`url_img_fornecedor` varchar(200) NOT NULL,
`dt_atualizacao` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
`url_raiz_fornecedor` varchar(200) NOT NULL,
PRIMARY KEY (`cd_fornecedor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;
CREATE TABLE IF NOT EXISTS `fornecedores_categorias` (
`cd_categoria` int(11) NOT NULL AUTO_INCREMENT,
`cd_fornecedor` int(11) NOT NULL,
`cd_categoria_site` int(11) DEFAULT NULL COMMENT 'Referencia a Categoria do SIte',
`ds_categoria` varchar(100) NOT NULL,
`url_categoria` varchar(200) NOT NULL,
`cd_prioridade` int(11) NOT NULL DEFAULT '1',
`ds_subcategoria` varchar(200) NOT NULL,
PRIMARY KEY (`url_categoria`),
UNIQUE KEY `cd_categoria` (`cd_categoria`),
KEY `ds_categoria` (`ds_categoria`),
KEY `cd_categoria_site` (`cd_categoria_site`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6112 ;
and
CREATE TABLE IF NOT EXISTS `preco_produto_fornecedor` (
`cd_seq` bigint(20) NOT NULL AUTO_INCREMENT,
`cd_produto_price` bigint(11) NOT NULL,
`cd_fornecedor` int(11) NOT NULL,
`dt_atualizacao` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`vlr_produto` float(10,2) NOT NULL,
`flg_sucesso` tinyint(1) NOT NULL,
PRIMARY KEY (`cd_seq`,`cd_produto_price`,`cd_fornecedor`),
KEY `dt_atualizacao` (`dt_atualizacao`),
KEY `cd_fornecedor` (`cd_fornecedor`),
KEY `vlr_produto` (`vlr_produto`),
KEY `cd_produto_price` (`cd_produto_price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=151452428 ;
CREATE TABLE IF NOT EXISTS `produto_fornecedor` (
`cd_produto_price` bigint(11) NOT NULL,
`ds_produto` varchar(5000) DEFAULT NULL,
`cd_categoria` int(11) NOT NULL,
`url_produto_fornecedor` varchar(500) NOT NULL,
`url_produto_fornecedor_prefix` varchar(200) DEFAULT NULL COMMENT 'Url Afiliados ANTES',
`url_produto_fornecedor_sufix` varchar(200) DEFAULT NULL COMMENT 'Url Afiliados DEPOIS',
`url_imagem` varchar(500) NOT NULL,
`cd_fornecedor` int(11) NOT NULL,
`flg_ativo` tinyint(1) NOT NULL,
`dt_atualizacao` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`cd_produto_price`,`cd_fornecedor`),
KEY `ds_produto_2` (`ds_produto`(333))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
and
CREATE TABLE IF NOT EXISTS `produto_price` (
`cd_produto` bigint(11) NOT NULL AUTO_INCREMENT,
`nm_produto` varchar(300) NOT NULL,
`nm_slug` varchar(200) DEFAULT NULL,
`cd_categoria` int(11) NOT NULL,
PRIMARY KEY (`cd_produto`),
UNIQUE KEY `nm_produto_2` (`nm_produto`),
UNIQUE KEY `nm_slug_2` (`nm_slug`),
FULLTEXT KEY `nm_produto` (`nm_produto`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3861158 ;
CREATE TABLE IF NOT EXISTS `tb_st_category` (
`cd_categoria` int(11) NOT NULL AUTO_INCREMENT,
`nm_categoria` varchar(100) NOT NULL,
`ds_categoria` varchar(500) DEFAULT NULL,
`url_img_categoria` varchar(100) DEFAULT NULL,
`nm_slug` varchar(100) DEFAULT NULL COMMENT 'Nome amigavel da categoria para poder indexar no google',
`flg_menu_topo` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Flag para aparecer ou nao no menu do site',
`flg_categoria_especial` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Flag para aparecer com prioridade (Ex.: Categoria de Natal)',
`cd_categoria_pai` int(11) NOT NULL DEFAULT '0' COMMENT 'Se tiver com numero maior que 0 é uma subcategoria',
`flg_ativo` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 desativado 1 ativo',
`dt_cadastro` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'data que foi criado a categoria',
PRIMARY KEY (`cd_categoria`),
FULLTEXT KEY `ds_categoria` (`ds_categoria`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=437 ;
CREATE TABLE IF NOT EXISTS `vw_preco_atual` (
`cd_produto_price` bigint(11) NOT NULL,
`cd_fornecedor` int(11) NOT NULL,
`cd_seq` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`cd_fornecedor`,`cd_produto_price`),
UNIQUE KEY `cd_seq` (`cd_seq`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
replace commands in the query are very expensive. Try to reduce them as much as possible. Index the fields used the where condition
I see you do insert the resultset in another table. So the records already inserted, do not need to be processed again. So best way is to run this query for the newly created records using the timestamp. If you don't have timestamps, introduce them newly. Once you process records upto a time, store it somewhere. And start from that time to current time. In this way of batching, you reduce the unnecessary time with the old records..