Doctrine Joining two Models with single-table inheritance, second join slows down

Question

I have 3 models using Single Table Inheritance. They are for three different types of items that can be purchased on our website. The items are placed into categories, so the Category model has a property for mapping each of the three types.

When using a simple select to get all Categories and then display their names and the number of each type of item within the category, Doctrine executes 361 queries in 549 ms total. (One for the list of categories, and then one for each type within the category.)

So I started adding joins to the query to eliminate all the extra queries. It worked fine for the first item type, the main query runs in 101.80 ms. (According to the Symfony Profiler Toolbar)

$this->_em->createQueryBuilder()
        ->select([$alias, 'courses'])
        ->from($this->_entityName, $alias)
        ->leftJoin("{$alias}.courses", 'courses');

As soon as I add the second join, the query slows to 24050.14 ms

$qb = $this->_em->createQueryBuilder()
        ->select([$alias, 'courses', 'bundles'])
        ->from($this->_entityName, $alias)
        ->leftJoin("{$alias}.courses", 'courses')
        ->leftJoin("{$alias}.bundles", 'bundles');

I haven't even tried with the third join yet, afraid it will just crash the server.

What's really weird is if I use Doctrine Query Logging and get the exact query, and manually run it against my DB, it runs in only .2 seconds.

The table does have indexes on all FK columns and the discriminator column.

Any advice would be greatly appreciated. Thanks!

Edit: 4/3

I had to get the branch back to a working point on other issues to get back to this issue.

SQL Fiddle with Schema (minus the FKs for other tables which are not loaded in this situation): http://sqlfiddle.com/#!2/85051

So, I have the page without the join, and it does 382 queries in 820.38 ms for lazy-loading. When I manually join instead of relying on lazy-loading, it does to 130 is 21159 (This is only joining 2 of the models, so it still lazy-loads the third)

$qb = $this->_em->createQueryBuilder()
        ->select([$alias, 'courses', 'bundles'])
        ->from($this->_entityName, $alias)
        ->leftJoin("{$alias}.courses", 'courses')
        ->leftJoin("{$alias}.bundles", 'bundles');

Here is the query from the Symfony toolbar (20241.26 ms)

    SELECT 
  i0_.description AS description0, 
  i0_.id AS id1, 
  i0_.name AS name2, 
  i0_.created_at AS created_at3, 
  i0_.updated_at AS updated_at4, 
  i0_.display_order AS display_order5, 
  i1_.atccode AS atccode6, 
  i1_.version AS version7, 
  i1_.description AS description8, 
  i1_.online_price AS online_price9, 
  i1_.mail_price AS mail_price10, 
  i1_.is_featured AS is_featured11, 
  i1_.code AS code12, 
  i1_.hours AS hours13, 
  i1_.summary AS summary14, 
  i1_.seo_keywords AS seo_keywords15, 
  i1_.seo_description AS seo_description16, 
  i1_.asha_code AS asha_code17, 
  i1_.preview_name AS preview_name18, 
  i1_.preview_link AS preview_link19, 
  i1_.preview_type AS preview_type20, 
  i1_.is_active AS is_active21, 
  i1_.id AS id22, 
  i1_.name AS name23, 
  i1_.created_at AS created_at24, 
  i1_.updated_at AS updated_at25, 
  i1_.deleted_at AS deleted_at26, 
  i1_.goals AS goals27, 
  i1_.disclosure_statement AS disclosure_statement28, 
  i1_.embedded_video AS embedded_video29, 
  i1_.broadcast_chat_link AS broadcast_chat_link30, 
  i2_.atccode AS atccode31, 
  i2_.version AS version32, 
  i2_.description AS description33, 
  i2_.online_price AS online_price34, 
  i2_.mail_price AS mail_price35, 
  i2_.is_featured AS is_featured36, 
  i2_.code AS code37, 
  i2_.hours AS hours38, 
  i2_.summary AS summary39, 
  i2_.seo_keywords AS seo_keywords40, 
  i2_.seo_description AS seo_description41, 
  i2_.asha_code AS asha_code42, 
  i2_.preview_name AS preview_name43, 
  i2_.preview_link AS preview_link44, 
  i2_.preview_type AS preview_type45, 
  i2_.is_active AS is_active46, 
  i2_.id AS id47, 
  i2_.name AS name48, 
  i2_.created_at AS created_at49, 
  i2_.updated_at AS updated_at50, 
  i2_.deleted_at AS deleted_at51, 
  i0_.created_by AS created_by52, 
  i0_.updated_by AS updated_by53, 
  i1_.type AS type54, 
  i1_.item_format_id AS item_format_id55, 
  i1_.company_id AS company_id56, 
  i1_.created_by AS created_by57, 
  i1_.updated_by AS updated_by58, 
  i1_.format_video_id AS format_video_id59, 
  i1_.exam_id AS exam_id60, 
  i1_.survey_id AS survey_id61, 
  i1_.royalty_owner_id AS royalty_owner_id62, 
  i2_.type AS type63, 
  i2_.item_format_id AS item_format_id64, 
  i2_.company_id AS company_id65, 
  i2_.created_by AS created_by66, 
  i2_.updated_by AS updated_by67 
