Consulta avançada MySQL

[eveschuh 1]

Senhores.
Esta é a primeira vez q publico um tópico no site.
Tenho um procedimento em funcionamento q está muito lento devido ao grande número de linhas da minha tabela
Minha tabela atualmente está com 200 mil linhas.
Vou tentar exemplificar de uma maneira mais simples.. mas minha duvida é se é possível fazer as contagens de uma coluna em uma mesma query
Bom vou exemplificar com 5 tabelas

Estado
tb_estado
id_estado name_estado
1 Rio Grande do Sul
2 Rio de Janeiro
3 São Paulo

Cidade
tb_cidade
id_cidade id_estado name_cidade
1 1 Porto alegre
2 1 Canoas
3 1 Novo Hamburgo
4 2 Rio de Janeiro
5 2 Cabo Frio
6 2 Angra dos Reis
7 3 São Paulo
8 3 Santos
9 3 Itu


Educação
tb_educacao
id_educacao name_educacao
1 Ensino Fundamental
2 Ensino Médio
3 Ensino Profissionalizante

Estado Civil
tb_estado_civil
id_estado_civil name_estado_civil
1 solteiro
2 casado
3 viuvo


e agora a tabela principal

Pessoas onde sx (0 = masculino e 1 = feminino)
tb_pessoa
id_pessoa name_pessoa sx_pessoa id_estado id_cidade id_educacao id_estado_civil
1 Fulano 0 1 1 1 2
2 Beltrano 0 2 4 1 2
3 Ciclano 0 1 1 3 1
4 Fulana 1 3 7 2 1
5 Beltrana 1 3 8 3 1
6 Ciclana 1 2 6 3 2
7 Fulanino 0 2 5 2 2
8 Beltranino 0 2 6 2 3
9 Fulanina 1 1 2 1 2
10 Beltraninda 1 1 3 1 1

TODAS AS IDS SAO PRIMARY KEY EM SUAS TABELAS E KEY NA TABELA PESSOA

bom tendo essas informações.. o que faço hoje é (claro q eu nomeio campo por campo mas por agora vou colocar *)

Select *
From tb_pessoa
inner Join tb_estado Using (id_estado)
inner Join tb_cidade Using (id_cidade)
inner Join tb_ensino Using (id_ensino)
inner Join tb_estado_civil Using (id_estado_civil)

desta maneira tenho a tabela (claro q utilizo paginação pois são 200 mil registros).
Mas o que acredito estar causando problema é que tenho q fazer inúmeros Selects para totalizar cada coluna.

por exemplo

Faço um Select para o Estado


Select Count(id_pessoa) as total_pessoas, name_estado
Fron tb_pessoas
inner Join tb_estado Using (id_estado)
Group id_estado


então ele me retorna

total_pessoas name_estado
4 Rio Grande do Sul
4 Rio de Janeiro
2 São Paulo


Faço outro para a Cidade

Select Count(id_pessoa) as total_pessoas, name_cidade
Fron tb_pessoas
inner Join tb_cidade Using (id_cidade)
Group id_cidade

outra pra Educação, outra para Estado Civil e uma na propria tabela para o ---o

cada consulta dessas leva em média 3 segundo em 200 mil registro. Então neste exemplo que realizo 6 Selects, o tempo é de 18 segundos para abrir a tela.

18 segundos na internet é inviável..

A pergunta que faço é:

Existe uma maneira de já contar todas as colunas em apensa 1 select, ou outra maneira que seja mais correta de fazer.

Como funciona o site do buscapé? do infojobs?
Eles operam de outra maneira ou eles operam desta maneira com um banco de dados ORACLE ?

Agradeço muito a ajuda de que puder dar-me este euxilio

Obrigado

[lokaodomau 61]

Desculpe a ignorância, mas os JOINs funcionam normalmente sem especificar relacionamento?

SELECT
  COUNT(id_pessoa) AS total_pessoas,
  name_cidade
