[MDEV-7239] explain index choice Created: 2014-12-01  Updated: 2019-06-14  Resolved: 2019-06-14

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: N/A

Type: Task Priority: Minor
Reporter: roberto spadim Assignee: Sergei Petrunia
Resolution: Duplicate Votes: 1
Labels: None

Issue Links:
Blocks
is blocked by MDEV-6111 optimizer trace Closed

 Description   

Hi guys, i openned this MDEV from MDEV-7125 problem...

+------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+
 
| id   | select_type | table | type  | possible_keys                       | key      | key_len | ref                             | rows | filtered | Extra                                                        |
 
+------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+
 
|    1 | SIMPLE      | b     | index | NULL                                | id       | 16      | NULL                            |    2 |   100.00 | Using index                                                  |
 
|    1 | SIMPLE      | a     | ref   | item,transferencias,rendimento,giro | item     | 6       | const,test.b.plano_conta_id_red | 3318 |   100.00 | Using where                                                  |
 
|    1 | SIMPLE      | c     | index | NULL                                | NewIndex | 16      | NULL                            |    2 |   100.00 | Using where; Using index; Using join buffer (flat, BNL join) |
 
+------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+

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:

{
 
  'item':{'key_len':1234, 'ref rows':'blablalba', 'filtered'  99, 'extra':'blabla'},
 
  'transferencias':{'key_len':1234, 'ref rows':'blablalba', 'filtered'  99, 'extra':'blabla'},
 
  'rendimento':{'key_len':1234, 'ref rows':'blablalba', 'filtered'  99, 'extra':'blabla'},
 
  'giro':{'key_len':1234, 'ref rows':'blablalba', 'filtered'  99, 'extra':'blabla'}
 
}

i don't know the complexity of this mdev but it's interesting to better know what optimizer do

 Comments   
Comment by Elena Stepanova [ 2014-12-01 ]

rspadim,

Please don't create multiple entries about the same problem, it serves no purpose at all, only makes things more complicated. If you want to share your thoughts or confusion, or have questions, send it to the mailing list. If you want to provide more information on the existing issue, comment on it.

Comment by Elena Stepanova [ 2014-12-01 ]

Closing as a duplicate of MDEV-7125.

Comment by roberto spadim [ 2014-12-01 ]

hum, this is not a duplicate of mdev-7125, it's a feature (task) about extending explain command not a bug report about optimizer choise

Comment by Elena Stepanova [ 2014-12-01 ]

Sorry, misunderstood. Re-opened.

Comment by roberto spadim [ 2014-12-01 ]

=) no problem
is it relevant? i didn't know if mysql is trying something like it, i checked that exist a json output of explain, but i don't know if it show more information than the 'standard' explain command ( i will give a try )

Comment by roberto spadim [ 2014-12-01 ]

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

Comment by Sergei Petrunia [ 2014-12-02 ]

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.

Comment by Sergei Petrunia [ 2014-12-02 ]

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.

Comment by roberto spadim [ 2014-12-02 ]

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

Comment by Sergei Petrunia [ 2019-06-14 ]

Marking as duplicate of MDEV-6111, Optimizer trace

Generated at Thu Feb 08 07:18:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.