FROM 
  item_category i0_ 
  LEFT JOIN item_category_assignment i3_ ON i0_.id = i3_.item_category_id 
  LEFT JOIN item i1_ ON i1_.id = i3_.item_id 
  AND i1_.type IN ('Product') 
  AND (
    (
      i1_.deleted_at IS NULL 
      OR i1_.deleted_at > '2014-04-03 13:50:45'
    )
  ) 
  LEFT JOIN item_category_assignment i4_ ON i0_.id = i4_.item_category_id 
  LEFT JOIN item i2_ ON i2_.id = i4_.item_id 
  AND i2_.type IN ('Bundle') 
  AND (
    (
      i2_.deleted_at IS NULL 
      OR i2_.deleted_at > '2014-04-03 13:50:45'
    )
  ) 
WHERE 
  i0_.id IN (
    '108', 
    '175', 
    '100', 
    '202', 
    '198', 
    '203', 
    '199', 
    '200', 
    '201', 
    '197', 
    '101', 
    '98', 
    '102', 
    '131', 
    '105', 
    '41', 
    '72', 
    '64', 
    '73', 
    '194', 
    '195', 
    '29', 
    '189', 
    '139', 
    '103', 
    '37', 
    '99', 
    '14', 
    '110', 
    '193', 
    '80', 
    '111', 
    '68', 
    '183', 
    '39', 
    '71', 
    '53', 
    '66', 
    '178', 
    '179', 
    '180', 
    '176', 
    '174', 
    '75', 
    '17', 
    '32', 
    '81', 
    '181', 
    '182', 
    '74', 
    '104', 
    '184', 
    '26', 
    '49', 
    '190', 
    '191', 
    '36', 
    '24', 
    '85', 
    '30', 
    '107', 
    '91', 
    '90', 
    '185', 
    '23', 
    '196', 
    '60', 
    '89', 
    '21', 
    '95', 
    '65', 
    '28', 
    '33', 
    '58', 
    '187', 
    '9', 
    '132', 
    '12', 
    '43', 
    '192', 
    '5', 
    '62', 
    '40', 
    '87', 
    '7', 
    '83', 
    '27', 
    '6', 
    '86', 
    '10', 
    '13', 
    '15', 
    '70', 
    '69', 
    '121', 
    '67', 
    '93', 
    '97', 
    '92', 
    '94', 
    '188', 
    '177', 
    '82', 
    '96', 
    '42', 
    '137', 
    '19', 
    '11', 
    '63', 
    '20', 
    '51', 
    '57', 
    '8', 
    '22', 
    '48', 
    '35', 
    '4', 
    '135', 
    '61', 
    '186', 
    '106', 
    '109', 
    '88', 
    '16', 
    '31', 
    '34'
  ) 
ORDER BY 
  i0_.name ASC

AND the explain for it:

