Optimizer choose incorrect index for query according to rows number in mysql 5.6 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.
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.