FROM tb_pessoas AS p
INNER JOIN tb_cidade AS c USING(id_cidade) ON c.id_cidade = p.id_cidade
GROUP BY id_cidade

Agora a possível solução:

 

Faça um SELECT na tabela de `tb_pessoa` mas no lugar de exibir os campos faça o COUNT( DISTINCT {campo} ) as total_{campo}. Desta forma fará a contagem distinta entre cada campo, mas como os campos são especificados na tabela pessoa, não precisa fazer uso dos JOIN.

 

Caso queira detalhar certo campo, basta utilizar o GROUP BY no campo a ser detalhado.

[eveschuh 1]

Lokaodomau

Obrigado por sua resposta.

Sim .. tudo está funcionando em meu JOINs
O relacionamento é o comando USING
Quando se faz um JOIN entre tabelas que conténham a coluna de comparação com o mesmo nome e tipo, não é necessário especificar tabela.coluna com o comando ON (tba.id = tbb.id). Basta utilizar o Using(id) e ele utilizará essa coluna procurando-a em ambas as tabelas envolvidas.

 

Mas com relação a sua sugestão, ela seria de eu utilizar um comando assim:

 

[tr]SELECT
COUNT(DISTINCT(id_estado)) as total_estado,
COUNT(DISTINCT(id_cidade)) as total_cidade,

COUNT(DISTINCT(id_edicacao)) as total_edicacao,

COUNT(DISTINCT(id_estado_civil)) as total_estado_civil,

FROM tb_pessoas
[/tr]

 

e isso me responderá

 

total_estado total_cidade total_educacao total_estado_civil

3 8 3 3

 

Note q o total de cidades deu 8 pois não utilizei todas as 9 cidades da tb_cidade na tb_pessoa

 

Bom, infelizmente não é isso q quero..

O que quero é exatamente o que está já funcionando..

mas que seja feito em apenas um query ou de uma maneira q a consulta fique mais rápida

 

quero a resposta

Rio Grande do Sul = 4 pessoas
Rio de Janeiro = 4 pessoas
São Paulo = 2 pessoas

 

Porto Alegre = 2 pessoas

Canoas = 1 pessoa

 

e assim por diante

 

Hoje isso funciona mas eu tenho q fazer SELECTs separado como mencionei

 

Select Count(id_pessoa) as total_pessoas, name_estado
Fron tb_pessoas
inner Join tb_estado Using (id_estado)
Group id_estado

então ele me retorna o que quero

total_pessoas name_estado
4 Rio Grande do Sul
4 Rio de Janeiro
2 São Paulo

 

mas dai tenho q fazer

outro para a cidade

outro para a educação

outro para o estado civil

Tantos SELECTs em uma tabela tão grande ocasiona demora!

Então gostaria de saber se existe como fazer tudo de uma vez só.

 

Mas obrigado pela resposta mesmo assim

[lokaodomau 61]

Fiz alguns testes, o que consegui é o seguinte:

select
	c.name_cidade,
	e.name_estado,
	x.name_educacao,
	y.name_estado_civil,
	sum(total_pessoas) total_pessoas
from (
#sub query
select
	id_cidade,
	id_estado,
	id_educacao,
	id_estado_civil,
	COUNT(id_pessoa) as total_pessoas
from tb_pessoa
group by
	id_cidade,
	id_estado,
	id_educacao,
	id_estado_civil
) v
inner join tb_cidade c on v.id_cidade = c.id_cidade
inner join tb_estado e on v.id_estado = e.id_estado
inner join tb_educacao x on v.id_educacao = x.id_educacao
inner join tb_estado_civil y on v.id_estado_civil = y.id_estado_civil
group by 1;

Demora tanto assim porque checa todas as 200 mil linhas em cada consulta (3 segundos em 6 consultas = 18 segundos), depois seria truques da linguagem.

[Motta 645]

O que faço em casos como este nu ambiente Oracle é gerar um Crystal Reports com uma refencia cruzada

 

 

No caso poderia ser

 

