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.