what's the idea of this mdev?
the second row, table 'a', have many possible_keys, but we don't know why optimizer selected the item index
what i do is : explain many times, forcing each index each explain... for example
1) force index (item)
2) force index (transferencias)
3) force index (rendimento)
4) force index (giro)
to get each explain
i don't know if optimizer have this values, but probably yes since it select the best index before returning explain results
my idea is include one new column with a dynamic output (json or dynamic column)
this will include all possible values from each index, for example:
the only 'information' that explain MDEV-7125 is:
with "rendimento" index: => rows = 226330
with "item" index: => 49 rows (from b table) * 3347 rows (from a table) = 164003
i don't know the optimizer choise, but what i see is 164003 < 226330, maybe this explain the optimizer choise?
reading mdev-6109 i see others optimizer structures, maybe should wait mdev-6109 before check this mdev
roberto spadim
added a comment - the only 'information' that explain MDEV-7125 is:
with "rendimento" index: => rows = 226330
with "item" index: => 49 rows (from b table) * 3347 rows (from a table) = 164003
i don't know the optimizer choise, but what i see is 164003 < 226330, maybe this explain the optimizer choise?
reading mdev-6109 i see others optimizer structures, maybe should wait mdev-6109 before check this mdev
MDEV-6109 (EXPLAIN JSON) will provide more information about the query plan that was used. It will not provide any information about query plans (or indexes) that were not used.
MySQL 5.6+ has optimizer_trace. The goal of optimizer_trace was exactly this - to provide clues how the optimizer processed the query - what options were considered, and why some of them were discarded. We haven't backported optimizer_trace to MariaDB because some of MariaDB had concerns about whether optimizer_trace is a finished work (it has certain issues). Recently we've been kind of leaning towards backporting optimizer trace in its current form.
At the moment, I don't see any other solution to this problem except optimizer trace.
Sergei Petrunia
added a comment - MDEV-6109 (EXPLAIN JSON) will provide more information about the query plan that was used. It will not provide any information about query plans (or indexes) that were not used.
MySQL 5.6+ has optimizer_trace. The goal of optimizer_trace was exactly this - to provide clues how the optimizer processed the query - what options were considered, and why some of them were discarded. We haven't backported optimizer_trace to MariaDB because some of MariaDB had concerns about whether optimizer_trace is a finished work (it has certain issues). Recently we've been kind of leaning towards backporting optimizer trace in its current form.
At the moment, I don't see any other solution to this problem except optimizer trace.
Well, there is also the traditional manual way: try the query with IGNORE INDEX for the index that was used and see what explain plan you will get.
Sergei Petrunia
added a comment - Well, there is also the traditional manual way: try the query with IGNORE INDEX for the index that was used and see what explain plan you will get.
nice
—
1) ok i will check optimizer trace from mysql. yeap i use the IGNORE INDEX too, that's useful to block some index, i use it sometimes when optimizer don't help me, but i prefer an 'automatic' way no problem the hardcoded works, i just reported this MDEV cause i don't know if it's relevant or not
2) if you consider this mdev duplicated of optimizer trace (MDEV-6111), no problem please close it i removed the block by explain json, and marked as blocked by optimizer trace
roberto spadim
added a comment - nice
—
1) ok i will check optimizer trace from mysql. yeap i use the IGNORE INDEX too, that's useful to block some index, i use it sometimes when optimizer don't help me, but i prefer an 'automatic' way no problem the hardcoded works, i just reported this MDEV cause i don't know if it's relevant or not
2) if you consider this mdev duplicated of optimizer trace ( MDEV-6111 ), no problem please close it i removed the block by explain json, and marked as blocked by optimizer trace
Task
Minor
the only 'information' that explain
MDEV-7125is:with "rendimento" index: => rows = 226330
with "item" index: => 49 rows (from b table) * 3347 rows (from a table) = 164003
i don't know the optimizer choise, but what i see is 164003 < 226330, maybe this explain the optimizer choise?
reading mdev-6109 i see others optimizer structures, maybe should wait mdev-6109 before check this mdev