Details
-
Task
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
None
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
Attachments
Issue Links
- is blocked by
-
-
- Closed
-