distal-attribute
distal-attribute
distal-attribute
distal-attribute

[HELP] Optimize SQL Query

gustavocassoto posted 1 year ago in Running SQL scripts
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
gustavocassoto posted 1 year ago
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 posted 1 year ago

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

gustavocassoto posted 1 year ago

ansgar wrote:

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. :)
gustavocassoto posted 1 year ago
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 :)

kerry posted 1 year ago
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.

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