CODEWITHSUNDEEP
mysqlindexing
OneChillDude
OneChillDude

Reputation: 8006

Configure better index choices for MySQL

I have a MySQL table with ~17M rows where I end up doing a lot of aggregation queries.

For this example lets say I have index_on_b, index_on_c, compound_index_on_a_b, compound_index_on_a_c

I try and run a query explain

EXPLAIN SELECT SUM(revenue) FROM table WHERE a = some_value AND b = other_value

And I find that the selected index is index_on_b, but when I use a query hint

SELECT SUM(revenue) FROM table USE INDEX(compound_index_on_a_b)

The query runs way way faster. Is there anything I can do in MySQL config to make MySQL choose the compound indexes first?

Upvotes: 3

Views: 101

Answers (2)

Rick James
Rick James

Reputation: 142298

A possible explanation:

If a has the same value throughout the table, then INDEX(b) is actually better than INDEX(a,b). This is because the former is smaller, hence faster to work with. Note that both will return the same number of rows, even without further checking of a.

Please provide:

SHOW CREATE TABLE
SHOW INDEXES  -- to see cardinality
EXPLAIN SELECT

Upvotes: 0

Norbert
Norbert

Reputation: 6084

There are 2 possible routes you can take:

A) The index resolution process is when according to the optimizer all things are equal based on the order the indexes are created in. You could drop index_b and recreate it and check if the optimizer was in a scenario where it just thought they were the same.

Or

B) Use optimizer_search_depth (see https://mariadb.com/blog/setting-optimizer-search-depth-mysql). By altering this parameter you determine how much effort the optimizer is allowed to spend on a query plan, and it might come up with the much better solution of using the combined index.

Upvotes: 1

Related Questions