Clever Geek Handbook

How to optimize MySQL queries with constants?

NOTE: original question is controversial but crawls to the bottom for something meaningful.

I have a query that I want to optimize that looks something like this:

select cols from tbl where col = "some run time value" limit 1;

I want to know what keys are used, but all I pass to explain is it can optimize the where nothing clause ("Impossible WHERE noticed ...") because I gave it a constant.

  • Is there a way to tell mysql not to do persistent optimizations in the explanation?
  • Did I miss something?
  • Is there a better way to get the information I need?

Edit: It EXPLAIN

seems to give me a query plan that will be derived from constant values. Since the query is part of a stored procedure (and IIRC query plans in the files are created before they are called) this makes me bad because the value is not constant. I want to know what query plan the optimizer will generate if it doesn't know what the actual value will be.

Am I missing something?

Edit2: Asking the question elsewhere, it looks like MySQL always rebuilds query plans unless you go out of your way to reuse them. Even in stored procedures. From this it seems that my question is moot.

However, it's not doing what I really wanted to find controversial: . How do you optimize a query that contains values ​​that are constant in any particular query, but where I, the programmer, don't know in advance which value will be used? - For example, my client side code generates a request with a number in it where

. Several times this number leads to impossibility, when in other cases it will not. How can I use an explanation to check how optimized a query is?

The best approach I see from the start is to run EXPLAIN

on it for a complete matrix of existing / non-existing cases. In fact, this is not a very good solution, as it would be difficult and difficult to do it manually.

+1


source to share


3 answers


For example, my client side code generates a request with a number in it where the offer is.

Several times the number will lead to the impossible, if elsewhere it will not.

How can I use an explanation to check how optimized a query is?

MySQL

builds different query plans for different values ​​of related parameters.

In this article, you can read a list of when the optimizer MySQL

does what:

    Action When

    Query parse PREPARE
    Negation elimination PREPARE
    Subquery re-writes PREPARE

    Nested JOIN simplification First EXECUTE
    OUTER-> INNER JOIN conversions First EXECUTE

    Partition pruning Every EXECUTE
    COUNT / MIN / MAX elimination Every EXECUTE
    Constant subexpression removal Every EXECUTE
    Equality propagation Every EXECUTE
    Constant table detection Every EXECUTE
    ref access analysis Every EXECUTE
    range / index_merge analysis and optimization Every EXECUTE
    Join optimization Every EXECUTE

One more thing is missing from this list.

MySQL

can rebuild the query plan for each iteration JOIN

: a such as range checking for each record

.

If you have a pivot index on a table:

CREATE INDEX ix_table2_col1_col2 ON table2 (col1, col2)

and a request like this:

SELECT  *
FROM    table1 t1
JOIN    table2 t2
ON      t2.col1 = t1.value1
        AND t2.col2 BETWEEN t1.value2_lowerbound AND t2.value2_upperbound

MySQL

Will NOT use the index RANGE

from (t1.value1, t1.value2_lowerbound)

before (t1.value1, t1.value2_upperbound)

. Instead, it will use the access to the index REF

to (t1.value)

simply filter out invalid values.



But if you rewrite your query like this:

SELECT  *
FROM    table1 t1
JOIN    table2 t2
ON      t2.col1 <= t1.value1
        AND t2.col1 >= t2.value1
        AND t2.col2 BETWEEN t1.value2_lowerbound AND t2.value2_upperbound

then it MySQL

will check the index RANGE

for each record from table1

and decide whether to use access RANGE

on the fly.

You can read about it in these articles on your blog:

All this uses range checking for each record

Coming back to your question: it is impossible to determine which plan to use MySQL

for each given constant, since there is no plan before the constant.

Unfortunately, there is no way to force MySQL

a single query plan to be used for each associated parameter value.

You can control the order of JOIN

and INDEX

'selected with STRAIGHT_JOIN

and clauses FORCE INDEX

, but they will not force a specific index path or deny IMPOSSIBLE WHERE

.

On the other hand, everyone JOIN

's MySQL

only used NESTED LOOPS

. This means that if you create the correct JOIN

ordering or select the desired index, it MySQL

will probably benefit from everyone IMPOSSIBLE WHERE

.

+4


source


You get "Impossible WHERE noticed" because the value you specified is not in the column, not just because it is a constant. You can either 1) use the value that exists in the column, or 2) just say col = col

:



explain select cols from tbl where col = col;
+5


source


How do you optimize a query with values ​​that are constant for the query only, but where I, the programmer, don't know in advance what value will be used?

Using indexes on specific columns (or even on a combination of columns if you always query the given columns together). If you have indexes, the query planner will potentially use them.

As for the "impossible" values, the query planner might conclude that the given value is missing from the table from multiple sources:

  • if there is a pointer to a specific column, it can notice that the specific value is large or less than any value in the index (min / max values ​​take constant time to retrieve from indices)
  • if you are using the wrong type (if you are asking for a numeric column to be equal to text)

PS. In general, creating a query plan is not expensive and is better to recreate than reuse, since conditions can change since the query plan is created and a better query plan can arise.

0


source






More articles:


All Articles