Otimização tabela

t0th · Dezembro 8, 2009

Olá,

tenho a seguinte tabela:

CREATE TABLE IF NOT EXISTS `produtos` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `lojas_id` int(10) unsigned NOT NULL DEFAULT '0',
  `nome` varchar(60) DEFAULT NULL,
  `codigo` varchar(15) DEFAULT NULL,
  `preço` varchar(10) DEFAULT NULL,
  `descc` text,
  `foto` varchar(200) DEFAULT NULL,
  `lastUpdate` varchar(22) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `lojas_id` (`lojas_id`),
  KEY `codigo` (`codigo`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=62123;

--
-- Restrições para a tabela `produtos`
--
ALTER TABLE `produtos`
  ADD CONSTRAINT `produtos_ibfk_1` FOREIGN KEY (`lojas_id`) REFERENCES `lojas` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

tenho essa query:

SELECT SQL_CACHE produtos.nome, produtos.preço, lojas.nome AS nomeLoja, site, produtos.id
FROM produtos
INNER JOIN lojas ON lojas.id = produtos.lojas_id
WHERE (
produtos.nome LIKE '%Estabilizador%'
OR produtos.nome LIKE '%ESTAB.%'
OR produtos.nome LIKE '%ESTAB%'
)
AND produtos.nome LIKE '%1000%'
AND produtos.nome LIKE '%110%'
AND lojas.publish =1
ORDER BY produtos.nome ASC
LIMIT 0 , 100

tenho 35 mil registro... e tem horas que a query(essa query em cima depende do que o usuário digita) demora uns 10 segundos pra retornar deixando meu server/site off

obrigado e espero resposta

Editado Dezembro 10, 2009 por Denis Courcy
melhorar entendimento do código

Denis Courcy · Dezembro 8, 2009

Oi, 't0th'

Já sei onde está seu erro. Mas para não restar dúvidas, por favor envie todos os índices da tabela produtos, com seus respectivos atributos(campos).

Envie, também, o engine das tabelas (MyISAM, InnoDB, etc)

Denis Courcy · Dezembro 9, 2009

Oi, 't0th'!

Como você não respondeu até agora e para não deixar o povo sem saber qual é seu erro, aqui vai a dica.

Passe a tabela produtos para o engine MyISAM e crie indices tipo FULLTEXT.

Depois disso, elimine as pesquisas com LIKE, que são o maior atraso, pois provocam TABLE SCAN e põe abaixo toda a vantagem de usar indices..

t0th · Dezembro 9, 2009

ola,

os indices são:

Nome chave Tipo Campo

PRIMARY PRIMARY 37826 id

lojas_id INDEX 37 lojas_id

codigo INDEX 37826 codigo

tipo da tabela produtos: InnoDB

não posso retirar o LIKE, pois ai acabaria com a minha pesquisa..

para trocar para MyISAM teria que trocar as tabelas pai para MyISAM, certo? não acabaria com minhas chaves extrangeiras?

quais campos posso criar indice FULLTEXT? nome?

obrigado

t0th · Dezembro 9, 2009

olá,

modifiquei todas as tabelas para MyISAM

pode me sugerir quais campos para indice FULLTEXT?

obrigado

Editado Dezembro 9, 2009 por t0th

Denis Courcy · Dezembro 10, 2009

Oi, 't0th'

Não havia a necessidade de passar todas as tabelas para MyISAM. somente a tabela de produtos.

Ao passar uma tabela para MyIsam ela perde a integridade referencial pois o engine MyISAM ignora relacionamento pai/filho entre tabelas. Neste caso seu controle passa a ser por programação através de sua linguagem de programação normal e/ou trigger, storage procedure, etc.

O campo nome da tabela produtos é o indicado para indice fulltext, pois no select que você passou este é o campo que recebe a carga do LIKE.

Dica.: Em suas consultas sempre verifique se os atributos em uma condição WHERE possuem indices. É sempre interessante criar índices para estes campos para que as consultas sejam aceleradas.

Tome o cuidado de não criar índices demais, pois eles podem degradar o sistema, tembém.

t0th · Dezembro 10, 2009

valeu, Denis..

mas como ficaria a query sem os LIKE?

obrigadão

Denis Courcy · Dezembro 10, 2009

Oi, 't0th'

Os indices fulltext proporcionam buscas dinâmicas tais como as relaizadas pelo google.

Veja o tópico abaixo e se você ainda tiver dúvidas não se acanhe em perguntar.

(Resolvido) Pesquisa full text

t0th · Dezembro 10, 2009

olá,

obrigado pela resposta.. dei uma estudada e cheguei a essa 'conversão'

a query:

SELECT sql_cache produtos.nome,produtos.precco,lojas.nome as nomeLoja, site, produtos.id 
FROM produtos inner join lojas on lojas.id = produtos.lojas_id where 
( produtos.nome LIKE '%processador%' or produtos.nome LIKE '%cpu%')
and produtos.nome LIKE '%intel%' and lojas.publish=1

para:

SELECT produtos.nome, produtos.precco, lojas.nome AS nomeLoja, site, produtos.id
FROM produtos
INNER JOIN lojas ON lojas.id = produtos.lojas_id
WHERE
(MATCH (produtos.nome) AGAINST ('+processador' IN BOOLEAN MODE) or
MATCH (produtos.nome) AGAINST ('+cpu' IN BOOLEAN MODE) )
and
MATCH (produtos.nome) AGAINST ('+intel' IN BOOLEAN MODE)
AND lojas.publish =1

ta certo isso ou tem alguma maneira para melhorar?

obrigado

Editado Dezembro 10, 2009 por t0th

Denis Courcy · Dezembro 10, 2009

Oi, "melhorar sempre" é o lema. Mas é bom que você teste primeiro e veja os resultados.

Não esqueça que palavras compostas dentro das variáveis "processador", "cpu" e "intel", ou seja as variáveis dentro dos MATCHES de fulltext deverão estar entre aspas ou você terá surpresas ingratas nas respostas.

Por exemplo: buscar por denis courcy trará todos os elementos com denis e todos os elementos com courcy independente de serem a mesma pessoa ou não, mas buscar por "denis courcy" só trará os elementos que corresponderem a string desejada.

t0th · Dezembro 14, 2009

Olá,

SELECT sql_cache produtos.nome,produtos.preço,lojas.nome as nomeLoja, site, produtos.id 
FROM produtos inner join lojas on lojas.id = produtos.lojas_id 
WHERE ( MATCH (produtos.nome) AGAINST ('+notebook' IN BOOLEAN MODE) or MATCH (produtos.nome) AGAINST ('+nb ' IN BOOLEAN MODE)) and lojas.publish=1

só retorna os registro com notebook não retorna os 'nb '

alguém sabe o que pode ser?

obrigado

Editado Dezembro 14, 2009 por Denis Courcy

Denis Courcy · Dezembro 14, 2009

Oi, 't0th'

Por que você não usa somente uma pesquisa em match? Veja o exemplo abaixo se funciona

SELECT sql_cache produtos.nome,produtos.preço,lojas.nome as nomeLoja, site, produtos.id 
FROM produtos inner join lojas on lojas.id = produtos.lojas_id 
WHERE  MATCH (produtos.nome) AGAINST ( CONCAT('+notebook', " ", '+nb ' IN BOOLEAN MODE)) and lojas.publish = 1

Faça o teste e reporte o resultado.

t0th · Dezembro 14, 2009

olá,

obrigado pela resposta

mudei o parenteses de lugar pra usar certo o concat

SELECT SQL_CACHE produtos.nome, produtos.preço, lojas.nome AS nomeLoja, site, produtos.id
FROM produtos
INNER JOIN lojas ON lojas.id = produtos.lojas_id
WHERE MATCH (produtos.nome) AGAINST (CONCAT( '+notebook', " ", '+nb ' ) IN BOOLEAN
MODE)
AND lojas.publish =1

o resultado é o mesmo que a query que te passei antes mas o problema é que ele não está pegando os registro com 'nb ' só os 'notebook'

lendo em http://dev.mysql.com/doc/refman/4.1/pt/ful...ine-tuning.html

meu servidor ta setado 'ft_min_word_len=3'

minimo 3 carecteres.. estranho que tem espaço ali após o 'nb' => 'nb '

não tenho acesso as configurações do servidor pra mudar o valor de ft_min_word para 2 e meu host me disse que não poderia!

alguma sugestão?

muito obrigado

Denis Courcy · Dezembro 15, 2009

Oi, 't0th'

Já que você não pode fazer um ajuste mais refinado em ft_min_word_len, vamos ter que buscar de outra forma e tentar evitar um TABLE SCAN.

Faça:

CREATE INDEX prod_nome ON produtos (nome);

Tente:

EXPLAIN SELECT SQL_CACHE produtos.nome, produtos.preço, lojas.nome AS nomeLoja, site, produtos.id
FROM produtos 
INNER JOIN lojas ON lojas.id = produtos.lojas_id
WHERE (MATCH (produtos.nome) AGAINST ( '+notebook' IN BOOLEAN MODE) OR LOCATE('nb', produtos.nome) <> 0)
AND lojas.publish =1

O explain serve para ver se a query está usando os indices. Se não estiver usando o indice prod_nome então force o uso dele conforme abaixo.

EXPLAIN SELECT SQL_CACHE produtos.nome, produtos.preço, lojas.nome AS nomeLoja, site, produtos.id
FROM produtos USE INDEX prod_nome
INNER JOIN lojas ON lojas.id = produtos.lojas_id
WHERE (MATCH (produtos.nome) AGAINST ( '+notebook' IN BOOLEAN MODE) OR LOCATE('nb', produtos.nome) <> 0)
AND lojas.publish =1

Retire o explain, teste a saída, veja o resultado e reporte.

t0th · Dezembro 15, 2009

olá,

obrigado pela resposta

removo o indice fulltext do nome antes?

obrigado

Editado Dezembro 15, 2009 por t0th

Denis Courcy · Dezembro 15, 2009

olá,
obrigado pela resposta
removo o indice fulltext do nome antes?
vou testar e reporto!

Não. Somente acrescente mais este índice.

t0th · Dezembro 15, 2009

deixei os dois indices

rodei a primeira query e tive o seguinte resultado:

consulta SQL: EXPLAIN SELECT SQL_CACHE produtos.nome, produtos.preço, lojas.nome AS nomeLoja, site, produtos.id FROM produtos INNER JOIN lojas ON lojas.id = produtos.lojas_id WHERE (MATCH (produtos.nome) AGAINST ( '+notebook' IN BOOLEAN MODE)) OR LOCATE('nb', produtos.nome) AND lojas.publish =1;

Registros: 2

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE produtos ALL NULL NULL NULL NULL 36453 Using where

1 SIMPLE lojas eq_ref PRIMARY PRIMARY 4 lojasnoparagua.produtos.lojas_id 1 Using where

está usando o índice? pra mim parece que não..

testei a outra query e fica:

consulta SQL: EXPLAIN SELECT SQL_CACHE produtos.nome, produtos.preço, lojas.nome AS nomeLoja, site, produtos.id FROM produtos USE INDEX (prod_nome) INNER JOIN lojas ON lojas.id = produtos.lojas_id WHERE (MATCH (produtos.nome) AGAINST ('+notebook' IN BOOLEAN MODE)) OR LOCATE('nb', produtos.nome) AND lojas.publish =1;

Registros: 2

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE produtos ALL NULL NULL NULL NULL 36453 Using where

1 SIMPLE lojas eq_ref PRIMARY PRIMARY 4 lojasnoparagua.produtos.lojas_id 1 Using where

não sei se esta usando index aqui..

obrigado e espero resposta

Denis Courcy · Dezembro 15, 2009

Oi, 't0th'

Em nenhum dos casos usou indice para produtos e quem está afetando isto é o JOIN (parece normal).

Tente mais uma vez assim

EXPLAIN SELECT STRAIGHT_JOIN SQL_CACHE produtos.nome, produtos.preço, lojas.nome AS nomeLoja, site, produtos.id FROM produtos USE INDEX (prod_nome) INNER JOIN lojas ON lojas.id = produtos.lojas_id WHERE ((MATCH (produtos.nome) AGAINST ('+notebook' IN BOOLEAN MODE)) OR LOCATE('nb', produtos.nome) <> 0)AND lojas.publish =1

Se der certo retire o EXPLAIN do comando acima e veja em quanto tempo ele retorna o resultado.

Outra coisa. Existe índice para lojas.publish ?

t0th · Dezembro 15, 2009

Olá,

Denis, agradeço suas respostas rápidas...

VALE LEMBRAR QUE A query com o LOCATE retorna o resultado desejado: MUITO OBRIGADO!

executei o comando acima com EXPLAIN e retornou:

consulta SQL:

explain SELECT straight_join SQL_CACHE produtos.nome, produtos.preço, lojas.nome AS nomeLoja, site, produtos.id FROM produtos force INDEX ( prod_nome ) INNER JOIN lojas ON lojas.id = produtos.lojas_id WHERE ( ( MATCH ( produtos.nome ) AGAINST ( '+notebook' IN BOOLEAN MODE ) ) OR LOCATE( 'nb', produtos.nome ) <>0 ) AND lojas.publish =1 order by produtos.nome asc \G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: produtos

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 36535

Extra: Using where; Using filesort

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: lojas

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: lojasnoparagua.produtos.lojas_id

rows: 1

Extra: Using where

2 rows in set (0.00 sec)

ainda me parce que não usou o índice INDEX prod_nome nem o FULLTEXT

para sua pergunta, não.. não há índice para lojas.publish , vale lembrar que esse valor é sempre 0 ou 1

muito obrigado

Editado Dezembro 15, 2009 por t0th

t0th · Dezembro 16, 2009

alguma sugestão, galerA?

obrigado

t0th · Fevereiro 5, 2010

Olá,

modifiquei algumas coisas:

CREATE TABLE IF NOT EXISTS `produtos` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`lojas_id` int(10) unsigned NOT NULL DEFAULT '0',

`nome` varchar(60) DEFAULT NULL,

`codigo` varchar(15) DEFAULT NULL,

`preço` varchar(10) DEFAULT NULL,

`descc` text,

`foto` varchar(200) DEFAULT NULL,

`lastUpdate` varchar(22) NOT NULL,

PRIMARY KEY (`id`),

KEY `prod_nome2` (`nome`),

FULLTEXT KEY `prod_nome` (`nome`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=96227 ;

essa query:

----------------------

SELECT sql_cache produtos.nome,produtos.preço,lojas.nome as nomeLoja, site, produtos.id FROM produtos inner join lojas on lojas.id = produtos.lojas_id where (LOCATE('hd', produtos.nome) or MATCH (produtos.nome) AGAINST (' "HD EXT" "HD IDE" "HD SATA" "HD SCSI" "HD EXTERNO" "NB HD" "HARD DISK" "HD * P/ NOTEBOO" "HD 160" "HD 1.5TB " "HD 250" "HD 320" "HD 400 " "HD 500" "HD 640" "HD 750" "HD 80" "HD EXT " "HD EXTERNO" "HD IDE" "HD NB" "HD NTB" "HD SATA" "HD SCSI" "HD SSD" "HD USB" "HD-NB" "HD-NOTEBOOK" "NTB HD" ' IN BOOLEAN MODE)) and lojas.publish=1 ORDER BY produtos.nome

-----------------------

mesma que:

SELECT sql_cache produtos.nome,produtos.preço,lojas.nome as nomeLoja, site, produtos.id FROM produtos inner join lojas on lojas.id = produtos.lojas_id where ( produtos.nome LIKE '%hd%' or produtos.nome LIKE '%HD EXT%' or produtos.nome LIKE '%HD IDE%' or produtos.nome LIKE '%HD SATA%' or produtos.nome LIKE '%HD SCSI%' or produtos.nome LIKE '%HD EXTERNO%' or produtos.nome LIKE '%NB HD%' or produtos.nome LIKE '%HARD DISK%' or produtos.nome LIKE '%HD * P/ NOTEBOO%' or produtos.nome LIKE '%HD 160%' or produtos.nome LIKE '%HD 1.5TB %' or produtos.nome LIKE '%HD 250%' or produtos.nome LIKE '%HD 320%' or produtos.nome LIKE '%HD 400 %' or produtos.nome LIKE '%HD 500%' or produtos.nome LIKE '%HD 640%' or produtos.nome LIKE '%HD 750%' or produtos.nome LIKE '%HD 80%' or produtos.nome LIKE '%HD EXT %' or produtos.nome LIKE '%HD EXTERNO%' or produtos.nome LIKE '%HD IDE%' or produtos.nome LIKE '%HD NB%' or produtos.nome LIKE '%HD NTB%' or produtos.nome LIKE '%HD SATA%' or produtos.nome LIKE '%HD SCSI%' or produtos.nome LIKE '%HD SSD%' or produtos.nome LIKE '%HD USB%' or produtos.nome LIKE '%HD-NB%' or produtos.nome LIKE '%HD-NOTEBOOK%' or produtos.nome LIKE '%NTB HD%') and lojas.publish=1 order by produtos.nome

fiz um explain nas duas queries e dá o seguinte:

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: produtos

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 42080

Extra: Using where; Using filesort

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: lojas

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: lojasnoparagua.produtos.lojas_id

rows: 1

Extra: Using where

alguém pode me dizer se essas queries estão usando o índice?

no phpMyAdmin a cardinalidade:

prod_nome2 INDEX 42080

prod_nome FULLTEXT 1

muito obrigado e desde já agaredeço

t0th · Fevereiro 8, 2010

olá,

se tiver alguém que pode me ajudar com esse problema eu pago pelo trabalho...

só me mandar uma MP

obrigado e espero resposata

Entrar

Otimização tabela

Pergunta

Link para o comentário

Compartilhar em outros sites

21 respostass a esta questão

Posts Recomendados

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Estatísticas dos Fóruns