I have mysql 5.6. The problematic query has the following format:

SELECT field_1, field_2
  FROM trace_ejb3
 WHERE field_1 IN (...) AND field_2 BETWEEN ... AND ...
 GROUP BY field_1;

Optimizer calculates the cost for each execution plans and eventually choosees incorrect index to use. From what I understand the main reason - number of returned rows. However in fact I need to use another index. The difference in execution is 2 days vs 1 hours.

I have tried to play with the following options: - innodb_stats_persistent + sample_pages - optimizer_switch - optimizer_search_depth - optimizer_prune_level

However none of the about options influence the results : optimizer still choose execution plan according to the least number of returned rows.

Question: is there a way to force optimizer not take into account rows number?

P.S. I know about index hints, but I don`t consider it like a long-term solution.

  • 1
    Start small, post the create table statement and the explain output and the result of show indexes from table_name. – tombom May 19 '17 at 9:18
  • @tombom thank you for the comment. I am rather looking for some common practices how to deal with optimizer in such cases... – user105715 May 19 '17 at 9:45

In general, the only way to force the optimizer to take a specific path is to add code (like index hints, as you mention) to explicitly tell the optimizer what it should (or shouldn't) use.

Beyond that, one generally has to look at the specific query and query plan, the information the optimizer has, and the reason why it would have chosen the path it did. In most cases, the optimizer chooses a sub-optimal path because either its information is bad (missing or out-of-date statistics), or because of a cached plan that worked well when cached, but not well with the current parameters. Of course, sometimes the best path available is still bad.

In these cases, you can look at the query plan, see where the query was having the most problems, and figure out a way to give the optimizer better options. This can be creating a covering index; adding or updating statistics (assuming that can be done under MySQL); breaking the query into multiple steps on which the optimizer can make accurate estimates, etc.

| improve this answer | |

There is no 'general' solution. The tweaks mentioned so far (plus others) are each individually useful sometimes.

The specific query may benefit from the composite INDEX(field1, field2)...

If the Optimizer is smart enough to jump from field1 to field1 to do the range on field2 (older versions of MySQL were not), then it might use that index to good advantage. (5.5 may have been missing such smarts; 5.7 may have more smarts; I lose track of the details.)

Having field1 first in the index means that it will probably consume the GROUP BY in the process of filtering.

Note that nothing I have said so far really depends on cardinality or other "statistics". However, also having INDEX(field2) may lead the Optimizer astray into to making the "wrong" decision.

Keep in mind that "ignoring all indexes" may be the fastest way to perform the query. For example, if field1 IN... does not filter out much, then INDEX(field1) is probably a waste of time. However, (field1, field2) may still be a viable index.

Oh. Your particular query will not 'work' in newer versions. See only_full_group_by -- You should not group by field1 without also doing field2. So, I have spent some time on analysis that is possibly useless for your underlying question. Please fix the Question; then I will continue.

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.