Using `contain` makes CakePHP 3 find() call slower, while the underlying query itself is still fast

Question

I have a CakePHP 3 find() call that I've been trying to optimise for a while. I have narrowed it down to these two snippets with the only difference being the contain parameter removed. Timings were measured by DebugKit.

Including contain:

public function qrytest() {
    $table = TableRegistry::get('OrderOptions', array(
        'className' => 'App\Model\Table\Opencart\OrderOptionsTable',
    ));
    $results = $table->find('all', array(
        'fields' => array(
            'OrderOptions.order_product_id',
            'OrderOptions.order_option_id',
            'OrderOptions.value',
        ),
        'conditions' => array(
            'OrderOptions.order_id' => 123,
        ),
        'contain' => array(
            'OpencartProductOptions' => array(
                'OpencartOption',
            ),
            'OpencartOrderProducts' => array(
                'OpencartOrders' => array(
                    'OpencartShippingZone',
                ),
                'OpencartProduct' => array(
                    'OpencartWeightClass',
                ),
            ),
            'OpencartProductOptionValues',
        ),
    ))->all();
    $this->viewBuilder()->template('anEmptyView');
}

Sql Log: 1 / 99 ms; Timer: 4.42 s / 5.72 MB

---

No contain

public function qrytest() {
    $table = TableRegistry::get('OrderOptions', array(
        'className' => 'App\Model\Table\Opencart\OrderOptionsTable',
    ));
    $results = $table->find('all', array(
        'fields' => array(
            'OrderOptions.order_product_id',
            'OrderOptions.order_option_id',
            'OrderOptions.value',
        ),
        'conditions' => array(
            'OrderOptions.order_id' => 123,
        ),
        // contain parameter removed; nothing else changed
    ))->all();
    $this->viewBuilder()->template('anEmptyView');
}

Sql Log: 1 / 92 ms; Timer: 1.06 s / 4.83 MB

---

Summary: including the contain parameter makes the controller take almost 5 seconds to render, while not changing the actual query time significantly. Expanding the DebugKit's Timers tab shows Controller action: 4,388.74 ms (screenshots part 1, part 2)

---

First thing that comes to mind here is optimising joins and indexes, but if you look at the results above, the query itself seems to take the same amount of time in both cases. To confirm, I ran the original query (including JOINs) directly in MySQL Workbench, and it takes about 100 ms, and that matches with what I'm seeing in the DebugKit output. Also I actually did create all required indexes some time ago anyway.

That said, I'm starting to think the problem is not with the MySQL query I have, but with how CakePHP handles it, and this is where I please need help.

I can post the table structure information if you need me to.

Updates

• No difference between enabled and disabled hydration. Tried both $query->enableHydration(false); and $query->hydrate(false); (as per query-builder.html#getting-arrays-instead-of-entities)
• Disabled debug by explicitly setting 'debug' => false, still slow: time = 4.30s, measured by the browser

• Here's the generated query:

  SELECT 
    `OrderOptions`.`order_product_id` AS `OrderOptions__order_product_id`, 
    `OrderOptions`.`order_option_id` AS `OrderOptions__order_option_id`, 
    `OrderOptions`.`value` AS `OrderOptions__value` 
  FROM 
    `order_option` `OrderOptions` 
    LEFT JOIN `product_option` `OpencartProductOptions` ON `OpencartProductOptions`.`product_option_id` = (
      `OrderOptions`.`product_option_id`
    ) 
    LEFT JOIN `option` `OpencartOption` ON `OpencartOption`.`option_id` = (
      `OpencartProductOptions`.`option_id`
    ) 
    LEFT JOIN `order_product` `OpencartOrderProducts` ON `OpencartOrderProducts`.`order_product_id` = (
      `OrderOptions`.`order_product_id`
    ) 
    LEFT JOIN `order` `OpencartOrders` ON `OpencartOrders`.`order_id` = (
      `OpencartOrderProducts`.`order_id`
    ) 
    LEFT JOIN `zone` `OpencartShippingZone` ON `OpencartShippingZone`.`zone_id` = (
      `OpencartOrders`.`shipping_zone_id`
    ) 
    LEFT JOIN `product` `OpencartProduct` ON `OpencartProduct`.`product_id` = (
      `OpencartOrderProducts`.`product_id`
    ) 
    LEFT JOIN `weight_class` `OpencartWeightClass` ON `OpencartProduct`.`weight_class_id` = (
      `OpencartWeightClass`.`weight_class_id`
    ) 
    LEFT JOIN `product_option_value` `OpencartProductOptionValues` ON `OpencartProductOptionValues`.`product_option_value_id` = (
      `OrderOptions`.`product_option_value_id`
    ) 
  WHERE 
    `OrderOptions`.`order_id` = 123
  

• Here's the XDebug output:

Answer 1

I work with larger enterprise projects and the frontend is CakePHP but when you start a discussion about performance over a framework for rapidly development you are placing a demand that this kind of framework does not handle by theyself.

Make use of CakePHP when it is appropriate to, for example show registries from a Table.

When I get into larger datasets like you, I made use of database resources to solve their problems of performance, this way I donnot demand from CakePHP performance expectations.

Using EXPLAIN you can setup the appropriate INDEX for a QUERY, when this is optimized enough setup a view simple like:

CREATE VIEW %VIEW-NAME% AS %QUERY%

If using views is not enough, for example if views still have larger datasets, start working with STORED PROCEDURES, this is an excelent way to work because you can make part of your program run inside the database, and again you use CakePHP only to show this data.