{"action":"created","issue":{"url":"https://api.github.com/repos/Cockatrice/Cockatrice/issues/2303","repository_url":"https://api.github.com/repos/Cockatrice/Cockatrice","labels_url":"https://api.github.com/repos/Cockatrice/Cockatrice/issues/2303/labels{/name}","comments_url":"https://api.github.com/repos/Cockatrice/Cockatrice/issues/2303/comments","events_url":"https://api.github.com/repos/Cockatrice/Cockatrice/issues/2303/events","html_url":"https://github.com/Cockatrice/Cockatrice/issues/2303","id":194097636,"number":2303,"title":"Ban DB Query Improvements","user":{"login":"woogerboy21","id":1361287,"avatar_url":"https://avatars.githubusercontent.com/u/1361287?v=3","gravatar_id":"","url":"https://api.github.com/users/woogerboy21","html_url":"https://github.com/woogerboy21","followers_url":"https://api.github.com/users/woogerboy21/followers","following_url":"https://api.github.com/users/woogerboy21/following{/other_user}","gists_url":"https://api.github.com/users/woogerboy21/gists{/gist_id}","starred_url":"https://api.github.com/users/woogerboy21/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/woogerboy21/subscriptions","organizations_url":"https://api.github.com/users/woogerboy21/orgs","repos_url":"https://api.github.com/users/woogerboy21/repos","events_url":"https://api.github.com/users/woogerboy21/events{/privacy}","received_events_url":"https://api.github.com/users/woogerboy21/received_events","type":"User","site_admin":false},"labels":[{"id":110154643,"url":"https://api.github.com/repos/Cockatrice/Cockatrice/labels/App%20-%20Servatrice","name":"App - Servatrice","color":"009800","default":false},{"id":107654899,"url":"https://api.github.com/repos/Cockatrice/Cockatrice/labels/Cleanup","name":"Cleanup","color":"fbca04","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":9,"created_at":"2016-12-07T16:29:44Z","updated_at":"2016-12-18T14:17:18Z","closed_at":null,"body":"So lacking the awesome skills that others more familiar with DB queries have, can anyone give recommendations on how to improve the DB queries involved in locating if a user is banned?  There are multiple queries that are performed at each and every login and I would like to see it improved on (if possible).\r\n\r\nYou can find the queries staring at:\r\nhttps://github.com/Cockatrice/Cockatrice/blob/master/servatrice/src/servatrice_database_interface.cpp#L298\r\n\r\nID: https://github.com/Cockatrice/Cockatrice/blob/master/servatrice/src/servatrice_database_interface.cpp#L313\r\n\r\nUserName:\r\nhttps://github.com/Cockatrice/Cockatrice/blob/master/servatrice/src/servatrice_database_interface.cpp#L351\r\n\r\nIP:\r\nhttps://github.com/Cockatrice/Cockatrice/blob/master/servatrice/src/servatrice_database_interface.cpp#L374\r\n\r\nThe one particular query that seems to always show up in the mysql_slow.log is the one for client id.  There is probably room for impovement on that query at the very least."},"comment":{"url":"https://api.github.com/repos/Cockatrice/Cockatrice/issues/comments/267823643","html_url":"https://github.com/Cockatrice/Cockatrice/issues/2303#issuecomment-267823643","issue_url":"https://api.github.com/repos/Cockatrice/Cockatrice/issues/2303","id":267823643,"user":{"login":"woogerboy21","id":1361287,"avatar_url":"https://avatars.githubusercontent.com/u/1361287?v=3","gravatar_id":"","url":"https://api.github.com/users/woogerboy21","html_url":"https://github.com/woogerboy21","followers_url":"https://api.github.com/users/woogerboy21/followers","following_url":"https://api.github.com/users/woogerboy21/following{/other_user}","gists_url":"https://api.github.com/users/woogerboy21/gists{/gist_id}","starred_url":"https://api.github.com/users/woogerboy21/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/woogerboy21/subscriptions","organizations_url":"https://api.github.com/users/woogerboy21/orgs","repos_url":"https://api.github.com/users/woogerboy21/repos","events_url":"https://api.github.com/users/woogerboy21/events{/privacy}","received_events_url":"https://api.github.com/users/woogerboy21/received_events","type":"User","site_admin":false},"created_at":"2016-12-18T14:17:18Z","updated_at":"2016-12-18T14:17:18Z","body":"@Daenyth My original thought was to update the ban table with a column that something like \"end_time\" and then when a ban is placed the server calculates when the ban would end and place the date/time in the new column.  Then on login we could do a much simpler query like check for current banlist of user that is newer than end_time = now() and if results return then user is still banned.  Of course the code would have to also include the extracting of reasoning to pass back to the client (assuming we still wanted to do so) but I think you get the idea."}}
{"actor":{"display_login":"woogerboy21"}}