{"action":"created","issue":{"url":"https://api.github.com/repos/infiniteautomation/ma-core-public/issues/810","repository_url":"https://api.github.com/repos/infiniteautomation/ma-core-public","labels_url":"https://api.github.com/repos/infiniteautomation/ma-core-public/issues/810/labels{/name}","comments_url":"https://api.github.com/repos/infiniteautomation/ma-core-public/issues/810/comments","events_url":"https://api.github.com/repos/infiniteautomation/ma-core-public/issues/810/events","html_url":"https://github.com/infiniteautomation/ma-core-public/issues/810","id":171968091,"number":810,"title":"Core Table Indexes - Add indexes for searchable tables ","user":{"login":"terrypacker","id":3905204,"avatar_url":"https://avatars.githubusercontent.com/u/3905204?v=3","gravatar_id":"","url":"https://api.github.com/users/terrypacker","html_url":"https://github.com/terrypacker","followers_url":"https://api.github.com/users/terrypacker/followers","following_url":"https://api.github.com/users/terrypacker/following{/other_user}","gists_url":"https://api.github.com/users/terrypacker/gists{/gist_id}","starred_url":"https://api.github.com/users/terrypacker/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/terrypacker/subscriptions","organizations_url":"https://api.github.com/users/terrypacker/orgs","repos_url":"https://api.github.com/users/terrypacker/repos","events_url":"https://api.github.com/users/terrypacker/events{/privacy}","received_events_url":"https://api.github.com/users/terrypacker/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/infiniteautomation/ma-core-public/labels/Enhancement","name":"Enhancement","color":"0052cc"}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/infiniteautomation/ma-core-public/milestones/17","html_url":"https://github.com/infiniteautomation/ma-core-public/milestones/2.8.0%20New%20Features","labels_url":"https://api.github.com/repos/infiniteautomation/ma-core-public/milestones/17/labels","id":1573479,"number":17,"title":"2.8.0 New Features","description":"New features for Mango 2.8.0","creator":{"login":"terrypacker","id":3905204,"avatar_url":"https://avatars.githubusercontent.com/u/3905204?v=3","gravatar_id":"","url":"https://api.github.com/users/terrypacker","html_url":"https://github.com/terrypacker","followers_url":"https://api.github.com/users/terrypacker/followers","following_url":"https://api.github.com/users/terrypacker/following{/other_user}","gists_url":"https://api.github.com/users/terrypacker/gists{/gist_id}","starred_url":"https://api.github.com/users/terrypacker/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/terrypacker/subscriptions","organizations_url":"https://api.github.com/users/terrypacker/orgs","repos_url":"https://api.github.com/users/terrypacker/repos","events_url":"https://api.github.com/users/terrypacker/events{/privacy}","received_events_url":"https://api.github.com/users/terrypacker/received_events","type":"User","site_admin":false},"open_issues":78,"closed_issues":35,"state":"open","created_at":"2016-02-09T21:46:12Z","updated_at":"2016-08-18T18:26:11Z","due_on":"2016-08-31T10:00:00Z","closed_at":null},"comments":1,"created_at":"2016-08-18T18:26:11Z","updated_at":"2016-08-18T18:34:05Z","closed_at":null,"body":"With the new RQL endpoints being able to sort and order by any field in a table it has been seen that the queries are quite slow.  Adding indexes to various fields will help.\r\n\r\nTables to review:\r\n\r\ndataPoints\r\ndataSources"},"comment":{"url":"https://api.github.com/repos/infiniteautomation/ma-core-public/issues/comments/240814654","html_url":"https://github.com/infiniteautomation/ma-core-public/issues/810#issuecomment-240814654","issue_url":"https://api.github.com/repos/infiniteautomation/ma-core-public/issues/810","id":240814654,"user":{"login":"jazdw","id":3579645,"avatar_url":"https://avatars.githubusercontent.com/u/3579645?v=3","gravatar_id":"","url":"https://api.github.com/users/jazdw","html_url":"https://github.com/jazdw","followers_url":"https://api.github.com/users/jazdw/followers","following_url":"https://api.github.com/users/jazdw/following{/other_user}","gists_url":"https://api.github.com/users/jazdw/gists{/gist_id}","starred_url":"https://api.github.com/users/jazdw/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jazdw/subscriptions","organizations_url":"https://api.github.com/users/jazdw/orgs","repos_url":"https://api.github.com/users/jazdw/repos","events_url":"https://api.github.com/users/jazdw/events{/privacy}","received_events_url":"https://api.github.com/users/jazdw/received_events","type":"User","site_admin":false},"created_at":"2016-08-18T18:34:05Z","updated_at":"2016-08-18T18:34:05Z","body":"My database has 100,000 points. I am seeing a query time of around 8 seconds on MySQL for the REST call \"/rest/v1/data-points?sort(deviceName,name)&limit(200,0)\"\r\n\r\nAdding an index for deviceName and name didn't seem to help the REST query speed at all.\r\n\r\nIn the MySQL console running \"SELECT SQL_NO_CACHE * FROM mango.datapoints ORDER BY deviceName, name LIMIT 200;\" dropped from 1.125s to 0.015s with the index."}}
{"actor":{"display_login":"jazdw"}}