{"action":"created","issue":{"url":"https://api.github.com/repos/centreon/centreon/issues/3595","labels_url":"https://api.github.com/repos/centreon/centreon/issues/3595/labels{/name}","comments_url":"https://api.github.com/repos/centreon/centreon/issues/3595/comments","events_url":"https://api.github.com/repos/centreon/centreon/issues/3595/events","html_url":"https://github.com/centreon/centreon/issues/3595","id":97050510,"number":3595,"title":"Extremely slow SQL query - centreonACL.class.php - getHostAclConf line 1521","user":{"login":"centreon","id":1890152,"avatar_url":"https://avatars.githubusercontent.com/u/1890152?v=3","gravatar_id":"","url":"https://api.github.com/users/centreon","html_url":"https://github.com/centreon","followers_url":"https://api.github.com/users/centreon/followers","following_url":"https://api.github.com/users/centreon/following{/other_user}","gists_url":"https://api.github.com/users/centreon/gists{/gist_id}","starred_url":"https://api.github.com/users/centreon/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/centreon/subscriptions","organizations_url":"https://api.github.com/users/centreon/orgs","repos_url":"https://api.github.com/users/centreon/repos","events_url":"https://api.github.com/users/centreon/events{/privacy}","received_events_url":"https://api.github.com/users/centreon/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/centreon/centreon/labels/bug","name":"bug","color":"fc2929"}],"state":"open","locked":false,"assignee":{"login":"mtoufik","id":11309826,"avatar_url":"https://avatars.githubusercontent.com/u/11309826?v=3","gravatar_id":"","url":"https://api.github.com/users/mtoufik","html_url":"https://github.com/mtoufik","followers_url":"https://api.github.com/users/mtoufik/followers","following_url":"https://api.github.com/users/mtoufik/following{/other_user}","gists_url":"https://api.github.com/users/mtoufik/gists{/gist_id}","starred_url":"https://api.github.com/users/mtoufik/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mtoufik/subscriptions","organizations_url":"https://api.github.com/users/mtoufik/orgs","repos_url":"https://api.github.com/users/mtoufik/repos","events_url":"https://api.github.com/users/mtoufik/events{/privacy}","received_events_url":"https://api.github.com/users/mtoufik/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/centreon/centreon/milestones/21","html_url":"https://github.com/centreon/centreon/milestones/Centreon-2.7","labels_url":"https://api.github.com/repos/centreon/centreon/milestones/21/labels","id":1223533,"number":21,"title":"Centreon-2.7","description":"","creator":{"login":"centreon","id":1890152,"avatar_url":"https://avatars.githubusercontent.com/u/1890152?v=3","gravatar_id":"","url":"https://api.github.com/users/centreon","html_url":"https://github.com/centreon","followers_url":"https://api.github.com/users/centreon/followers","following_url":"https://api.github.com/users/centreon/following{/other_user}","gists_url":"https://api.github.com/users/centreon/gists{/gist_id}","starred_url":"https://api.github.com/users/centreon/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/centreon/subscriptions","organizations_url":"https://api.github.com/users/centreon/orgs","repos_url":"https://api.github.com/users/centreon/repos","events_url":"https://api.github.com/users/centreon/events{/privacy}","received_events_url":"https://api.github.com/users/centreon/received_events","type":"User","site_admin":false},"open_issues":106,"closed_issues":0,"state":"open","created_at":"2015-07-24T08:03:35Z","updated_at":"2015-09-08T14:55:23Z","due_on":"2015-11-02T23:00:00Z","closed_at":null},"comments":2,"created_at":"2014-06-18T19:05:36Z","updated_at":"2015-09-08T14:58:08Z","closed_at":null,"body":"---\n\n\nAuthor Name: **Florent Peterschmitt** (Florent Peterschmitt)\nOriginal Redmine Issue: 5632, https://forge.centreon.com/issues/5632\nOriginal Date: 2014-06-18\n\n\n---\n\nHi,\r\n\r\nI didnt fount anything related to this problem.\r\n\r\nThis problem happens when visualizing or creating a new Service Group in the Configuration.\r\n\r\nThe page does'nt respond quick, and for a normal use, I can say it never repond.\r\n\r\n\r\nUssuing a \"show full processlist\" in mysql gives me this query:\r\n\r\n```\r\nSELECT host.host_id, host.host_name FROM host  WHERE  host_activate = '1' AND host_register = '1' AND EXISTS (SELECT * FROM host_service_relation, hostgroup_relation WHERE (host_service_relation.host_host_id = host.host_id AND host_service_relation.service_service_id IS NOT NULL) OR (host.host_id = hostgroup_relation.host_host_id AND hostgroup_relation.hostgroup_hg_id = host_service_relation.hostgroup_hg_id AND host_service_relation.service_service_id IS NOT NULL)) ORDER BY host.host_name;\r\n\n```\r\n\r\nPlease see the following informations:\r\n\r\n```\r\nmysql> select count(*) from host;\r\n+----------+\r\n| count(*) |\r\n+----------+\r\n|     6052 |\r\n+----------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select count(*) from host_service_relation;\r\n+----------+\r\n| count(*) |\r\n+----------+\r\n|     6210 |\r\n+----------+\r\n1 row in set (0.01 sec)\r\n\r\nmysql> select count(*) hostgroup_relation;\r\n+--------------------+\r\n| hostgroup_relation |\r\n+--------------------+\r\n|                  1 |\r\n+--------------------+\r\n1 row in set (0.00 sec)\r\n\r\nSimulate an only query by setting host_id at... say 37:\r\n\r\nmysql> SELECT * FROM host_service_relation, hostgroup_relation WHERE (host_service_relation.host_host_id = 37 AND host_service_relation.service_service_id IS NOT NULL) OR (37 = hostgroup_relation.host_host_id AND hostgroup_relation.hostgroup_hg_id = host_service_relation.hostgroup_hg_id AND host_service_relation.service_service_id IS NOT NULL)\r\n    -> ;\r\n+--------+-----------------+--------------+--------------------+--------------------+--------+-----------------+--------------+\r\n| hsr_id | hostgroup_hg_id | host_host_id | servicegroup_sg_id | service_service_id | hgr_id | hostgroup_hg_id | host_host_id |\r\n+--------+-----------------+--------------+--------------------+--------------------+--------+-----------------+--------------+\r\n|    854 |             377 |         NULL |               NULL |                892 |  47536 |             377 |           37 |\r\n+--------+-----------------+--------------+--------------------+--------------------+--------+-----------------+--------------+\r\n1 row in set (14.63 sec)\r\n\r\n\r\nFrom www/class/centreonACL.class.php line 1521, Centreon 2.5.1\r\n\r\nmysql> SELECT host.host_id, host.host_name FROM host  WHERE  host_activate = '1' AND host_register = '1' AND EXISTS (SELECT * FROM host_service_relation, hostgroup_relation WHERE (host_service_relation.host_host_id = host.host_id AND host_service_relation.service_service_id IS NOT NULL) OR (host.host_id = hostgroup_relation.host_host_id AND hostgroup_relation.hostgroup_hg_id = host_service_relation.hostgroup_hg_id AND host_service_relation.service_service_id IS NOT NULL)) ORDER BY host.host_name;\r\n\r\nWe have 6052 hosts, so 6052 * 14 seconds... 6052 * 14 / 60 / 60 = 23 hours!\r\n\r\n23 hours on i3 3°gen, 2GB RAM 1333MHz, CentOS 6.5.\r\n\n```\n\n\n"},"comment":{"url":"https://api.github.com/repos/centreon/centreon/issues/comments/138592564","html_url":"https://github.com/centreon/centreon/issues/3595#issuecomment-138592564","issue_url":"https://api.github.com/repos/centreon/centreon/issues/3595","id":138592564,"user":{"login":"leoncx","id":3930276,"avatar_url":"https://avatars.githubusercontent.com/u/3930276?v=3","gravatar_id":"","url":"https://api.github.com/users/leoncx","html_url":"https://github.com/leoncx","followers_url":"https://api.github.com/users/leoncx/followers","following_url":"https://api.github.com/users/leoncx/following{/other_user}","gists_url":"https://api.github.com/users/leoncx/gists{/gist_id}","starred_url":"https://api.github.com/users/leoncx/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/leoncx/subscriptions","organizations_url":"https://api.github.com/users/leoncx/orgs","repos_url":"https://api.github.com/users/leoncx/repos","events_url":"https://api.github.com/users/leoncx/events{/privacy}","received_events_url":"https://api.github.com/users/leoncx/received_events","type":"User","site_admin":false},"created_at":"2015-09-08T14:58:08Z","updated_at":"2015-09-08T14:58:08Z","body":"Look to following fix; #498\r\n"}}