mysqlsymfonydoctrinedql

Possible solutions for slow Left Joins on complex doctrine query


I have a symfony repository method, which fetches a quite complex data set, which will then be placed in a CSV file by an export manager class. I don't want to place the whole code of handling the export job, but I managed to chatch the point at which the query slows down, so my question is regarding any other alternative to make that query faster, rather than the code itself. So the data fetched is some "site" data, which has multpiple "memberships", which then has a "user" . So the problem is, that when my query is trying to join the user information to the site, it slows down the execution. It looks like this:

$qb->leftJoin('s.memberships', 'ex_sm', 'WITH', 'ex_sm.revokedAt IS NULL');
$qb->leftJoin('ex_sm.user', 'ex_jappr', 'WITH', 'ex_sm.approverJobReactiveWeight IS NOT NULL');  

There are a few things (I tried or crossed through my mind could help) to mention:

There are 6 different user types, for now I just called the script fetching the one above and it took 33 minutes to return the data. We are talking about 512 sites, which is not a huge collection of data. So my question is : Is there another DQL or any Doctrine way to simlify or reduce the call nulber of leftJoins in such a complex query, and somehow improve the performance?

Update: I think the problem comes from indexes so I give some details on the relationships: The 'memberships' entity comes from a table named 'access' the relationship to the user in its model looks like this:

/**
 * The user this membership encapsulates.
 *
 * @ORM\ManyToOne(targetEntity="User", inversedBy="siteMemberships", cascade={"persist"})
 * @ORM\JoinColumn(name="security_identity_id", referencedColumnName="id")
 *
 * @var User
 */
protected $user; 

Here is a screenshot of the indexes assigend to the 'security_identity_id' columns enter image description here

And the related User comes from a 'user' table having this relationship pointing to the membershipt

/**
 * @ORM\OneToMany(targetEntity="SiteMembership", mappedBy="user", cascade={"persist"}, fetch="EXTRA_LAZY")
 */
protected $siteMemberships;

Primary key is 'id' in the entity. Hope this gives a better view of the problem. I'm not an expert with sql, but tried everything I found and could understand so far.

Update: Here is the query executed:

SELECT s0_.name AS name_0, s0_.id AS id_1, GROUP_CONCAT(DISTINCT u1_.name SEPARATOR ', ') AS sclr_2 FROM site s0_ 
  LEFT JOIN access a2_ ON s0_.id = a2_.entity_id 
  AND a2_.type IN ('site_member') 
  AND (a2_.revoked_at IS NULL) 
  LEFT JOIN user u1_ ON a2_.security_identity_id = u1_.id 
  AND (a2_.approver_job_reactive_weight IS NOT NULL)

This returns the first site record with it's joined membershipt and user peroperty. But even the fething of this one single row takes more than 2 minutes.

Here is the create table information of the access (membership entity) table

'CREATE TABLE `access` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `buddy_id` int(11) DEFAULT NULL,
  `security_identity_id` int(11) DEFAULT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `approver_job_reactive_weight` int(11) DEFAULT NULL,
  `entity_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `access_idx` (`type`,`security_identity_id`,`entity_id`,`buddy_id`),
  KEY `IDX_6692B54395CE8D6` (`buddy_id`),
  KEY `IDX_6692B54DF9183C9` (`security_identity_id`),
  KEY `IDX_6692B5481257D5D` (`entity_id`),
  KEY `idx_revoked_id_approver_type` (`revoked_at`,`entity_id`,`approver_job_reactive_weight`,`approver_job_planned_weight`,`type`),
  KEY `idx_user_site_access` (`revoked_at`,`security_identity_id`,`buddy_id`,`type`),
  KEY `idx_user` (`security_identity_id`),
  KEY `idx_user_id` (`security_identity_id`),
  CONSTRAINT `FK_6692B54DF9183C9` FOREIGN KEY (`security_identity_id`) REFERENCES `user` (`id`)
)
ENGINE=InnoDB AUTO_INCREMENT=262441 DEFAULT CHARSET=utf8 
COLLATE=utf8_unicode_ci'

I removed some of the columns which are not relevant.


Solution

  • When doing a LEFT JOIN, the ON needs to say how the tables are related. The WHERE clause normally has IS NULL or IS NOT NULL to say whether to exclude or include the right-hand rows.

    LEFT JOIN and INNER JOIN are mostly the same speed. But I need to see the indexes (SHOW CREATE TABLE) and the SQL of the SELECT to see if there are other issues.

    More

    Replace

    KEY `IDX_6692B5481257D5D` (`entity_id`),
    

    With

    INDEX(entity_id, type, revoked_at)