{"action":"created","issue":{"url":"https://api.github.com/repos/src-d/go-mysql-server/issues/324","repository_url":"https://api.github.com/repos/src-d/go-mysql-server","labels_url":"https://api.github.com/repos/src-d/go-mysql-server/issues/324/labels{/name}","comments_url":"https://api.github.com/repos/src-d/go-mysql-server/issues/324/comments","events_url":"https://api.github.com/repos/src-d/go-mysql-server/issues/324/events","html_url":"https://github.com/src-d/go-mysql-server/issues/324","id":348495938,"node_id":"MDU6SXNzdWUzNDg0OTU5Mzg=","number":324,"title":"performance issue - indexed queries are much slower than non indexed","user":{"login":"kuba--","id":4056521,"node_id":"MDQ6VXNlcjQwNTY1MjE=","avatar_url":"https://avatars1.githubusercontent.com/u/4056521?v=4","gravatar_id":"","url":"https://api.github.com/users/kuba--","html_url":"https://github.com/kuba--","followers_url":"https://api.github.com/users/kuba--/followers","following_url":"https://api.github.com/users/kuba--/following{/other_user}","gists_url":"https://api.github.com/users/kuba--/gists{/gist_id}","starred_url":"https://api.github.com/users/kuba--/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kuba--/subscriptions","organizations_url":"https://api.github.com/users/kuba--/orgs","repos_url":"https://api.github.com/users/kuba--/repos","events_url":"https://api.github.com/users/kuba--/events{/privacy}","received_events_url":"https://api.github.com/users/kuba--/received_events","type":"User","site_admin":false},"labels":[{"id":839045699,"node_id":"MDU6TGFiZWw4MzkwNDU2OTk=","url":"https://api.github.com/repos/src-d/go-mysql-server/labels/performance","name":"performance","color":"ffc97f","default":false}],"state":"open","locked":false,"assignee":{"login":"kuba--","id":4056521,"node_id":"MDQ6VXNlcjQwNTY1MjE=","avatar_url":"https://avatars1.githubusercontent.com/u/4056521?v=4","gravatar_id":"","url":"https://api.github.com/users/kuba--","html_url":"https://github.com/kuba--","followers_url":"https://api.github.com/users/kuba--/followers","following_url":"https://api.github.com/users/kuba--/following{/other_user}","gists_url":"https://api.github.com/users/kuba--/gists{/gist_id}","starred_url":"https://api.github.com/users/kuba--/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kuba--/subscriptions","organizations_url":"https://api.github.com/users/kuba--/orgs","repos_url":"https://api.github.com/users/kuba--/repos","events_url":"https://api.github.com/users/kuba--/events{/privacy}","received_events_url":"https://api.github.com/users/kuba--/received_events","type":"User","site_admin":false},"assignees":[{"login":"kuba--","id":4056521,"node_id":"MDQ6VXNlcjQwNTY1MjE=","avatar_url":"https://avatars1.githubusercontent.com/u/4056521?v=4","gravatar_id":"","url":"https://api.github.com/users/kuba--","html_url":"https://github.com/kuba--","followers_url":"https://api.github.com/users/kuba--/followers","following_url":"https://api.github.com/users/kuba--/following{/other_user}","gists_url":"https://api.github.com/users/kuba--/gists{/gist_id}","starred_url":"https://api.github.com/users/kuba--/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kuba--/subscriptions","organizations_url":"https://api.github.com/users/kuba--/orgs","repos_url":"https://api.github.com/users/kuba--/repos","events_url":"https://api.github.com/users/kuba--/events{/privacy}","received_events_url":"https://api.github.com/users/kuba--/received_events","type":"User","site_admin":false}],"milestone":null,"comments":1,"created_at":"2018-08-07T21:35:34Z","updated_at":"2018-08-09T11:01:33Z","closed_at":null,"author_association":"MEMBER","body":"Non indexed queries like:\r\n```\r\nSELECT count(*) FROM commits WHERE  commit_author_email='existing email';\r\n\r\nSELECT count(*) FROM commits WHERE  NOT (commit_author_email='existing email');\r\n```\r\nreturn in ~ 1sec. (tested on gitbase repo where commits table has 580 rows) 180 and 394 rows.\r\n\r\nAfter we create an index (for both drivers):\r\n```\r\nCREATE INDEX email_idx ON commits USING pilosalib (commit_author_email);\r\n```\r\nAbove queries take: ~5sec, ~10sec.\r\n\r\nIt's much slower than queries without any index.\r\n\r\nIt's not a problem of original queries with `group by` or `count`. It's also not a problem with `NOT` operator (which doesn't exist on pilosa). Pilosa and mapping are pretty fast. But because we have to iterate all the time calling `Next`it causes a performance bottleneck.\r\nPilosa can return (even for simulated NOT operator) 500 columns in couple ms."},"comment":{"url":"https://api.github.com/repos/src-d/go-mysql-server/issues/comments/411720258","html_url":"https://github.com/src-d/go-mysql-server/issues/324#issuecomment-411720258","issue_url":"https://api.github.com/repos/src-d/go-mysql-server/issues/324","id":411720258,"node_id":"MDEyOklzc3VlQ29tbWVudDQxMTcyMDI1OA==","user":{"login":"ajnavarro","id":1196465,"node_id":"MDQ6VXNlcjExOTY0NjU=","avatar_url":"https://avatars3.githubusercontent.com/u/1196465?v=4","gravatar_id":"","url":"https://api.github.com/users/ajnavarro","html_url":"https://github.com/ajnavarro","followers_url":"https://api.github.com/users/ajnavarro/followers","following_url":"https://api.github.com/users/ajnavarro/following{/other_user}","gists_url":"https://api.github.com/users/ajnavarro/gists{/gist_id}","starred_url":"https://api.github.com/users/ajnavarro/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ajnavarro/subscriptions","organizations_url":"https://api.github.com/users/ajnavarro/orgs","repos_url":"https://api.github.com/users/ajnavarro/repos","events_url":"https://api.github.com/users/ajnavarro/events{/privacy}","received_events_url":"https://api.github.com/users/ajnavarro/received_events","type":"User","site_admin":false},"created_at":"2018-08-09T11:01:33Z","updated_at":"2018-08-09T11:01:33Z","author_association":"MEMBER","body":"Ideally, if the Index result count is almost the same as the total of elements on the table, use the index is useless. Maybe we should get first the number of results that that index is going to return. If the amount of elements is almost the total amount of elements on the indexed tables, it's better to do a full scan and filter a few elements. WDYT?"}}
{"actor":{"display_login":"ajnavarro"}}