{"action":"created","issue":{"url":"https://api.github.com/repos/code-and-effect/effective_datatables/issues/24","repository_url":"https://api.github.com/repos/code-and-effect/effective_datatables","labels_url":"https://api.github.com/repos/code-and-effect/effective_datatables/issues/24/labels{/name}","comments_url":"https://api.github.com/repos/code-and-effect/effective_datatables/issues/24/comments","events_url":"https://api.github.com/repos/code-and-effect/effective_datatables/issues/24/events","html_url":"https://github.com/code-and-effect/effective_datatables/issues/24","id":170266303,"number":24,"title":"total_records nested SELECT is slow","user":{"login":"sheldond","id":149429,"avatar_url":"https://avatars.githubusercontent.com/u/149429?v=3","gravatar_id":"","url":"https://api.github.com/users/sheldond","html_url":"https://github.com/sheldond","followers_url":"https://api.github.com/users/sheldond/followers","following_url":"https://api.github.com/users/sheldond/following{/other_user}","gists_url":"https://api.github.com/users/sheldond/gists{/gist_id}","starred_url":"https://api.github.com/users/sheldond/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sheldond/subscriptions","organizations_url":"https://api.github.com/users/sheldond/orgs","repos_url":"https://api.github.com/users/sheldond/repos","events_url":"https://api.github.com/users/sheldond/events{/privacy}","received_events_url":"https://api.github.com/users/sheldond/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":3,"created_at":"2016-08-09T20:46:05Z","updated_at":"2016-08-10T16:45:36Z","closed_at":null,"body":"Hi Matt!\r\n\r\nI've been testing effective_datatables with production data and found that the query made in total_records is quite slow for me, over 4 seconds in my case. There may be some optimization I can make in our db, but I believe it's fundamentally slow because the nested select is selecting all records and all columns in the table. \r\n\r\nAn example query like:\r\n```\r\nSELECT COUNT(*) FROM (SELECT ... WHERE (...)) AS datatables_total_count\r\n```\r\nThis query takes about 4100ms.\r\n\r\nIf I remove the nested select, and perform the same query but just selecting a count:\r\n```\r\nSELECT COUNT(*) FROM ... WHERE (...)\r\n```\r\nThen the count query takes about 20ms.\r\n\r\nMaybe we can improve total_records query to use activerecord `.count` when possible, and fallback to the current query when not?\r\n\r\nDo you think you could provide an example of when `.count` does not work? If so maybe I could work on a solution.\r\n\r\nThanks!\r\nSheldon"},"comment":{"url":"https://api.github.com/repos/code-and-effect/effective_datatables/issues/comments/238927935","html_url":"https://github.com/code-and-effect/effective_datatables/issues/24#issuecomment-238927935","issue_url":"https://api.github.com/repos/code-and-effect/effective_datatables/issues/24","id":238927935,"user":{"login":"matt-riemer","id":954468,"avatar_url":"https://avatars.githubusercontent.com/u/954468?v=3","gravatar_id":"","url":"https://api.github.com/users/matt-riemer","html_url":"https://github.com/matt-riemer","followers_url":"https://api.github.com/users/matt-riemer/followers","following_url":"https://api.github.com/users/matt-riemer/following{/other_user}","gists_url":"https://api.github.com/users/matt-riemer/gists{/gist_id}","starred_url":"https://api.github.com/users/matt-riemer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/matt-riemer/subscriptions","organizations_url":"https://api.github.com/users/matt-riemer/orgs","repos_url":"https://api.github.com/users/matt-riemer/repos","events_url":"https://api.github.com/users/matt-riemer/events{/privacy}","received_events_url":"https://api.github.com/users/matt-riemer/received_events","type":"User","site_admin":false},"created_at":"2016-08-10T16:45:36Z","updated_at":"2016-08-10T16:45:36Z","body":"Hey again @sheldond \r\n\r\nTry out effective_datatables 2.6.4\r\n\r\nHere's the relevant commit:\r\n\r\nhttps://github.com/code-and-effect/effective_datatables/commit/2614285b7448130526f4b8fa5e8929a03c64fb39\r\n\r\nDoes this work for you on MySql?\r\nDoes it actually speed anything up? :)\r\n\r\nThanks,\r\n\r\n"}}
{"actor":{"display_login":"matt-riemer"}}