What is the weight of a subquery for the query?

Asked

Viewed 391 times

4

During the development of some queries in the database, I come across the situation of the need to perform a new query, but another solution in some cases may be the subquery.

So what is the best solution to perform a second query or use subquery in situations like this? What is the weight of a subquery for my query? Valid to the point where you don’t need to make a new bank query?

mysql database select-sql
  • 1

    Depending on the situation, it depends on how the subquery is built. I don’t know if there is any tool for searching strategy analysis with mysql... Either way, a subquery is a new query, and according to the form used, may generate one or more queries to the bank for each record of query leading.

  • 2

    It also depends on the existence of indexes, updated statistics etc. In some cases the use of an "Outer Join" is faster.

  • All indexed, in most of these situations Outer Join does not solve the situation, usually there is a need for subquery or second query.

  • 1

    In some cases a "denormalization" can help , imagine the following scenario client x debit balance , in theory it would take a subquery or Join to know this, but if a column in client is updated by triggers the weight of this query falls because just access customer, the issue is to weigh the cost of triggers x cost of consultations.

  • 1

    I try my best not to subquery by the performative factor and learned to make SQL optimized that bring the same result and faster. Everything will depend on how your bank is for me being the crucial point (lack of indices, tables without relationships, banks without normalization, etc.).

  • 1

    The exact answer you’ll only get by giving a EXPLAIN in the query and analyzing the result. It depends a lot on the case.

Show 1 more comment

1 answer

1

Between getting the result through a subquery or performing a second query, the subquery under normal conditions will be less costly because it does not duplicate the execution of the communication algorithms between the client and the server. However, it is necessary to consider that for each line of the main query, the subquery will be executed; therefore, you need to weigh whether you would actually do the same amount of queries alone.

The actual weight of a subquery depends on the algorithms of your DBMS and the modeling of your Database. This is an impossible answer to give without analyzing its structure.

My final advice is that, if possible, replace the subquerys with Joins. This will optimize performance and, in my experience, this is almost always possible (and don’t forget the indexes).

Browser other questions tagged mysql database select-sql

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