Can you help me optimize this SQL query?

Asked

Viewed 89 times

-1

I am having a problem with this sql query. Its return time is on average 10 seconds and ta using 100% cpu. Probably because it uses many subquery. However I’m not able to optimize it. Does anyone have any idea which way I can go ?

SELECT id_post, midia, texto, tb_usuario.id, tb_usuario.nome, user, avatar, tipo, tb_comm_post.id_categoria, tb_comm_post.data_criacao, tb_comm_categorias.nome, cor1, cor2,
    (SELECT COUNT(*) FROM tb_liked_comunidade WHERE tb_liked_comunidade.id_post = tb_comm_post.id_post AND flag = 1) as n_like,
    (SELECT COUNT(*) FROM tb_liked_comunidade WHERE tb_liked_comunidade.id_post = tb_comm_post.id_post AND flag = -1) as n_dislike,
    (SELECT flag FROM tb_liked_comunidade WHERE tb_liked_comunidade.id_usuario = ? AND tb_liked_comunidade.id_post = tb_comm_post.id_post) as flag,
    (SELECT COUNT(*) FROM tb_comentario_comunidade WHERE tb_comentario_comunidade.id_post = tb_comm_post.id_post) as n_comentarios
    FROM tb_comm_post
    JOIN tb_usuario ON tb_usuario.id = tb_comm_post.id_autor
    JOIN tb_comm_categorias ON tb_comm_post.id_categoria = tb_comm_categorias.id_categoria
    WHERE tb_comm_post.exibir = 1
    ORDER BY tb_comm_post.data_criacao DESC
    LIMIT 5 OFFSET ?

Where ta the question mark "?" is that the data comes when executing the query

mysql sql query optimizing
  • 1

    Try to put the data in a temporary table, right after you apply the sub-consultations.

  • Makes Join with tb_liked_community in place of subselect and deals with iif or case (pivot) , Join with tb_comment community as well.

  • Run a EXPLAIN and analyze the execution plan by identifying the bottlenecks.

  • Try to do it without the Ubqueries and go back and forth one by one to see which one is picking up. Remembering that optimization of SQL query is an NP-complete problem, so every SQL server ends up "choking" with some queries, even if the optimization is obvious to a human, and ends up having to do the sub-consultations as other queries in client-side code.

  • The answers even give ideas of how to improve, but other problems of slowness can be caused by poorly planned modeling, doing anyway many things can fail, in this appear accusations (not that I am praising) mysql is slower q other banks, when in fact many problems are from a bad planning of the structure. I’m only commenting to complement, because you may even have obtained a "solution" in the answers, but there are things that are not enough a select "efficient"

2 answers

1

The reason for the slowness is that for each row of your select you are running 4 selects. Imagine you run 4 subselect times 10thousand lines...

I would create a trial, or include these steps in the existing trial:

...

DECLARE

a NUMBER;
b NUMBER;
c NUMBER;
d NUMBER;

BEGIN

a:= (SELECT COUNT(*) FROM tb_liked_comunidade WHERE tb_liked_comunidade.id_post = tb_comm_post.id_post AND flag = 1) as n_like,
b:= (SELECT COUNT(*) FROM tb_liked_comunidade WHERE tb_liked_comunidade.id_post = tb_comm_post.id_post AND flag = -1) as n_dislike,
c:= (SELECT flag FROM tb_liked_comunidade WHERE tb_liked_comunidade.id_usuario = ? AND tb_liked_comunidade.id_post = tb_comm_post.id_post) as flag,
d:= (SELECT COUNT(*) FROM tb_comentario_comunidade WHERE tb_comentario_comunidade.id_post = tb_comm_post.id_post) as n_comentarios

SELECT 
a AS n_like,
b AS n_dislike,
c AS flag,
d AS n_comentarios,
id_post, midia, texto, tb_usuario.id, tb_usuario.nome, user, avatar, tipo, 
tb_comm_post.id_categoria, tb_comm_post.data_criacao, tb_comm_categorias.nome, cor1, cor2

FROM tb_comm_post

INNER JOIN tb_usuario 
    ON tb_usuario.id = tb_comm_post.id_autor

INNER JOIN tb_comm_categorias 
    ON tb_comm_post.id_categoria = tb_comm_categorias.id_categoria

WHERE tb_comm_post.exibir = 1

ORDER BY tb_comm_post.data_criacao DESC

LIMIT 5 OFFSET ?

END

...

It is possible to solve this problem also with temporary table and cross stating that 1 is equal to 1 (ON 1=1) to repeat all data from one table with the other.

0

Complementing the reply of @someone.

When we talk about optimization we need to understand the query time that each table has, because each table has a certain amount of real data and we can measure how much it can grow in the future. In turn, not only a well done query can have a positive effect. The optimization solution may be different depending on the environment you find yourself in today, because perhaps the creation of new relational tables may have a better effect.

A good solution, that I have seen in some projects is to create index in the tables and use indexex in select, I believe that the query time will be much more efficient.

Example:

SELECT CAMPO
FROM TABELA T WITH (INDEX(INDEX_NOMETABELAINDEXADA))
INNER JOIN OUTRA_TABELA OT
WITH (INDEX(OUTRO_INDICE))
ON OT.ID = T.ID

Browser other questions tagged mysql sql query optimizing

You are not signed in. Login or sign up in order to post.