Problema like em 3.000 caracteres e 56 mi de registros

[castmetal 0]

Ultimamente tenho problemas em uma base de dados, onde tenho que armazenar um dado de 3000 caracteres em um campo.

 

O problema não é o armazenamento e sim a consulta. Procurei ajuda, tentei índices e etc. Nada resolve, pois em um banco com mais de 56 milhões de registros, fazer um like em uma tabela com vários joins e num campo com 3000 caracteres, não há índice que melhore.

 

Pensei em uma solução. Só que está ficando inviável devido ao número alto de subselects.

 

Alguma idéia?

 

Tentei separar em palavras chaves, só que para fazer o like é inviável. Uso Mysql ou PostgreSQL.

 

Tabela: patente

Campos: cod Integer Auto-increment Primary Key

numero Varchar(14) NOT NULL UNIQUE INDEX

Tabela: resumo_patentes

Campos: cod Integer Auto-increment Primary Key

cod_palavra_resumo Integer NOT NULL Foreing Key References palavra_resumo(cod)

cod_patente Integer NOT NULL Foreing Key References patente(cod)

Tabela: palavra_resumo

Campos: cod Integer Auto-increment Primary Key

palavra Varchar(50) NOT NULL UNIQUE INDEX

O Resumo de uma patente é em média 3000 caracteres. Para armazená-lo eu quebro os espaços em um array de palavras e cada palavra é armazenada em palavra_resumo. Cada vez que vou armazenar uma palavra, verifico se ela já existe, caso não exista insiro os dados, se existe seleciono e depois recupero o seu código para armazená-lo em resumo_patentes. Cada palavra é armazenada na ordem de ocorrência que foi encontrada, se foi encontrada duas vezes, insiro seu código duplicado em resumo_patentes, de acordo com sua ocorrência.

 

 

Exemplos de Aplicação:

Patente: PI0567098-4

Resumo: Aplicação voltada para confinamento de exceção de metais. Aplicação de Estratégias comerciais.

 

Dados:

patente

cod numero

 

1 PI0567098-4

resumo_patentes

cod cod_palavra_resumo cod_patente ordem

 

1 1 1 1

2 2 1 2

3 3 1 3

4 4 1 4

5 5 1 5

6 6 1 6

7 5 1 7

8 7 1 8

9 1 1 9

10 5 1 10

11 8 1 11

12 9 1 12

palavra_resumo

cod palavra

1 Aplicação

2 voltada

3 para

4 confinamento

5 de

6 exceção

7 metais.

8 Estratégias

9 comerciais.

 

 

SQL:

 

SQL: procurar um número de patente executando like com “Aplicação voltada para confinamento”

select p.numero from 
			patente p inner join resumo_patentes r 
                                            on p.cod=r.cod_patente
			inner join palavra_resumo pr 
                                            on r.cod_palavra_resumo=pr.cod
where
	r.cod in(select resumo_patentes.cod from 
			patente inner join resumo_patentes
                                            on patente.cod=resumo_patentes.cod_patente
			inner join palavra_resumo
                                            on resumo_patentes.cod_palavra_resumo=palavra_resumo.cod
		where
			palavra_resumo.palavra in(select palavra from palavra_resumo where palavra like '%Aplicação%' order by palavra asc) group by patente.numero,resumo_patentes.cod asc)
		and (r.cod+1) in(select resumo_patentes.cod from 
			patente,resumo_patentes,palavra_resumo
		where
			patente.cod=p.cod and resumo_patentes.cod_patente=patente.cod and resumo_patentes.cod_palavra_resumo=palavra_resumo.cod and
			palavra_resumo.palavra='voltada' group by resumo_patentes.cod asc)
		and (r.cod+2) in(select resumo_patentes.cod from 
			patente,resumo_patentes,palavra_resumo
		where
			patente.cod=p.cod and resumo_patentes.cod_patente=patente.cod and resumo_patentes.cod_palavra_resumo=palavra_resumo.cod and
			palavra_resumo.palavra='para' group by resumo_patentes.cod asc)
		and (r.cod+3) in(select resumo_patentes.cod from 
			patente,resumo_patentes,palavra_resumo
		where
			patente.cod=p.cod and resumo_patentes.cod_patente=patente.cod and resumo_patentes.cod_palavra_resumo=palavra_resumo.cod and
			palavra_resumo.palavra='confinamento' group by resumo_patentes.cod asc)
	group by p.numero asc