____________educacao

_______________________estado civil

Estado

_cidade

 

 

E totalizadores em todos os niveis

 

Não conheço nunhuma ferramenta free semelhante que faça de forma automática.

[eveschuh 1]

Lokaodomau

 

Mais uma vez, obrigado por sua resposta.

 

Acho q você vai ser o cara que vai conseguir me ajudar.

 

Muito boa essa sua consulta mas ainda não é o que quero.

Porém a elementos bons ali que estarei utilizando para fazer testes tentando chegar no resultado que espero

 

Em sua quey, a resposta que resultará é:

 

 

name_cidade name_estado name_educacao name_estado_civil total_pessoas

Angra dos Reis Rio de Janeiro Ensino Médio Viúvo 1

Cabo Frio Rio de Janeiro Ensino Médio Casado 1

Canoas Rio Grande do Sul Ensino Fundamental Casado 1

Novo Hamburgo Rio Grande do Sul Ensino Fundamental Solteiro 1

Porto Alegre Rio Grande do Sul Ensino Fundamental Casado 1

Rio de Janeiro Rio de Janeiro Ensino Fundamental Casado 1

São Paulo São Paulo Ensino Médio Solteiro 1

 

 

 

e o que eu queria era algo assim.. ou pelo menos as colunas 2 e 3

name_coluna name_campo total_pessoas

id_estado Rio Grande do Sul 4

id_estado Rio de Janeiro 4

id_estado São Paulo 2

id_cidade Porto Alegre 2

id_cidade Canoas 1

id_cidade Novo Hamurgo 1

id_cidade Rio de Janeiro 1

id_cidade Cabo Frio 1

id_cidade Angra dos Reis 2

id_cidade São Paulo 1

id_cidade Santos 1

id_educacao Ensino Fundamental 4

id_educacao Ensino Médio 2

id_educacao Ensino Profissionalizante 3

id_estado_civil Solteiro 4

id_estado_civil Casado 5

id_estado_civil Viúvo 1

 

Espero não estar abusando de sua boa vontade..

Obrigado pelas respostas até agora..

Obrigado Motta.

Boa dica..

Vou consultar por Crystal Reports no Mysql.

Se encontrar algo postarei aqui

Motta.

 

Obrigado pela dica

 

Vou pesquisar sobre Crystal Reports no mysql

 

Se achar algo postarei aqui

[lokaodomau 61]

Cara, acho que o problema não é como será exibido, mas o tempo que leva para concluir a consulta.

 

De qualquer jeito, teria que somar tudo, acredito que com este resumo, é possível fazer a soma utilizando uma linguagem que não seja SQL.

 

Mas vou continuar pensando sobre o assunto.

[eveschuh 1]

Senhores..

 

Após muito divagar, resolvi fazer de uma maneira em que o resultado me gere uma tabela com todos totais.

 

a tabela de resposta fica com todas as linas e uma coluna para cada total.

 

Ex

 

name_estado total_estado name_cidade total_cidade name_educacao total_educacao

Rio Grande do Sul 4 Porto Alegre 2 Ensino Fundamental 4

Rio Grande do Sul 4 Porto Alegre 2 Ensino Fundamental 4

Rio Grande do Sul 4 Novo Hamburgo 1 Ensino Médio 3

Rio Grande do Sul 4 Canoas 1 Ensino Fundamental 4

São Paulo 2 São Paulo 1 Ensino Médio 3

São Paulo 2 Santos 1 Ensino Profissionalizante 3

 

 

Note que o importante é apenas o conjunto de 2 (duas) colunas:

name_COLUNA e o total_COLUNA

 

com base nesses dados e com essa tabela em mãos, utilizo o resultado, transformando as colunas em Arrays no PHP e retirando os repetidos para transforma-los em o que quero na minha tela, onde os resultados ficariam assim:

 

 

Estados

Rio Grande do Sul (4)

São Paulo (2)

 

 

Cidades

Porto Alegre (2)

Novo Hamburgo (1)

