Details
-
Task
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2.4-4, 10.2.4-1, 10.2.4-2
Description
Some ERP generate that type of queries
explain
|
select count(0) AS `COUNT(*)` |
from |
E_relance
|
left join E_action on ((E_action.id_demande = E_relance.id_demande)) |
where |
((E_relance.id_demande = 88224) or (E_action.id_demande = 88224)) |
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: E_relance
|
type: index
|
possible_keys: fk_E_relance_E_demande1
|
key: fk_E_relance_E_demande1
|
key_len: 5
|
ref: NULL
|
rows: 205655
|
Extra: Using index
|
*************************** 2. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: E_action
|
type: ref
|
possible_keys: FI__4381
|
key: FI__4381
|
key_len: 4
|
ref: siam2.E_relance.id_demande
|
rows: 2
|
Extra: Using where; Using index
|
The full index scan on primary table is not necessary if const on joined table is not NULL.
Rewriting the query is hard to be done in the application because it may happen that the application is doing a lookup for NULL on left joined table
indeed query rewriting change the lookup to const or range in case the second condition is not null and propagated to the upper table
MAD_WEB_DEV (madweb@localhost) [siam2]>
|
explain select count(0) AS `COUNT(*)`
|
from
|
E_relance left join E_action on ((E_action.id_demande = E_relance.id_demande))
|
where
|
((E_relance.id_demande = 88224) or (E_relance.id_demande = 88224))\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: E_relance
|
type: ref
|
possible_keys: fk_E_relance_E_demande1
|
key: fk_E_relance_E_demande1
|
key_len: 5
|
ref: const
|
rows: 1
|
Extra: Using index
|
*************************** 2. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: E_action
|
type: ref
|
possible_keys: FI__4381
|
key: FI__4381
|
key_len: 4
|
ref: siam2.E_relance.id_demande
|
rows: 2
|
Extra: Using where; Using index
|
Attachments
Issue Links
- relates to
-
MDEV-10946 Partition pruning not working with LEFT JOIN
-
- Stalled
-
Activity
Elena Stepanova, I would like a test pass for this. Need outer joins
ON expressions have equalities
WHERE expression has range conditions on inner tables
the outer table has indexes that these range conditions could use
I've run tests on the patch comparing to the vanilla tree, didn't get any problems which weren't there before the patch. Please go ahead and push, there will also be regular regression tests on the main tree.
This fix doesn't help with MDEV-10946.
This fix allows using parts of WHERE to construct access for the outer tables.
MDEV-10946 needs to use a part of WHERE when the ON expression is used to construct access to an inner table.