1   SIMPLE       i0_    ALL     PRIMARY                                                                       126   Using where; Using filesort  ,
1   SIMPLE       i3_    ref     item_category_id         item_category_id  4        cems-staging.i0_.id       6                                  ,
1   SIMPLE       i1_    eq_ref  PRIMARY                    PRIMARY           4        cems-staging.i3_.item_id  1     Using where                  ,
1   SIMPLE       i4_    ref     item_category_id         item_category_id  4        cems-staging.i0_.id       6                                  ,
1   SIMPLE       i2_    eq_ref  PRIMARY                    PRIMARY           4        cems-staging.i4_.item_id  1     Using where   

* PHP *

This is the mapping on the Items model:

 /**
 * @var ArrayCollection
 * @ORM\ManyToMany(targetEntity="models\Category")
 * @ORM\JoinTable(name="item_category_assignment",
 *    joinColumns={@ORM\JoinColumn(name="item_id", referencedColumnName="id")},
 *    inverseJoinColumns={@ORM\JoinColumn(name="item_category_id", referencedColumnName="id")}
 * )
 */
protected $categories;

And the mappings on the Category Model

    /**
 * @var ArrayCollection
 *
 * @ORM\ManyToMany(targetEntity="models\Course")
 * @ORM\JoinTable(name="item_category_assignment",
 *    inverseJoinColumns={@ORM\JoinColumn(name="item_id", referencedColumnName="id")},
 *    joinColumns={@ORM\JoinColumn(name="item_category_id", referencedColumnName="id")}
 * )
 */
protected $courses;

/**
 * @var ArrayCollection
 *
 * @ORM\ManyToMany(targetEntity="models\Bundle", mappedBy="categories", orphanRemoval=true)
 * @ORM\JoinTable(name="item_category_assignment",
 *    inverseJoinColumns={@ORM\JoinColumn(name="item_id", referencedColumnName="id")},
 *    joinColumns={@ORM\JoinColumn(name="item_category_id", referencedColumnName="id")}
 * )
 */
protected $bundles;

/**
 * @var ArrayCollection
 *
 * @ORM\ManyToMany(targetEntity="models\Package", mappedBy="categories", orphanRemoval=true)
 * @ORM\JoinTable(name="item_category_assignment",
 *    inverseJoinColumns={@ORM\JoinColumn(name="item_id", referencedColumnName="id")},
 *    joinColumns={@ORM\JoinColumn(name="item_category_id", referencedColumnName="id")}
 * )
 */
protected $packages;

Soerabaja

Whats table structure and schema look like? What does a select and join with course and bundles look like as far as time to run? My first guess is no proper indexing, or the mysql parameters need to be tuned if the result set is large so that its not paging to disk. I wonder if when you run it manually its already in the query cache, hence it runs faster... Re-run the select with SELECT SQL_NO_CACHE {your original select here} – Mephitis

For us slow folks. It seems Doctrine is an object relational model built on top of MySQL (at least in this case)? Off the top of my head it seems: "It isn't the DB (MySQL)" if running that portion doesn't experience the slowdown. Is it possible that Doctrine does part of the join operation itself? ie: What was the logged query you ran and what would it's result look like? (Yes, I guess I'm cheating, I don't want to have to install Doctrine to be marginally helpful) – Pavkovic

@Mephitis - upvote for query cache, I'd forgot about that. (though 200 ms seems really slow in that case, but I don't know the result size) – Pavkovic

@Mephitis - The manual run takes .2 seconds according to SqlYog and I don't believe I have any cache on already, but I will re-run and get back to you. I'll also upload the schema and queries to sql fiddle. – Soerabaja

@ebyrob As far as I can tell the problem is definitely Doctrine, I just don't get where or how to fix it. – Soerabaja

"What's really weird is if I use Doctrine Query Logging and get the exact query" - Query please! PS - MySQL query cache is pretty much on all the time unless you went to pains to disable it. – Pavkovic

If your query is for read only purpose then You may try using Partial select ( selecting only required fields, it will return partial object, you can use it for read only purpose ), or DQL with selecting required fields only ( It will not return objects but will return array), Native query ( again with selecting required fields) – Hatchery

doctrine-orm.readthedocs.org/en/latest/reference/… , doctrine-orm.readthedocs.org/en/latest/reference/… , uvd.co.uk/blog/some-doctrine-2-best-practices – Hatchery

