Performance: leftJoins with IS NOT NULL

[dimyy]

I must have tired you, but...

maybe it makes sense to use inner join

   'IS NOT NULL' => [
                    'innerJoins' => [['phoneNumbers', 'phoneNumbersMultiple']],
                    'distinct' => true
                ],
​

instead of left join

                'IS NOT NULL' => [
                    'leftJoins' => [['phoneNumbers', 'phoneNumbersMultiple']],
                    'whereClause' => [
                        'phoneNumbersMultiple.name!=' => null,
                    ],
                    'distinct' => true
                ],
​

About 25% speed increase

And very important with complex queries (screens)

[yuri]

This where clause can be used inside OR where-clause, in this case inner join will give wrong result.

[dimyy]

Result in this case is the same because select count(*) from phone_number where name is null or name = ''; is always 0.

[yuri]

This is one case. But the same where-clause can be used within OR in reports or in custom code. The fix will break them.

I don't think this filter is often used. 25% performance increase (that can be not the case for MySQL) is not worth changing the well tested logic of the ORM making it more complex.