Canoas (1)

São Paulo (1)

Santos (1)

 

 

Educação

Ensino Fundamental (4)

Ensino Médio (3)

Ensino Profissionalizante (3)

 

 

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

 

 

Para obter este resultado, adicionando ainda a tabela tb_estado_civil, criei a query abaixo:

SELECT

results_estado.id_estado ,

results_estado.name_estado ,

results_estado.total_estado ,

results_cidade.id_cidade ,

results_cidade.name_cidade ,

results_cidade.total_cidade ,

results_educacao.id_educacao ,

results_educacao.name_educacao ,

results_educacao.total_educacao ,

results_estado_civil.id_estado_civil ,

results_estado_civil.name_estado_civil ,

results_estado_civil.total_estado_civil

FROM tb_pessoa A

LEFT JOIN (

SELECT

COUNT(B.id_pessoa) as total_estado,

C.id_estado,

C.name_estado

FROM tb_pessoa B

LEFT JOIN tb_estado C USING( id_estado )

GROUP BY C.id_estado

) results_estado

USING ( id_estado )

LEFT JOIN (

SELECT

COUNT(D.id_pessoa) as total_cidade,

E.id_cidade,

E.name_cidade

FROM tb_pessoa D

LEFT JOIN tb_cidade E USING( id_cidade )

GROUP BY E.id_cidade

) results_cidade

USING ( id_cidade )

LEFT JOIN (

SELECT

COUNT(F.id_pessoa) as total_educacao,

G.id_educacao,

G.name_educacao

FROM tb_pessoa F

LEFT JOIN tb_educacao G USING( id_educacao )

GROUP BY G.id_educacao

) results_educacao

USING ( id_educacao )

LEFT JOIN (

SELECT

COUNT(H.id_pessoa) as total_estado_civil,

I.id_estado_civil,

I.name_estado_civil

FROM tb_pessoa H

LEFT JOIN tb_estado_civil I USING( id_estado_civil )

GROUP BY I.id_estado_civil

) results_estado_civil

USING ( id_estado_civil )

 

Obrigado pela ajuda de todos

[lokaodomau 61]

Mas não tem 6 ensino médio? 3 em Novo Hamburgo e 3 em São Paulo?

 

Se for o caso de trabalhar em PHP, acredito que a SQL que traz a contagem total agrupando todas as colunas seria mais útil. Pelo menos foi a forma que encontrei para trabalhar com mais de 1.500.000 linhas que o meu chefe havia solicitado.

[eveschuh 1]

Note a tabela Pessoas!!!!

Tem apenas 3 pessoas no Ensino Médio. A id_educacao do Ensino Médio é 2

tb_educacao

id_educacao name_educacao
2 Ensino Médio

Só tem uma pessoa na cidade de Novo Hamburgo. A id_cidade de Novo Hamburgo é 3

tb_cidade

id_cidade id_estado name_cidade

3 1 Novo Hamburgo

 

Tem apenas 2 pessoas no Estado de São Paulo. A id_estado de São Paulo é 3

tb_estado
id_estado name_estado
3 São Paulo

 

tb_pessoa
id_pessoa name_pessoa sx_pessoa id_estado id_cidade id_educacao id_estado_civil
1 Fulano 0 1 1 1 2
2 Beltrano 0 2 4 1 2
3 Ciclano 0 1 1 3 1
4 Fulana 1 3 7 2 1
5 Beltrana 1 3 8 3 1
6 Ciclana 1 2 6 3 2
7 Fulanino 0 2 5 2 2
8 Beltranino 0 2 6 2 3
9 Fulanina 1 1 2 1 2
10 Beltraninda 1 1 3 1 1

 

 

Então na minha tabela resposta, ele repete os valores falando sempre o total.

Por isso, eu transformo as colunas name_COLUNA e total_COLUNA da tabela de resposta em em um array. Para depois aplicar um ARRAY_UNIQUE e remover os repetidos, ficando apenas com o resultado que quero.