{"action":"created","issue":{"url":"https://api.github.com/repos/code-dot-org/code-dot-org/issues/11694","repository_url":"https://api.github.com/repos/code-dot-org/code-dot-org","labels_url":"https://api.github.com/repos/code-dot-org/code-dot-org/issues/11694/labels{/name}","comments_url":"https://api.github.com/repos/code-dot-org/code-dot-org/issues/11694/comments","events_url":"https://api.github.com/repos/code-dot-org/code-dot-org/issues/11694/events","html_url":"https://github.com/code-dot-org/code-dot-org/pull/11694","id":188128541,"number":11694,"title":"Add index to users.birthday","user":{"login":"jeremydstone","id":19172895,"avatar_url":"https://avatars.githubusercontent.com/u/19172895?v=3","gravatar_id":"","url":"https://api.github.com/users/jeremydstone","html_url":"https://github.com/jeremydstone","followers_url":"https://api.github.com/users/jeremydstone/followers","following_url":"https://api.github.com/users/jeremydstone/following{/other_user}","gists_url":"https://api.github.com/users/jeremydstone/gists{/gist_id}","starred_url":"https://api.github.com/users/jeremydstone/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jeremydstone/subscriptions","organizations_url":"https://api.github.com/users/jeremydstone/orgs","repos_url":"https://api.github.com/users/jeremydstone/repos","events_url":"https://api.github.com/users/jeremydstone/events{/privacy}","received_events_url":"https://api.github.com/users/jeremydstone/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"wjordan","id":56541,"avatar_url":"https://avatars.githubusercontent.com/u/56541?v=3","gravatar_id":"","url":"https://api.github.com/users/wjordan","html_url":"https://github.com/wjordan","followers_url":"https://api.github.com/users/wjordan/followers","following_url":"https://api.github.com/users/wjordan/following{/other_user}","gists_url":"https://api.github.com/users/wjordan/gists{/gist_id}","starred_url":"https://api.github.com/users/wjordan/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/wjordan/subscriptions","organizations_url":"https://api.github.com/users/wjordan/orgs","repos_url":"https://api.github.com/users/wjordan/repos","events_url":"https://api.github.com/users/wjordan/events{/privacy}","received_events_url":"https://api.github.com/users/wjordan/received_events","type":"User","site_admin":false},"assignees":[{"login":"wjordan","id":56541,"avatar_url":"https://avatars.githubusercontent.com/u/56541?v=3","gravatar_id":"","url":"https://api.github.com/users/wjordan","html_url":"https://github.com/wjordan","followers_url":"https://api.github.com/users/wjordan/followers","following_url":"https://api.github.com/users/wjordan/following{/other_user}","gists_url":"https://api.github.com/users/wjordan/gists{/gist_id}","starred_url":"https://api.github.com/users/wjordan/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/wjordan/subscriptions","organizations_url":"https://api.github.com/users/wjordan/orgs","repos_url":"https://api.github.com/users/wjordan/repos","events_url":"https://api.github.com/users/wjordan/events{/privacy}","received_events_url":"https://api.github.com/users/wjordan/received_events","type":"User","site_admin":false},{"login":"ashercodeorg","id":14206260,"avatar_url":"https://avatars.githubusercontent.com/u/14206260?v=3","gravatar_id":"","url":"https://api.github.com/users/ashercodeorg","html_url":"https://github.com/ashercodeorg","followers_url":"https://api.github.com/users/ashercodeorg/followers","following_url":"https://api.github.com/users/ashercodeorg/following{/other_user}","gists_url":"https://api.github.com/users/ashercodeorg/gists{/gist_id}","starred_url":"https://api.github.com/users/ashercodeorg/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ashercodeorg/subscriptions","organizations_url":"https://api.github.com/users/ashercodeorg/orgs","repos_url":"https://api.github.com/users/ashercodeorg/repos","events_url":"https://api.github.com/users/ashercodeorg/events{/privacy}","received_events_url":"https://api.github.com/users/ashercodeorg/received_events","type":"User","site_admin":false}],"milestone":null,"comments":2,"created_at":"2016-11-08T23:04:59Z","updated_at":"2016-11-08T23:08:30Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/code-dot-org/code-dot-org/pulls/11694","html_url":"https://github.com/code-dot-org/code-dot-org/pull/11694","diff_url":"https://github.com/code-dot-org/code-dot-org/pull/11694.diff","patch_url":"https://github.com/code-dot-org/code-dot-org/pull/11694.patch"},"body":"(Originally opened as https://github.com/code-dot-org/code-dot-org/pull/11237; closing that and reopening after a rebase had unintended effects.)\r\n\r\nThis adds an index to users.birthday which the contact rollup process (for Pardot integration) needs in order to run efficiently.\r\n\r\nOne of the things the rollup computes what ages of students are taught by each teacher. The full graph of this is 400K teachers -> even more sections -> ~10M students. The brute force version of this query across the entire data set never completes. Breaking into batches by teacher completes but the throughput is too low. Ultimately breaking into batches by student age runs well (completes all batches in <10 min). This requires indexing by student age.\r\n\r\nA representative query using student age is:\r\nSELECT DISTINCT sections.user_id AS teacher_user_id\r\nFROM dashboard_development.users AS students\r\nINNER JOIN dashboard_development.followers AS followers\r\nON followers.student_user_id = students.id\r\nINNER JOIN dashboard_development.sections AS sections\r\nON sections.id = followers.section_id\r\nWHERE\r\nstudents.birthday > DATE_ADD(NOW(),INTERVAL -7 YEAR) AND\r\nstudents.birthday <= DATE_ADD(NOW(),INTERVAL -6 YEAR) AND\r\nstudents.id != sections.user_id;\r\n\r\nTo @ashercodeorg's earlier question to demonstrate the index gets used, EXPLAIN output on the above is:\r\n![explain](https://cloud.githubusercontent.com/assets/19172895/20121119/9aeccf7e-a5c4-11e6-8d8b-6160e54a0815.png)"},"comment":{"url":"https://api.github.com/repos/code-dot-org/code-dot-org/issues/comments/259288458","html_url":"https://github.com/code-dot-org/code-dot-org/pull/11694#issuecomment-259288458","issue_url":"https://api.github.com/repos/code-dot-org/code-dot-org/issues/11694","id":259288458,"user":{"login":"ashercodeorg","id":14206260,"avatar_url":"https://avatars.githubusercontent.com/u/14206260?v=3","gravatar_id":"","url":"https://api.github.com/users/ashercodeorg","html_url":"https://github.com/ashercodeorg","followers_url":"https://api.github.com/users/ashercodeorg/followers","following_url":"https://api.github.com/users/ashercodeorg/following{/other_user}","gists_url":"https://api.github.com/users/ashercodeorg/gists{/gist_id}","starred_url":"https://api.github.com/users/ashercodeorg/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ashercodeorg/subscriptions","organizations_url":"https://api.github.com/users/ashercodeorg/orgs","repos_url":"https://api.github.com/users/ashercodeorg/repos","events_url":"https://api.github.com/users/ashercodeorg/events{/privacy}","received_events_url":"https://api.github.com/users/ashercodeorg/received_events","type":"User","site_admin":false},"created_at":"2016-11-08T23:08:30Z","updated_at":"2016-11-08T23:08:30Z","body":"LGTM."}}
{"actor":{"display_login":"ashercodeorg"}}