In the condition filtering feature that MySQL introduced from version 5.7, has filter estimates for various condition formats in the join order, to estimate the number of rows that enter the join order pipe line after joins.

Assume a query joining three tables A, B and C, and the criteria is A.col_a > B.col_b and C.col_c > 10. If the join order for this query is A->B->C then, the query plan, attaches the heuristic guesstimate of 33.33 percent after both A and B are in the plan at the stage of A->B.

But if a query joining the same three tables A, B and C, had criteria such that (A.col_a = 3 or B.col_b = 17) and C.col_c > 10, the combined criteria (A.col_a = 3 or B.col_b = 17) is added to the attached conditions at the stage in join order when A->B but the filter condition percent is just 100. I suspected it to be following the probability theory and a filtering percentage of 55.55.

P(A.col_a = 3 or B.col_b = 17) = P(A.col_a = 3) + P(B.col_b = 17) - P(A.col_a = 3) * P(B.col_b = 17)
P(A.col_a = 3 or B.col_b = 17) = 0.3333 + 0.3333 - 0.1111
P(A.col_a = 3 or B.col_b = 17) = 0.5555

Why is this not applied in the planning? I see the documentation says:

A condition contributes to the filtering estimate only if:

  • It refers to the current table.

  • It depends on a constant value or values from earlier tables in the join sequence.

  • It was not already taken into account by the access method.

Why have they not included criteria that also refers to tables that are already in the join order also? Why is the case A.col_a op B.col_b case included but not A.col_a op val or B.col_b op val not included in the rows estimation method?

  • The bullets say "only if"--not "if"--so the conditions are said to be necessary, but they are not said to be sufficient. So you can't expect anything from the bullets except that if conditions were used, they have those properties – philipxy May 26 '20 at 4:39
  • I don't mean to say there is incongruity between software and its documentation, I understand what they mean. I am trying to open a discussion to know if there is any reason for it is currently such that, a condition referring to table already in the plan's join order cannot contribute to condition filtering? – Arun S R May 26 '20 at 4:53
  • Please clarify via edits, not comments. This site is for Q&A, not "discussion". You asked "why is this not applied". Why should it? I just showed that the bullets you give don't support your expectations, giving them doesn't make sense. But anyway: What makes you think an estimate is independent of the details of the comparison & the context, including statistics? The description of heuristics, including estimate calculation & usage, is so vague & general that there is nothing specific to expect in any particular case. – philipxy May 26 '20 at 5:12
  • If you want to ask about a particular case, you should give a proper description of it (eg a minimal reproducible example), including statistics, EXPLAIN output & DDL, including constraints, indexes, statistics & EXPLAIN output. PS What did your research show? dev.mysql.com/doc/internals/en/optimizer.html dba.stackexchange.com/q/42602/43932 dba.stackexchange.com/q/208452/43932 etc – philipxy May 26 '20 at 5:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.