(Resolvido) Consulta Mysql Travando!

[Parenti]

Bom Dia, Eu tenho um Banco de Dados em Mysql com 1 GB de espaço em disco (tem mais de 200 mil registros)...

Uso isso para dar 1 visitas para gerar o cantor mais visitado:

$atualiza2 = mysql_query("UPDATE tb_cantor SET cantor_visitas = (cantor_visitas + 1) WHERE cat_cantor = '$id_cat'");

Uso isso para Listar o Registro (musica) atual:

$consulta = mysql_query("SELECT * FROM tb_musicas WHERE endereco = '$endereco2'") or die (mysql_error());

while($n = mysql_fetch_array($consulta)){

$title = $n["title"];

$conteudo = $n["conteudo"];

}

Para mostrar o resultado eu uso isso:

<? echo "$title"; ?>

<? echo "$conteudo"; ?>

Tudo em um unico arquivo

Só que quando eu rodo isso no servidor (tanto no da net, quanto no meu compudador localhost), o computador fica processando e trava...

Dai o kara do servidor disse que são muitos registros e tals....

Pensei: Como o Uol, Wordpress, Yahoo guarda os arquivos e os reculpera sem problemas????

Se alguém souber como faço para dar uma melhorada nissso ai eu agradeço.....

[Denis Courcy]

Bom Dia, Eu tenho um Banco de Dados em Mysql com 1 GB de espaço em disco (tem mais de 200 mil registros)...

Uso isso para dar 1 visitas para gerar o cantor mais visitado:

$atualiza2 = mysql_query("UPDATE tb_cantor SET cantor_visitas = (cantor_visitas + 1) WHERE cat_cantor = '$id_cat'");

Uso isso para Listar o Registro (musica) atual:

$consulta = mysql_query("SELECT * FROM tb_musicas WHERE endereco = '$endereco2'") or die (mysql_error());

while($n = mysql_fetch_array($consulta)){

$title = $n["title"];

$conteudo = $n["conteudo"];

}

Para mostrar o resultado eu uso isso:

<? echo "$title"; ?>

<? echo "$conteudo"; ?>

Tudo em um unico arquivo

Só que quando eu rodo isso no servidor (tanto no da net, quanto no meu compudador localhost), o computador fica processando e trava...

Dai o kara do servidor disse que são muitos registros e tals....

Pensei: Como o Uol, Wordpress, Yahoo guarda os arquivos e os reculpera sem problemas????

Se alguém souber como faço para dar uma melhorada nissso ai eu agradeço.....

Oi, Parenti!

Por acaso você tem índices para os campos cat_cantor e endereco ?

Com bases grandes ganha-se muito no tempo de consultas se os campos que estão nas cláusulas WHERE forem indexados.

Faça o teste de tempo. Observe em quanto tempo seu MySQL retorna a consulta sem o índice e emquanto tempo ele o fará com o índice.

Sem índice o MySQL entra numa condição que chamamos de TABLE SCAN, ou seja vai procurar registro a registro até encontrar o que ele precisa.

att

Denis Courcy

[Parenti]

Olá Denis Courcy!!!

Então, estou tentando, porém não tem como você fazer para mim?

"Por acaso você tem índices para os campos cat_cantor e endereco"

Só para eu entender a lógica, existem vários tutoriais, porém eu não consegui compreender....

Obrigado pela ajuda...

[Denis Courcy]

Olá Denis Courcy!!!

Então, estou tentando, porém não tem como você fazer para mim?

"Por acaso você tem índices para os campos cat_cantor e endereco"

Só para eu entender a lógica, existem vários tutoriais, porém eu não consegui compreender....

Obrigado pela ajuda...

Indice se cria deste jeito:

1 - indice unico. Ou seja, o campo chamado nome-do-campo não poderá ter valores iguais na tabela nome-da-tabela.

CREATE UNIQUE INDEX nome-do-indice ON nome-da-tabela (nome-do-campo)

