[Resolvido] Otimização de Consulta

[Vitor Justin_116799 0]

Pessoal,

 

Queria saber se tem alguma forma de otimizar uma consulta que eu formulei, pois achei ela muito extensa e creio que existem formas mais otimizadas de chegar no mesmo resultado.

 

Primeiro vou explicar um pouco sobre o sistema que estou trabalhando. Se trata de um cadastro de desfiles, onde cada desfile terá varios aplausos. Estes aplausos podem ser feitos da seguinte forma: por facebook, por twitter ou se a pessoa já estiver cadastrada no fã-clube do cliente (e-mail). Quando alguém "aplaude" o desfile, é gerado um registro.

 

Estrutura do banco de dados

 

Tabela "desfile":
id
created   - Data de criação do registro
updated   - Data de atualização do registro
nome      - Nome do desfile
permalink - Identificador único criado a partir do nome
status    - Status de aprovação do desfile

Tabela "desfile_aplauso":
id
created       - Data de criação do registro
tipo          - Tipo do aplauso (facebook | twitter | fa-clube)
identificador - Identificador do tipo do aplauso (id da rede social ou e-mail do fã-clube)
desfile_id    - Id do desfile "aplaudido"

 

O que eu preciso é pegar os 5 desfiles mais aplaudidos, sendo que cada registro de aplauso com o tipo "fa-clube" vale 4 aplausos e os outros dois tipos (facebook e twitter) valem 1. O critério de desempate no caso de existirem desfiles com o mesmo número de aplausos, é a data de criação do desfile, sendo que o mais novo (criado por último) ganha.

 

Eu consegui chegar ao resultado esperado usando a seguinte consulta:

 

SELECT
da.desfile_id desfileid,
d.created as data,
d.nome,
(
	SELECT COUNT(id) FROM desfile_aplauso WHERE tipo = 'facebook' AND desfile_id = desfileid
) as 'totalfacebook',
(
	SELECT COUNT(id) FROM desfile_aplauso WHERE tipo = 'twitter' AND desfile_id = desfileid
) as 'totaltwitter',
(
	SELECT COUNT(id)*4 FROM desfile_aplauso WHERE tipo = 'fa-clube' AND desfile_id = desfileid
) as 'totalfaclube',
(
	SELECT SUM(totalfacebook+totaltwitter+totalfaclube)
) as 'total'
FROM
desfile d,
desfile_aplauso da
WHERE
d.id = da.desfile_id
GROUP BY
da.desfile_id
ORDER BY
total DESC,
data DESC
LIMIT
0,5

 

Então novamente pergunto a vocês, existe alguma maneira mais otimizada de chegar ao resultado esperado?

 

Abraços, agradeço desde já.

[Motta 645]

pq não

 

SELECT tipo , COUNT(id) 
FROM desfile_aplauso 
where tipo in ( 'facebook' ,'twitter','fa-clube')
union all
SELECT 'total', COUNT(id) 
FROM desfile_aplauso 
where tipo in ( 'facebook' ,'twitter','fa-clube')

[Vitor Justin_116799 0]

pq não

 

SELECT tipo , COUNT(id) 
FROM desfile_aplauso 
where tipo in ( 'facebook' ,'twitter','fa-clube')
union all
SELECT 'total', COUNT(id) 
FROM desfile_aplauso 
where tipo in ( 'facebook' ,'twitter','fa-clube')

 

Motta,

 

No meu caso, eu preciso dos resultados para cada desfile, e também quando o tipo for "fa-clube" o registro conta como 4 "aplausos", entende?

[Motta 645]

Desculpe, não entendi.

 

O critério de desempate no caso de existirem desfiles com o mesmo número de aplausos, é a data de criação do desfile, sendo que o mais novo (criado por último) ganha.

 

SELECT tipo , data , COUNT(id) 
FROM desfile_aplauso 
where tipo in ( 'facebook' ,'twitter','fa-clube')
group by tipo , data
order by 3 desc,2 desc

 

Rode a query acima direto no BD (ajuste o nome do campo de data) e veja se lhe atende.

[giesta 29]

Select * from desfile d,

(select id_desfile, sum(if(tipo='fa-clube',4,1))aplausos from desfile_aplauso group by id_desfile )z

where d.id = z.id_desfile

order by z.aplauso desc , d.created desc

limit 5

[Motta 645]

Eu não estou certo de ter entendido...

[giesta 29]

Motta, nao estava me referindo a você, mas de qualquer forma, editei meu post. :thumbsup:

[Motta 645]

Motta, nao estava me referindo a você, mas de qualquer forma, editei meu post. :thumbsup:

 

Não achei que estivesse, só comentei que realmente não entendi o problema do Vitor Justin_116799.

 

[]´s

