[HELP] Optimize SQL Query

[expired user #7450]'s profile image [expired user #7450] posted 11 years ago in Running SQL scripts Permalink
Hy Guys! All good?

Well, I have a problem with one query that i've been doing lately..
I have a table called blog this table has 1.800 registers on this table, then I have other table called blog_coments and this table has 36.000 registers.

What I need is, a list which show all theses posts of blog's table and sum all the comments of each post!

I`ve done a query, but it took so looong to show the result, about 40 seconds. Ill post here the query, if someone could help me, ill be really thanks full!!

Here is the Query: (ps: I commented lines because it is in portuguesesmile)

SELECT
// the blog id
`blog`.`NR_SEQ_BLOG_BLRC`,
//the photo
`blog`.`DS_EXT_BLRC`,
//the title
`blog`.`DS_TITULO_BLRC`,
//the date
`blog`.`DT_PUBLICACAO_BLRC`,
//the id of who posted
`blog`.`NR_SEQ_COLUNISTA_BLRC`,
//the short description
`blog`.`DS_TEXTO_BLRC`,
//the sum of comments
(SELECT
COUNT(NR_SEQ_COMENTARIO_CBRC) AS total_comentatios
FROM
blog_coments
WHERE
NR_SEQ_BLOG_BLRC = NR_SEQ_BLOG_CBRC) AS `total_comentarios`,
//the name of who posted
`colunistas`.`DS_COLUNISTA_CORC`
FROM
`blog`
INNER JOIN
`colunistas` ON blog.NR_SEQ_COLUNISTA_BLRC = colunistas.NR_SEQ_COLUNISTA_CORC
WHERE
// i need only active postes
(blog.DS_STATUS_BLRC = 'A')
GROUP BY `NR_SEQ_BLOG_BLRC`
//order by date
ORDER BY `DT_PUBLICACAO_BLRC` DESC
[expired user #7450]'s profile image [expired user #7450] posted 11 years ago Permalink
here is the query withou comments easier to checksmile

SELECT
`blog`.`NR_SEQ_BLOG_BLRC`,
`blog`.`DS_EXT_BLRC`,
`blog`.`DS_TITULO_BLRC`,
`blog`.`DT_PUBLICACAO_BLRC`,
`blog`.`NR_SEQ_CATEGORIA_BLRC`,
`blog`.`NR_SEQ_COLUNISTA_BLRC`,
`blog`.`DS_TEXTO_BLRC`,
(SELECT
COUNT(NR_SEQ_COMENTARIO_CBRC) AS total_comentatios
FROM
blog_coments
WHERE
NR_SEQ_BLOG_BLRC = NR_SEQ_BLOG_CBRC) AS `total_comentarios`,
`colunistas`.`DS_COLUNISTA_CORC`
FROM
`blog`
INNER JOIN
`colunistas` ON blog.NR_SEQ_COLUNISTA_BLRC = colunistas.NR_SEQ_COLUNISTA_CORC
WHERE
(blog.DS_STATUS_BLRC = 'A')
GROUP BY `NR_SEQ_BLOG_BLRC`
ORDER BY `DT_PUBLICACAO_BLRC` DESC
ansgar's profile image ansgar posted 11 years ago Permalink
SELECT b.*, COUNT(*) AS num_comments
FROM blog AS b
LEFT JOIN blog_comments AS c ON b.NR_SEQ_COLUNISTA_BLRC = c.NR_SEQ_COLUNISTA_CORC
GROUP BY b.NR_SEQ_COLUNISTA_BLRC
[expired user #7450]'s profile image [expired user #7450] posted 11 years ago Permalink

SELECT b.*, COUNT(*) AS num_comments
FROM blog AS b
LEFT JOIN blog_comments AS c ON b.NR_SEQ_COLUNISTA_BLRC = c.NR_SEQ_COLUNISTA_CORC
GROUP BY b.NR_SEQ_COLUNISTA_BLRC




Thanks for reply ansgar!!
But I don't think this will help me, here is the link that I'm doing http://dev.reverbcity.com/blog/page still not having style so, don't worry about layout, but at the first time it can take long to load, or even worst, excess the server time limit, so please refresh the page if you have some problem. :)
[expired user #7450]'s profile image [expired user #7450] posted 11 years ago Permalink
Ok, I'm Done!! :D
Here is the problem :

Who did the database, did not use relation between the tables, now my query is taking only 3 seconds :)

Special tks to ansgar :)
[expired user #7454]'s profile image [expired user #7454] posted 11 years ago Permalink
hey I got it
I can read tables now in tis apps window with the select thing
here is a real very primary top web padge for sql
if I can learn anyone can
http://www.w3schools.com/sql/sql_select.asp

its 3c school I learned to insert create and read and I started
with MySQL only a week ago. try that padge.
Nitic Jain's profile image Nitic Jain posted 4 years ago Permalink

Great Discussion. I am an engineer. Thanks for this forum discussion, I have found answer of my query at last. I also write about engineering stuff for a education website www.cleariitmedical.com Discussions like these help lot of seekers like me. Thanks Again!

Please login to leave a reply, or register at first.