2 - indice não único.Ou seja, o campo chamado nome-do-campo poderá ter valores repetidos na tabela nome-da-tabela.

CREATE INDEX nome-do-indice ON nome-da-tabela (nome-do-campo)

att

Denis Courcy

[Parenti]

Indice se cria deste jeito:

1 - indice unico. Ou seja, o campo chamado nome-do-campo não poderá ter valores iguais na tabela nome-da-tabela.

CREATE UNIQUE INDEX nome-do-indice ON nome-da-tabela (nome-do-campo)

2 - indice não único.Ou seja, o campo chamado nome-do-campo poderá ter valores repetidos na tabela nome-da-tabela.

CREATE INDEX nome-do-indice ON nome-da-tabela (nome-do-campo)

att

Denis Courcy

Nossa Denis Courcy, tú não é macaco gordo, porém está quebrando um galho em...kkkk

Tá, configurei o Mysql no meu Pc, e estou usando o MySQL-Front (já ouvi dizer que o Front não é muito bom, porém aqui em casa não tenho ADSL)...

No Front fiz assim:

Criei um novo banco de dados

Criei uma Tabela, e dentro da criação da Tabela posso Criar os fields e indices

Porém nesse lugar, só consegui criar 1 Indices (na verdade são 2, um para o ID que já é automático e outro para o campo que vou mais usar musica_endereco)

Dai Importei meu banco de dados antigo para esse com as configurações nova...

2 problemas:

mandei organizar (no phpadmin, só cliquei em cima do musica_endereco) e já travou o pc..

Acho que faltou eu mandar listar pelo indice que eu criei...

Como eu faço isso?

Como eu faço para criar indices para mais de 1 campo?

Se quizer posso mandar a estrutura sql para você dar uma olhada...

Obrigado mesmo pela ajuda....

[Denis Courcy]

2 problemas:

mandei organizar (no phpadmin, só cliquei em cima do musica_endereco) e já travou o pc..

Acho que faltou eu mandar listar pelo indice que eu criei...

1 ) Como eu faço isso?

2 ) Como eu faço para criar indices para mais de 1 campo?

3 ) Se quizer posso mandar a estrutura sql para você dar uma olhada...

Respostas

1 - Não faz. O MySQL VÊ que você tem índice(s) para a tabela e ele usa este(s) índice(s)

2 - CREATE INDEX nome-do-indice ON nome-da-tabela (nome-do-campo1, nome-do-campo2, ...)

3 - Seria uma boa idéia.

Perguntas:

Suas tabelas estão íntegras?

Como são os engines que você está usando (MyIsam ou InnoDB)?

att

Denis Courcy

[Parenti]

Esse cria tb_cantor:

CREATE TABLE `tb_cantor` (

`id` int(5) NOT NULL auto_increment,

`cantor` varchar(255) NOT NULL default '',

`cantor_visitas` bigint(255) NOT NULL default '1005',

`lista` char(1) NOT NULL default '',

`cat_cantor` varchar(255) NOT NULL default '',

PRIMARY KEY (`id`)

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

Tá eu sei, esse cria a tb_musicas:

CREATE TABLE `tb_musicas` (

`id` int(5) NOT NULL auto_increment,

`title` varchar(255) NOT NULL default '',

`musica` text NOT NULL,

`musica_endereco` varchar(255) NOT NULL default '',

`cantor` varchar(255) NOT NULL default '',

`conteudo` text NOT NULL,

`endereco` varchar(255) NOT NULL default '',

`cat_cantor` varchar(255) NOT NULL default '',

`musica_visitas` bigint(255) NOT NULL default '938',

PRIMARY KEY (`id`)

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

A questão: Já li esse tópico várias vezes (http://forum.ievolutionweb.com/index.php?showtopic=6803) e não consegui compreender a diferença entre MyISAM e BDB, os tablespaces InnoDB...

Teria como você passar esses código ai para mim, e eles já criar o Indices?

Caso: Sim; Teria como Mostrar como faço um consulta? para retornar todas as musicas_endereco do cantor?

Obrigado pela atenção...

Não sei, porém veja se ajuda:

<?

$atualiza2 = mysql_query("UPDATE tb_cantor SET cantor_visitas = (cantor_visitas + 1) WHERE cat_cantor = '$id_cat'");

$endereco2 = "letras-$id_cat-$id";

$atualiza = mysql_query("UPDATE tb_musicas SET musica_visitas = (musica_visitas + 1) WHERE endereco = '$endereco2'");

$consulta = mysql_query("SELECT * FROM tb_musicas WHERE endereco = '$endereco2'") or die (mysql_error());

while($n = mysql_fetch_array($consulta)){

$title = $n["title"];

$cantor = $n["cantor"];

$musica = $n["musica"];

$conteudo = $n["conteudo"];

$conteudo = nl2br($conteudo);

}

?>

Mostrar os dados:

<? echo "$title"; ?>

<? echo "$conteudo"; ?>

É dessa forma que eu uso para chamar os dados das tabelas criadas acima, e para dar um voto no cantor mais visitado e um na musica mais visitada...

[Denis Courcy]

Antes de tudo verifique se suas tabelas não estão corrompidas. leia o manual do mysql para isso.

O básico que você precisa entender nos engines é:

InnoDB = Tabela transacional, ou seja funciona dentro de transações e estas precisam ser completadas em sua totalidade para que haja integridade nas tabelas. Bom para ser usado se há grande entrada e saída de dados.

MyISAM = Tabelas Não Transacionais, não há controle de integridade. Se houver uma falha na gravação dos dados então uma tabela pode ter os dados gravados e outra não. Exemplo

pode haver item de nota fiscal sem haver a nota fiscal propriamente dita.

Agora, Vamos tentar entender o que você fez:

Antes de mais nada é EXTREMAMENTE RECOMENDÁVEL FAZER UM BACKUP de sua base de dados. Pois não me responsabilizarei por danos aos seus dados caso você opte por fazer as sugestões que coloco abaixo.

Você criou uma tabela tb_cantor com um campo id de tamanho 5 do tipo int auto_increment. Isto quer dizer que o valor deste campo vai de 1 a 9999 pois o último byte significativo é usado para guardar o "sinal de número negativo"

Se desejar aumentar o tamanho do campo sem utilizar mais espaço em disco para o valor para UNSIGNED. executando o comando:

ALTER TABLE tb_cantor MODIFY id INT(5) UNSIGNED NOT NULL AUTO_INCREMENT;

Porém para melhor identificar os atributos de cada tabela modifique o nome do campo id para id_cantor. então, fazendo os dois comandos simultaneamente, O narrado acima e este.

ALTER TABLE tb_cantor CHANGE id, id_cantor INT(5) UNSIGNED NOT NULL AUTO_INCREMENT;

Outra, ainda nesta tabela:

Se seu campo `cat_cantor` é o que entendi, ou deja categoria do cantor, então é um campo descritivo que está se repetindo em toda a sua tabela causando uma perda de espaço e conseqüentemente uma possibilidade grande de erro na manutenção do cadastro. por exemplo poderia estar escrito ROCK em uma linha e Rock em outra linha, ou ainda, Rock and Roll em outra linha o que dificultaria a pesquisa:

A sugestão é normalizar esta tabela criando uma nova tabela para armazenar estas categorias.

se você desejar seguir esta sugestão, faça assim:

1 - Crie a tabela categoria:

CREATE TABLE categoria (

id_categoria int(5) unsigned not null auto_increment,

`cat_cantor` varchar(255) NOT NULL default '',

PRIMARY KEY (id_categoria)

);

2 - Se a tabela tb_cantor tem muitos registros então deve-se crie um índice por cat_cantor

CREATE INDEX cat_cantor_index ON tb_cantor (cat_cantor);

3 - Dê carga dos dados na tabela categoria seria assim:

INSERT INTO categoria (cat_cantor)

select cat_cantor from tb_cantor group by cat_cantor;

4 - modifique a tabela tb_cantor

4.1 - Inclua o campo id_categoria

ALTER TABLE tb_cantor

ADD COLUMN id_categoria int(5) unsigned not null default 0;

5 Dê carga no campo id_categoria da tabela tb_cantor

UPDATE tb_cantor tbc

set tbc.id_categoria = (select id_categoria from categoria cat where cat.cat_cantor = tbc.cat_cantor);

6 exclua o índice cat_cantor_index (se você o criou antes)

DROP INDEX cat_cantor_index;

7 exclua o campo cat_cantor na tabela tb_cantor

ALTER TABLE tb_cantor

DROP COLUMN cat_cantor;

8 crie um indice por id_categoria para a tabela tb_cantor. Este servirá para estabelecer um relacionamento com a tabela categoria

CREATE INDEX id_categoria_index ON tb_cantor (id_categoria)

Pronto.

Se você fez os passos acima, você criou um relacionamento de um para muitos entre categoria e cantores. Ou seja Uma categoria pode estar em vários cantores.

Para você consultar a tabela cantor com a categoria deverá fazer assim:

SELECT tbc.cantor, cat.cat_categoria from tb_cantor tbc

INNER JOIN categoria cat ON cat.id_categoria = tbc.id_categoria

A tabela tb_músicas pelo que entendo guarda as músicas cantadas pro determinado(s) cantor(es)

então há um relacionamento de muitos para muitos entre musicas e cantores mas não vou entrar neste mérito agora. Vamos ficar na forma de um cantor para muitas musicas.

Você falou que tem 200.000 registros nesta tabela e ela está pesada. então vamos cortar algumas gordurinhas. Se você inserir o campo id_cantor na tabela tb_musicas e remover o campo cantor (porque ele já está na tabela tb_cantor) você vai passar de (255 * 200000) bytes para (5 * 2000000) bytes, no seu pior caso, além de normalizar a tabela tb_musicas.

Se você optar por fazer o que recomendo faça os passos abaixo:

1 criar um índice por cantor na tabela tb_musicas

CREATE INDEX cantor_index ON tb_cantor (cantor);

2 Incluir o campo id_cantor na tabela tb_musicas

ALTER TABLE tb_musicas

ADD COLUMN id_cantor INT(5) UNSIGNED NOT NULL default 0;

3 carregar valores na coluna id_cantor da tabela tb_musicas

UPDATE tb_musicas m

SET m.id_cantor = (select c.id_cantor FROM tb_cantor c where c.cantor = m.cantor);

4 excluir o índice cantor_index

DROP INDEX cantor_index;

5 Excluir o campo cantor da tabela tb_musicas

ALTER TABE tb_musicas

DROP COLUMN cantor;

6 Criar indice por id_cantor na tabela tb_musicas (para que possa ser estabelecido o relacionamento entre as tabelas.

CREATE INDEX id_cantor_index ON tb_cantor (cantor);

Pronto

Agora, para consultar que cantor canta que musica faça assim:

Select c.cantor, m.title, m.musica from tb_cantor c

INNER JOIN tb_musicas m ON c.id_cantor = m.id_cantor

De outra forma, se quero saber que música é cantada por que cantores, então tenho:

Select m.title, c.cantor, m.musica from tb_cantor c

INNER JOIN tb_musicas m ON c.id_cantor = m.id_cantor

O INNER JOIN trará somente as linhas que existirem nas duas tabelas.

Poderei, ainda buscar que músicas pertencem a que categoria.

como não foi definido um id_categoria para a tabela tb_musicas temos que nos arranjar assim:

SELECT c.cat_cantor, m.title FROM categoria c

INNER JOIN tb_cantor tbc on tbc.id_categoria = c.id_categoria

INNER JOIN tb_musicas m on tbc_id_cantor = m.id_cantor

Leia tudo com atenção, mais uma vez verifique a integridade de suas tabelas MYISAM, FAÇA BACKUP e reporte dúvidas se houver mais.

att

Denis Courcy

[Parenti]

Então Denis, deu os seguintes erros:

1) Erro

consulta SQL:

ALTER TABLE tb_cantor CHANGE id,

id_cantor INT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT

Mensagens do MySQL :

#1064 - Something is wrong in your syntax près de ' id_cantor INT(5) UNSIGNED NOT NULL AUTO_INCREMENT' à la ligne 1

2) Erro

consulta SQL: Documentação

UPDATE tb_cantor tbc SET tbc.id_categoria = ( SELECT id_categoria

FROM categoria cat

WHERE cat.cat_cantor = tbc.cat_cantor )

Mensagens do MySQL : Documentação

#1064 - Something is wrong in your syntax près de 'tbc

set tbc.id_categoria = (select id_categoria from categoria ' à la ligne 1

3) Erro

consulta SQL: Documentação

UPDATE tb_musicas m SET m.id_cantor = ( SELECT c.id_cantor

FROM tb_cantor c

WHERE c.cantor = m.cantor )

Mensagens do MySQL : Documentação

#1064 - Something is wrong in your syntax près de 'm

SET m.id_cantor = (select c.id_cantor FROM tb_cantor c where c' à la ligne 2

4) Erro

consulta SQL:

ALTER TABE tb_musicas DROP COLUMN cantor

Mensagens do MySQL : Documentação

#1064 - Something is wrong in your syntax près de 'TABE tb_musicas

DROP COLUMN cantor' à la ligne 2

5) --------------- No PHPAdmin -----------------

Índices:

Os seguintes índices parecem ser idênticos e um deles deve ser removido: cantor_index, id_cantor_index

O que eu faço???

Editado Novembro 9, 2007 por Parenti

[Denis Courcy]

Parenti

Tenha atenção porque estes procedimentos são em cascata. falhou o primeiro não adianta continuar com o resto. por isso conserte o primeiro da seguinte forma:

ALTER TABLE tb_cantor CHANGE id,

id_cantor INT( 5 ) UNSIGNED NOT NULL

e prossiga com os outros.

Informe onde houver um próximo erro.

att Denis Courcy

[Parenti]

Tá Denis, estou no

5) Dê carga no campo id_categoria da tabela tb_cantor

