{"action":"created","issue":{"url":"https://api.github.com/repos/bolt/bolt/issues/4632","labels_url":"https://api.github.com/repos/bolt/bolt/issues/4632/labels{/name}","comments_url":"https://api.github.com/repos/bolt/bolt/issues/4632/comments","events_url":"https://api.github.com/repos/bolt/bolt/issues/4632/events","html_url":"https://github.com/bolt/bolt/issues/4632","id":123827246,"number":4632,"title":"Bad pagination performance","user":{"login":"C4Grey","id":2578826,"avatar_url":"https://avatars.githubusercontent.com/u/2578826?v=3","gravatar_id":"","url":"https://api.github.com/users/C4Grey","html_url":"https://github.com/C4Grey","followers_url":"https://api.github.com/users/C4Grey/followers","following_url":"https://api.github.com/users/C4Grey/following{/other_user}","gists_url":"https://api.github.com/users/C4Grey/gists{/gist_id}","starred_url":"https://api.github.com/users/C4Grey/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/C4Grey/subscriptions","organizations_url":"https://api.github.com/users/C4Grey/orgs","repos_url":"https://api.github.com/users/C4Grey/repos","events_url":"https://api.github.com/users/C4Grey/events{/privacy}","received_events_url":"https://api.github.com/users/C4Grey/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":9,"created_at":"2015-12-24T18:33:50Z","updated_at":"2016-01-02T14:28:27Z","closed_at":null,"body":"I've just finished migration of a huge amount of data into a Bolt-based site and now I have a problem with a very slow performance of pagination on large lists. So I watched into profiler and this query\r\n````sql\r\nSELECT bolt_news.* FROM bolt_news WHERE (`bolt_news`.`status` = 'published') ORDER BY `datepublish` DESC LIMIT 35 OFFSET 25270 \r\n````\r\ntakes near 12 seconds to fetch a data. I've tried [an alternative approach](http://devoluk.com/mysql-limit-offset-performance.html):\r\n````sql\r\nSELECT * \r\nFROM bolt_news \r\nJOIN (SELECT id \r\n      FROM bolt_news \r\n      WHERE status = 'published'\r\n      ORDER BY datepublish DESC\r\n      LIMIT 25270, 35) AS t ON t.id = bolt_news.id;\r\n````\r\nand it is very fast in comparison to previous query(~0.016 sec), even without a multi-column index(btw, how to create it using Bolt configuration file?). Where should I look to make a quick fix and replace the default query?"},"comment":{"url":"https://api.github.com/repos/bolt/bolt/issues/comments/168396190","html_url":"https://github.com/bolt/bolt/issues/4632#issuecomment-168396190","issue_url":"https://api.github.com/repos/bolt/bolt/issues/4632","id":168396190,"user":{"login":"GawainLynch","id":1427081,"avatar_url":"https://avatars.githubusercontent.com/u/1427081?v=3","gravatar_id":"","url":"https://api.github.com/users/GawainLynch","html_url":"https://github.com/GawainLynch","followers_url":"https://api.github.com/users/GawainLynch/followers","following_url":"https://api.github.com/users/GawainLynch/following{/other_user}","gists_url":"https://api.github.com/users/GawainLynch/gists{/gist_id}","starred_url":"https://api.github.com/users/GawainLynch/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/GawainLynch/subscriptions","organizations_url":"https://api.github.com/users/GawainLynch/orgs","repos_url":"https://api.github.com/users/GawainLynch/repos","events_url":"https://api.github.com/users/GawainLynch/events{/privacy}","received_events_url":"https://api.github.com/users/GawainLynch/received_events","type":"User","site_admin":false},"created_at":"2016-01-02T14:28:27Z","updated_at":"2016-01-02T14:28:27Z","body":"@SahAssar Yeah, 2.2 is definitely in maintenance mode functionality-wise.\r\n\r\n…and yes, doable with an extension in 3.x."}}