{"action":"closed","issue":{"url":"https://api.github.com/repos/cakephp/cakephp/issues/8386","repository_url":"https://api.github.com/repos/cakephp/cakephp","labels_url":"https://api.github.com/repos/cakephp/cakephp/issues/8386/labels{/name}","comments_url":"https://api.github.com/repos/cakephp/cakephp/issues/8386/comments","events_url":"https://api.github.com/repos/cakephp/cakephp/issues/8386/events","html_url":"https://github.com/cakephp/cakephp/issues/8386","id":137721646,"number":8386,"title":"ORM Slow - Cake 3.2.3 MySQL","user":{"login":"waspinator","id":100416,"avatar_url":"https://avatars.githubusercontent.com/u/100416?v=3","gravatar_id":"","url":"https://api.github.com/users/waspinator","html_url":"https://github.com/waspinator","followers_url":"https://api.github.com/users/waspinator/followers","following_url":"https://api.github.com/users/waspinator/following{/other_user}","gists_url":"https://api.github.com/users/waspinator/gists{/gist_id}","starred_url":"https://api.github.com/users/waspinator/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/waspinator/subscriptions","organizations_url":"https://api.github.com/users/waspinator/orgs","repos_url":"https://api.github.com/users/waspinator/repos","events_url":"https://api.github.com/users/waspinator/events{/privacy}","received_events_url":"https://api.github.com/users/waspinator/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/cakephp/cakephp/labels/ORM","name":"ORM","color":"ffffff"},{"url":"https://api.github.com/repos/cakephp/cakephp/labels/Question","name":"Question","color":"207de5"}],"state":"closed","locked":false,"assignee":null,"milestone":{"url":"https://api.github.com/repos/cakephp/cakephp/milestones/80","html_url":"https://github.com/cakephp/cakephp/milestones/3.2.4","labels_url":"https://api.github.com/repos/cakephp/cakephp/milestones/80/labels","id":1595817,"number":80,"title":"3.2.4","description":"Bugfixes and minor features for the 3.2 line.","creator":{"login":"markstory","id":24086,"avatar_url":"https://avatars.githubusercontent.com/u/24086?v=3","gravatar_id":"","url":"https://api.github.com/users/markstory","html_url":"https://github.com/markstory","followers_url":"https://api.github.com/users/markstory/followers","following_url":"https://api.github.com/users/markstory/following{/other_user}","gists_url":"https://api.github.com/users/markstory/gists{/gist_id}","starred_url":"https://api.github.com/users/markstory/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/markstory/subscriptions","organizations_url":"https://api.github.com/users/markstory/orgs","repos_url":"https://api.github.com/users/markstory/repos","events_url":"https://api.github.com/users/markstory/events{/privacy}","received_events_url":"https://api.github.com/users/markstory/received_events","type":"User","site_admin":false},"open_issues":38,"closed_issues":49,"state":"open","created_at":"2016-02-21T20:15:21Z","updated_at":"2016-03-02T04:09:05Z","due_on":"2016-03-06T05:00:00Z","closed_at":null},"comments":2,"created_at":"2016-03-01T23:33:28Z","updated_at":"2016-03-02T04:09:05Z","closed_at":"2016-03-02T04:09:05Z","body":"This is a (multiple allowed):\r\n* [ ] bug\r\n* [ ] enhancement\r\n* [ ] feature-discussion (RFC)\r\n\r\n* CakePHP Version: 3.2.3\r\n* Platform and Target: \r\nApache 2.4.12 (Win32)\r\nPHP 5.6.11\r\nMySQL 5.6.25\r\n\r\nThe database tables are the following sizes \r\n- applications: 120 000 rows X 45 columns\r\n- transactions: 500 000 rows X 13 columns\r\n- letter_histories: 520 000 rows X 29 columns\r\n\r\n### What you did\r\nI'm trying to query the database. When using Cake's `$query->all()` function the query takes 3-4 seconds. When using the same SQL query generated by `find()` and `mysqli_query()` the same query takes ~0.5 seconds. I'm using the Timer tab in DebugKit to report the time. Both are run from the same controller. The only difference between them is the faster one doesn't call `$applications = $query->all();` but instead runs the second snippet.\r\n\r\nThis takes a long time (3-4 seconds)\r\n\r\n    $query = $this->Applications->find()\r\n        ->select([\r\n            'transaction_id',\r\n        ])\r\n        ->contain([\r\n            'Transactions' => function ($q) {\r\n                return $q ->\r\n                select([\r\n                    'transaction_id',\r\n                ]);\r\n            },\r\n            'Transactions.LetterHistories' => function ($q) {\r\n                return $q ->\r\n                    select([\r\n                ]);\r\n            }\r\n            ])\r\n            ->where(['id_number' => $id_number]);\r\n    $query->hydrate(false)\r\n    $applications = $query->all();\r\n\r\n\r\nThis takes a shorter time (~0.5 seconds)\r\n\r\n\r\n    $conn = mysqli_connect('localhost', 'username', 'password', 'db_name');\r\n\r\n    $sql = 'SELECT Applications.transaction_id AS `Applications__transaction_id`, Transactions.transaction_id AS `Transactions__transaction_id` FROM applications Applications LEFT JOIN transactions Transactions ON Transactions.transaction_id = (Applications.transaction_id) WHERE id_number = 444444';\r\n\r\n    \r\n    $result = mysqli_query($conn, $sql);\r\n    $output_array = [];\r\n\r\n    while($row = $result->fetch_assoc()) {\r\n        $output_array[] = $row;\r\n    }\r\n    echo json_encode($output_array);                \r\n\r\nThis is fast as well\r\n\r\n    $connection = ConnectionManager::get('default');\r\n    $results = $connection->execute($sql)->fetchAll('assoc');\r\n\r\nThe result of the query is as follows:\r\n\r\nORM\r\n\r\n    object(Cake\\ORM\\ResultSet) {\r\n\r\n        'items' => [\r\n            (int) 0 => [\r\n                'transaction_id' => (int) 858755,\r\n                'transaction' => [\r\n                    'transaction_id' => (int) 858755,\r\n                    'letter_histories' => [\r\n                        (int) 0 => [\r\n                            'transaction_id' => (int) 858755\r\n                        ]\r\n                    ]\r\n                ]\r\n            ]\r\n        ]\r\n\r\n    }\r\n\r\nPHP json\r\n\r\n    [{\r\n        \"Applications__transaction_id\": \"858755\",\r\n        \"Transactions__transaction_id\": \"858755\"\r\n    }]\r\n\r\nCake execute\r\n\r\n    [\r\n        (int) 0 => [\r\n            'Applications__transaction_id' => '858755',\r\n            'Transactions__transaction_id' => '858755'\r\n        ]\r\n    ]\r\n\r\n### Expected Behavior\r\nThe run times for both native php and cake queries should be similar. \r\n\r\n### Actual Behavior\r\nCake is much slower than native php.\r\n\r\n"}}