Select inside the Where leaving the query too slow?

Asked

Viewed 82 times

1

I have the following structure:

Tabelas relacionadas na consulta

And my query is that way

SELECT tbl_nfes.*, count(tbl_nfe_itens.pedido > 0) as v, tbl_nfe_itens.status FROM 
tbl_nfes
INNER JOIN tbl_nfe_itens ON tbl_nfes.nfe = tbl_nfe_itens.nfe
INNER JOIN tbl_users ON tbl_nfes.id_user = tbl_users.id
WHERE (SELECT COUNT(*) FROM tbl_nfe_itens WHERE position = 0 AND nfe = tbl_nfe_itens.nfe ) = 0
GROUP BY tbl_nfes.nfe ORDER BY tbl_nfes.nfe ASC

Removing select inside Where the query does not delay in showing the result, but loads data that are not accepted, could only return the Nfes that all items have position = 0. What I must do to make this consultation more effective?

mysql query select

  • The standard that this way it brings the result I wish that eh show only the nfs where all your items have position = 0 I’ve tried to put where tbl_nfe_itens.position = 0 and it carries the nfs that have at least one item with position = 0, has another way to load only if all items have position = 0?

  • Sorry missed a part of Where already edited, missed `AND nfe = tbl_nfe_items.nfe

  • Yes it was time to transcribe the query, but I’ve already put the ,

  • 2

    Execute that query to create an Index and test your query again CREATE INDEX tbl_nfe_itens_nfe ON tbl_nfe_itens(nfe);

  • Perfect worked great, I’m trying to find some material for me to understand how the indices work. thank you very much.

  • Show @Sorak thanks for the availability and if you can post the answer will help many people and I could understand better.

  • 2

    Beware of subquery in Where. Note that the subquery will be executed for each line found. Imagine the impact on a search with a source of millions of records. Creating index is an outline that can relieve you momentarily. Use sparingly.

Show 2 more comments

2 answers

1

Run the query below:

SELECT tbl_nfes.*,
       count(tbl_nfe_itens.pedido > 0) AS v,
       tbl_nfe_itens.status
FROM tbl_nfes
    INNER JOIN tbl_nfe_itens ON tbl_nfes.nfe = tbl_nfe_itens.nfe
    INNER JOIN tbl_users ON tbl_nfes.id_user = tbl_users.id
GROUP BY tbl_nfes.nfe
HAVING COUNT(case when tbl_nfe_itens.POSITION =0 then 1 end )=0
ORDER BY tbl_nfes.nfe ASC

0

Your WHERE is strange, see that it compares a COUNT(*) number with another number (?) and may be the reason why it slows down the query.. I believe he should be making a comparison with the value of some column.

WHERE (SELECT COUNT(*) FROM tbl_nfe_itens WHERE position = 0 ) = 0

Browser other questions tagged mysql query select

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