[giesta 29]

erm...

 

(3k * 56kk) = 168kkk (168.000.000.000) de entradas, acredito que você deveria começar a dividir a tabela por categoria, eu sei q isso nao esta mto certo , mas 168 bilhoes de entradas eh um pouco grande pro caminhaozinho do mysql...

[castmetal 0]

Boa Tarde,

 

Obrigado pela ajuda.

A divisão da tabela é até interessante, só que os dados aumentam bastante com o tempo.

 

O maior problema não é a quantidade de dados, pois até em tabelas menores o select é demorado.

 

Acho que o gargalo está em fazer um like num campo de 3000 caracteres.

Só não me vem em mente em como fazer o like para que fique otimizado ou dividir em chaves.

 

Não sei se alguém passou pela mesma situação.

Alguma sugestão?

[Prog 183]

Não conhecemos o seu ambiente...

 

Qual a config do equipamento? Quanto de memória? Qual a velocidade do disco? Qual o sistema operacional?

O MySQL esta otimizado para consultas? O serviço esta sendo executado sozinho ou esta instalado com outros serviços?

Qual tipo de motor para tabelas esta sendo utilizado? Você já procurou sobre FULL-TEXT INDEX?

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

 

Algumas dicas podem otimizar o processamento das suas buscas, por exemplo, algumas palavras podem ser desconsideradas, como palavras com menos de 5 caracteres. Isto é uma ideia, precisa saber se pode ser aplicada.

[castmetal 0]

Space Invaders respondendo a sua pergunta:

 

2 servidores intel core 2 quad 4GB de RAM, 350GB em disco, Western Digital 10000 RPM.

 

O Servidor é um Linux Ubuntu.

O Serviço é sozinho, dedicado.

As tabelas estão em InnoDB, portanto não posso usar o FULL TEXT INDEX.

 

Tentei usar tbm o PostgreSQL e sem sucesso tbm!

 

Usei uma configuração Tunning para Mysql que melhorou, mas continua lento.

 

Obrigado

[Prog 183]

No PostgreSQL você usou FULL-TEXT Search?

 

Você pode postar o resultado do comando EXPLAIN para a execução do comando SQL que você postou?

[Prog 183]

castmetal,

 

Conseguiu resolver o problema de performance?

 

Na outra mensagem perguntei sobre o plano de execução do SELECT. Se quiser continuar na busca de uma possível solução, poste o plano de execução para a sua consulta.

[giesta 29]

Pra q innodb? Existe necessidade de transação? caso nao haja troque pra MyISAM.

[castmetal 0]

Pra q innodb? Existe necessidade de transação? caso nao haja troque pra MyISAM.

 

Troquei para MyISAM e melhorou bastante. Só que não consigo fazer relacionamentos(Foreing Keys).

 

Alguma ajuda? Realmente a performance aumentou e muito.

[Prog 183]

Bom!

 

Aí entramos em outra questão... uma das razões do MyISAM ser mais veloz é o fato deste motor possuir menos recursos, por este motivo não existe integridade referencial em tabelas do tipo MyISAM, o controle deve ser estabelecido na aplicação.

 

Você precisa decidir por performance ou segurança. Mas ainda existe uma possibilidade de ter performance e segurança para o seu ambiente: criando 2 esquemas, um apenas para consultas (MyISAM) e outro para as demais tarefas (InnoDB).

[castmetal 0]

Bom!

 

Aí entramos em outra questão... uma das razões do MyISAM ser mais veloz é o fato deste motor possuir menos recursos, por este motivo não existe integridade referencial em tabelas do tipo MyISAM, o controle deve ser estabelecido na aplicação.

 

Você precisa decidir por performance ou segurança. Mas ainda existe uma possibilidade de ter performance e segurança para o seu ambiente: criando 2 esquemas, um apenas para consultas (MyISAM) e outro para as demais tarefas (InnoDB).

 

Boa Space Invaders,

 

estou fazendo como sugeriu.

 

Obrigado.