@Soerabaja it would be better to post the schema of your tables with proper relationship details and also post the EXPLAIN plan for your queries (using single join,using multiple joins), without proper information you will have replies based on the guess. – Insight

@Soerabaja I don't know doctrine, so I'm a bit confused: ->from($this->_entityName, $alias) 1) Why would a from clause need two parameters (unless there's an implicit join). 2) How could the same string $alias be both a retrieved column and in the name of the table (type) being read? 3) What might be the value of $alias? 4) Is $alias read only or also written in this line of code? – Pavkovic

I haven't had a chance yet to export the structure, sorry guys, working on it. – Soerabaja

@ebyrob All of that is related to doctrine so maybe read the Doctrine docs? – Soerabaja

Did you try to run it several times to make sure this is constant ? Because if you are seeing this time in the profiler (24sec) and only 0.2sec in a manual run... This is really odd, because the profiler itself logs a native SQL query that has been generated by Doctrine. So there should not be any difference – Moneybags

@Moneybags yes it was consistent. – Soerabaja

Updated with schema, explain, query. – Soerabaja

Does this issue remains in production environment ? – Moneybags

@Moneybags Do you mean switching Symfony to production mode? – Soerabaja

@Soerabaja when you say "Here is the query from the Symfony toolbar (20241.26 ms)" Does it mean that Symfony reports that the query took ~20 seconds to run? And then if you were to take that exact query and run it from the CLI you get .2 seconds even while using SQL_NO_CACHE? You can try one additional things prior to running the SQL manually to ensure its not a cache issue. RESET QUERY CAHCE; clears the query cache. so run this and then run the query manually. Please report the time manual query time. Thanks. – Mephitis

@jessica never used SqlYog, but if you are using that to run your queries as opposed to the MySQL CLI client, take a look at stackoverflow.com/questions/1031484/… It could be that your "base/control" of .2seconds is incorrect.. – Mephitis

@Mephitis Looking into it, thanks. – Soerabaja

Answer 1

The difference between Objects and attributes vs rows and columns really doesn't matter here.

You're trying to do a series of LEFT JOINs where you should probably be using UNIONs.

Please see Microsoft's take on CROSS JOIN and Cartesian products. They really summarize it well.

EDIT: I think this answers the question of "Why is my second join slow?".

Did you mean to ask: "Please help me improve my 3 model Doctrine query to run in less than 550ms." ?

Pavkovic

Doctrine doesn't support UNION. – Soerabaja

@Soerabaja I beg to differ: doctrine-orm.readthedocs.org/en/latest/reference/… Plus, as we know in the RDBMS world, UNION can be simulated with a mutex table (or type) JOIN... – Pavkovic

Answer 2

I will explain it in simple terms , I think you are trying to join 3 tables named item onto item category onto item category assignment so probably want something like:

    SELECT item.id, item.name, item.value FROM item
       LEFT JOIN item category
         INNER JOIN item category assignment
       ON item category.id = item category assignment.id
       ON item category.id = item.id

This joins item onto item category assignment via the intermediate table item category. Because the join between item and item category is a LEFT JOIN, you will get all item records.

Which is alternative:

         SELECT item.id, item.name, item.value FROM item
         LEFT JOIN item category ON item category.id = item.id
         LEFT JOIN item category assignment ON item category.id = item category assignment.id

Dosser

Please explain how to force Doctrine to do that. Because as I've said over and over again, this is a Doctrine related question. – Soerabaja

@Soerabaja change: ->leftJoin("{$alias}.bundles", 'bundles'); to ->innerJoin("{$alias}.bundles", 'bundles');. Note: I'm still not 100% on your model, but in my testing switching to INNER JOIN definitely removes tons of extra rows. (I'm just not 100% that you're not losing anything, especially if you stack more joins) – Pavkovic

I can't inner join on both bundles and courses, because then I won't get categories which have no bundles or no courses. It will also still generate a query which inner joins to category_assignment twice. :/ – Soerabaja

@Soerabaja see my updated answer. JOINing only onto previous NULL rows for each new type should limit your result size to something manageable. Joining twice isn't necessarily the problem. Unconditional CROSS JOIN twice is the problem. – Pavkovic

Again - how do you make Doctrine do that? – Soerabaja