Announcement

Collapse
No announcement yet.

Performance: leftJoins with IS NOT NULL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Performance: leftJoins with IS NOT NULL

    I must have tired you, but...

    maybe it makes sense to use inner join

    Code:
       'IS NOT NULL' => [
                        'innerJoins' => [['phoneNumbers', 'phoneNumbersMultiple']],
                        'distinct' => true
                    ],
    ​
    instead of left join
    Code:
                    'IS NOT NULL' => [
                        'leftJoins' => [['phoneNumbers', 'phoneNumbersMultiple']],
                        'whereClause' => [
                            'phoneNumbersMultiple.name!=' => null,
                        ],
                        'distinct' => true
                    ],
    ​
    About 25% speed increase

    And very important with complex queries (screens)
    Attached Files
    Last edited by dimyy; 08-30-2022, 02:41 PM.

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

    Comment


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

      Comment


      • #4
        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.
        Last edited by yuri; 08-31-2022, 07:05 AM.

        Comment

        Working...
        X