WHERE condition referring to inner table of left join can be sargable

Description

Some ERP generate that type of queries

1 2 3 4 5 6 7 8 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)) \G
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 *************************** 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

1 2 3 4 5 6 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 *************************** 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

Environment

None

Status

Assignee

Sergei Petrunia

Reporter

VAROQUI Stephane

External issue ID

None

External issue ID

None

Time tracking

40h

Components

Fix versions

Priority

Major