{"action":"created","issue":{"url":"https://api.github.com/repos/laravel/framework/issues/12861","repository_url":"https://api.github.com/repos/laravel/framework","labels_url":"https://api.github.com/repos/laravel/framework/issues/12861/labels{/name}","comments_url":"https://api.github.com/repos/laravel/framework/issues/12861/comments","events_url":"https://api.github.com/repos/laravel/framework/issues/12861/events","html_url":"https://github.com/laravel/framework/pull/12861","id":143559154,"number":12861,"title":"[5.2] Speed up chunk for large sets of data","user":{"login":"kevindoole","id":508730,"avatar_url":"https://avatars.githubusercontent.com/u/508730?v=3","gravatar_id":"","url":"https://api.github.com/users/kevindoole","html_url":"https://github.com/kevindoole","followers_url":"https://api.github.com/users/kevindoole/followers","following_url":"https://api.github.com/users/kevindoole/following{/other_user}","gists_url":"https://api.github.com/users/kevindoole/gists{/gist_id}","starred_url":"https://api.github.com/users/kevindoole/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kevindoole/subscriptions","organizations_url":"https://api.github.com/users/kevindoole/orgs","repos_url":"https://api.github.com/users/kevindoole/repos","events_url":"https://api.github.com/users/kevindoole/events{/privacy}","received_events_url":"https://api.github.com/users/kevindoole/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":11,"created_at":"2016-03-25T18:16:42Z","updated_at":"2016-05-16T09:06:26Z","closed_at":"2016-03-28T14:30:25Z","pull_request":{"url":"https://api.github.com/repos/laravel/framework/pulls/12861","html_url":"https://github.com/laravel/framework/pull/12861","diff_url":"https://github.com/laravel/framework/pull/12861.diff","patch_url":"https://github.com/laravel/framework/pull/12861.patch"},"body":"I've been working on a project that involves sifting through some pretty large databases, and the query builder's chunk method was not really doing it for me because it slowed down so much as it worked through the table. \r\n\r\nAs chunk gets deeper and deeper into a table, it asks MySQL to count through more and more rows. When chunk says to MySQL, `select whatever from wherever limit 800000, 1000` MySQL counts through 800,000 rows without using an index or nuthin. Then, the next page, it again counts through all previous rows to find the next set. Poor MySQL... It gets really really slow (details below).\r\n\r\nI realize this may be too much an edge case, so didn't want to spend too much time working on the code; just looking for feedback at this point.\r\n\r\nIf you're still reading, here are some scrappy benchmarks. :)\r\n\r\n--- \r\n\r\nAfter 4 seconds, chunk is very optimistic:\r\n`187500/862443 [▓▓▓▓▓▓░░░░░░░░░░░░░░░░░░░░░░]  21% 4 secs/18 secs 10.0 MiB`\r\n\r\nHowever, in the end the process has taken substantially longer:\r\n`862443/862443 [▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓] 100% 2 mins/2 mins 10.0 MiB`\r\n\r\nAs you can imagine, the problem gets progressively worse as the database gets larger.\r\n\r\nIf we instead query by `'id' > $theLastIdFromTheLastSet`, the chunking is much faster.\r\n\r\nAgain, after 4 seconds:\r\n`387000/862443 [▓▓▓▓▓▓▓▓▓▓▓▓░░░░░░░░░░░░░░░░]  44% 4 secs/9 secs 10.0 MiB`\r\n\r\nAnd the same set takes only 21s in the end.\r\n`862443/862443 [▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓] 100% 21 secs/21 secs 10.0 MiB`"},"comment":{"url":"https://api.github.com/repos/laravel/framework/issues/comments/219381375","html_url":"https://github.com/laravel/framework/pull/12861#issuecomment-219381375","issue_url":"https://api.github.com/repos/laravel/framework/issues/12861","id":219381375,"user":{"login":"barryvdh","id":973269,"avatar_url":"https://avatars.githubusercontent.com/u/973269?v=3","gravatar_id":"","url":"https://api.github.com/users/barryvdh","html_url":"https://github.com/barryvdh","followers_url":"https://api.github.com/users/barryvdh/followers","following_url":"https://api.github.com/users/barryvdh/following{/other_user}","gists_url":"https://api.github.com/users/barryvdh/gists{/gist_id}","starred_url":"https://api.github.com/users/barryvdh/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/barryvdh/subscriptions","organizations_url":"https://api.github.com/users/barryvdh/orgs","repos_url":"https://api.github.com/users/barryvdh/repos","events_url":"https://api.github.com/users/barryvdh/events{/privacy}","received_events_url":"https://api.github.com/users/barryvdh/received_events","type":"User","site_admin":false},"created_at":"2016-05-16T09:06:26Z","updated_at":"2016-05-16T09:06:26Z","body":"@kevindoole Did you perhaps also benchmark chunking with a `whereIn()` or subquery for the ids?\r\nEg. for large datasets, make 2 queries (or 1 + 1 subquery); 1 which selects just the `id` for the resultset, the second to get the actual data. This means MySQL can look up the ids from just the index, which should be faster (see https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ and https://explainextended.com/2011/02/11/late-row-lookups-innodb/ although I'm not sure this is not optimised yet)\r\nThat could work for other orderBy's also."}}