UPDATE tb_cantor tbc

set tbc.id_categoria = (select id_categoria from categoria cat where cat.cat_cantor = tbc.cat_cantor);

Está retornando esse erro:

Erro

consulta SQL:

UPDATE `tb_cantor` `tbc` SET `tbc`.`id_categoria` = ( SELECT `id_categoria`

FROM `categoria` `cat`

WHERE `cat`.`cat_cantor` = `tbc`.`cat_cantor` )

Mensagens do MySQL :

#1064 - Something is wrong in your syntax près de 'tbc`

set `tbc`.`id_categoria` = (select `id_categoria` from `cat' à la ligne 2

Já descobri que é a sintaxe, o que você tinha passado:

ALTER TABLE tb_cantor CHANGE id, id_cantor INT(5) UNSIGNED NOT NULL AUTO_INCREMENT;

E a forma que o PhpAdmin entende:

ALTER TABLE `tb_cantor` CHANGE `id` `id_cantor` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT

Só que nesse código acima não consegui entender o que é o que...

Se puder corrigir a sintaxe...

[Parenti]

Olá Denis Courcy!!

Consegui resolver!!!

No próprio PHPAdmin tem a opção: Índices

E por lá consegui fazer tudo, rodei, antes de criar o indices no campo endereco:

EXPLAIN SELECT * FROM tb_musicas WHERE endereco = 'meu endereco';

e rodei novamente depois de criar os Indices, e OK....

Valeu pela força...