[giesta 29]

O problema dele (pelo que eu entendi) eh simples.

 

Cada voto conta 1 ponto, se o voto for de fa-clube conta 4 pontos,

 

depois você soma os pontos todos, os 5 com mais pontos ganham. Se empatar o mais recente vem primeiro (criterio de desempate).

[Bruno Augusto 417]

Olha, não sou muito bom com bancos de dados, mas acho que resolvi seu problema.

 

Como não havia uma estrutura postada, eu criei uma separando as informações em três tabelas: clappers com as origens dos votos (Twitter, Facebook e E-mail) e seus respectivos pesos, parades com os dados dos desfiles e applauses com os registros dos aplausos recebidos, para qual evento de qual origem.

 

Simplifiquei a estrutura ao máximo, logo não inclui todos os campos das suas queries.

 

Tal estrutura ficou assim:

 

 

-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
--
-- Servidor: localhost
-- Tempo de Geração: 21/04/2012 às 16h23min
-- Versão do Servidor: 5.5.16
-- Versão do PHP: 5.3.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Banco de Dados: `imasters`
--

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

--
-- Estrutura da tabela `applauses`
--

CREATE TABLE IF NOT EXISTS `applauses` (
 `aid` int(5) NOT NULL AUTO_INCREMENT,
 `cid` int(5) NOT NULL,
 `pid` int(5) NOT NULL,
 `date` date NOT NULL,
 PRIMARY KEY (`aid`),
 KEY `pid` (`pid`),
 KEY `cid` (`cid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;

--
-- Extraindo dados da tabela `applauses`
--

INSERT INTO `applauses` (`aid`, `cid`, `pid`, `date`) VALUES
(1, 1, 1, '2012-04-21'),
(2, 1, 2, '2012-04-21'),
(3, 2, 1, '2012-04-21'),
(4, 2, 1, '2012-04-21'),
(5, 3, 1, '2012-04-21'),
(6, 3, 1, '2012-04-21'),
(7, 3, 1, '2012-04-21'),
(8, 3, 1, '2012-04-21'),
(9, 2, 2, '2012-04-21'),
(10, 2, 2, '2012-04-21'),
(11, 1, 2, '2012-04-21'),
(12, 1, 2, '2012-04-21'),
(13, 1, 2, '2012-04-21'),
(14, 1, 2, '2012-04-21'),
(15, 3, 2, '2012-04-21'),
(16, 3, 2, '2012-04-21');

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

--
-- Estrutura da tabela `clappers`
--

CREATE TABLE IF NOT EXISTS `clappers` (
 `cid` int(5) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `weight` char(1) NOT NULL,
 PRIMARY KEY (`cid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Extraindo dados da tabela `clappers`
--

INSERT INTO `clappers` (`cid`, `name`, `weight`) VALUES
(1, 'Twitter', '1'),
(2, 'Facebook', '1'),
(3, 'E-mail', '4');

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

--
-- Estrutura da tabela `parades`
--

CREATE TABLE IF NOT EXISTS `parades` (
 `pid` int(5) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`pid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Extraindo dados da tabela `parades`
--

INSERT INTO `parades` (`pid`, `name`) VALUES
(1, 'July, 4th'),
(2, 'Christmas Parade');

--
-- Restrições para as tabelas dumpadas
--

--
-- Restrições para a tabela `applauses`
--
ALTER TABLE `applauses`
 ADD CONSTRAINT `applauses_ibfk_2` FOREIGN KEY (`pid`) REFERENCES `parades` (`pid`) ON DELETE CASCADE ON UPDATE NO ACTION,
 ADD CONSTRAINT `applauses_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `clappers` (`cid`) ON DELETE CASCADE ON UPDATE NO ACTION;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

E a query que lista os resultados:

 

SELECT c.name as origin, p.name as parade, COUNT(a.cid) as applauses, COUNT(a.cid) * c.weight as total
   FROM `applauses` a
       LEFT JOIN `clappers` c ON( c.cid = a.cid )
           LEFT JOIN `parades` p ON ( p.pid = a.pid )
               GROUP BY c.cid, p.name
                   ORDER BY p.name ASC

Eu optei por ordenar pelo nome do desfile, mas no seu caso a ordenação obviamente será outra.

 

àqueles que entendem melhor do assunto eu agradeço qualquer comentário construtivo, afinal não sou especialista.

[Vitor Justin_116799 0]

Select * from desfile d,

(select id_desfile, sum(if(tipo='fa-clube',4,1))aplausos from desfile_aplauso group by id_desfile )z

where d.id = z.id_desfile

order by z.aplauso desc , d.created desc

limit 5

 

giesta,

 

Funcionou perfeitamente!

 

Valeu pela ajuda pessoal :)