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
-