Here you go - https://gist.github.com/korekhov/7ad0fddae6e330f1655305c626bb4808

There's also a thread for this issue - https://groups.google.com/forum/#!topic/foreman-users/oT68AXrskV0

The slowest request is:

2016-11-01 13:01:23 e58c75eb [sql] [D] SQL (79753.0ms) SELECT DISTINCT `hosts`.`id` FROM `hosts` LEFT OUTER JOIN `host_status` ON `host_status`.`host_id` = `hosts`.`id` LEFT OUTER JOIN `compute_resources` ON `compute_resources`.`id` = `hosts`.`compute_resource_id` LEFT OUTER JOIN `hostgroups` ON `hostgroups`.`id` = `hosts`.`hostgroup_id` LEFT OUTER JOIN `operatingsystems` ON `operatingsystems`.`id` = `hosts`.`operatingsystem_id` LEFT OUTER JOIN `nics` ON `nics`.`host_id` = `hosts`.`id` LEFT OUTER JOIN `tokens` ON `tokens`.`host_id` = `hosts`.`id` LEFT OUTER JOIN `models` ON `models`.`id` = `hosts`.`model_id` LEFT OUTER JOIN `nics` `primary_interfaces_hosts_join` ON `primary_interfaces_hosts_join`.`host_id` = `hosts`.`id` AND `primary_interfaces_hosts_join`.`primary` = 1 LEFT OUTER JOIN `domains` ON `domains`.`id` = `primary_interfaces_hosts_join`.`domain_id` LEFT OUTER JOIN `realms` ON `realms`.`id` = `hosts`.`realm_id` LEFT OUTER JOIN `environments` ON `environments`.`id` = `hosts`.`environment_id` LEFT OUTER JOIN `architectures` ON `architectures`.`id` = `hosts`.`architecture_id` LEFT OUTER JOIN `images` ON `images`.`id` = `hosts`.`image_id` LEFT OUTER JOIN `nics` `primary_interfaces_hosts` ON `primary_interfaces_hosts`.`host_id` = `hosts`.`id` AND `primary_interfaces_hosts`.`primary` = 1 LEFT OUTER JOIN `nics` `primary_interfaces_hosts_join_2` ON `primary_interfaces_hosts_join_2`.`host_id` = `hosts`.`id` AND `primary_interfaces_hosts_join_2`.`primary` = 1 LEFT OUTER JOIN `subnets` ON `subnets`.`id` = `primary_interfaces_hosts_join_2`.`subnet_id` AND `subnets`.`type` = 'Subnet::Ipv4' LEFT OUTER JOIN `nics` `primary_interfaces_hosts_join_3` ON `primary_interfaces_hosts_join_3`.`host_id` = `hosts`.`id` AND `primary_interfaces_hosts_join_3`.`primary` = 1 LEFT OUTER JOIN `subnets` `subnet6s_hosts` ON `subnet6s_hosts`.`id` = `primary_interfaces_hosts_join_3`.`subnet6_id` AND `subnet6s_hosts`.`type` = 'Subnet::Ipv6' LEFT OUTER JOIN `nics` `provision_interfaces_hosts` ON `provision_interfaces_hosts`.`host_id` = `hosts`.`id` AND `provision_interfaces_hosts`.`provision` = 1 LEFT OUTER JOIN `discovery_rules` ON `discovery_rules`.`id` = `hosts`.`discovery_rule_id` LEFT OUTER JOIN `host_salt_modules` ON `host_salt_modules`.`host_id` = `hosts`.`id` LEFT OUTER JOIN `salt_modules` ON `salt_modules`.`id` = `host_salt_modules`.`salt_module_id` LEFT OUTER JOIN `salt_environments` ON `salt_environments`.`id` = `hosts`.`salt_environment_id` LEFT OUTER JOIN `smart_proxies` ON `smart_proxies`.`id` = `hosts`.`salt_proxy_id` WHERE `hosts`.`type` IN ('Host::Managed') AND ((`hosts`.`name` LIKE '%test%' OR `hosts`.`comment` LIKE '%test%' OR `models`.`name` LIKE '%test%' OR `hostgroups`.`name` LIKE '%test%' OR `hostgroups`.`title` LIKE '%test%' OR `hostgroups`.`title` LIKE '%test%' OR `domains`.`name` LIKE '%test%' OR `realms`.`name` LIKE '%test%' OR `environments`.`name` LIKE '%test%' OR `architectures`.`name` LIKE '%test%' OR `compute_resources`.`name` LIKE '%test%' OR `images`.`name` LIKE '%test%' OR `operatingsystems`.`name` LIKE '%test%' OR `operatingsystems`.`description` LIKE '%test%' OR `operatingsystems`.`title` LIKE '%test%' OR `operatingsystems`.`major` LIKE '%test%' OR `operatingsystems`.`minor` LIKE '%test%' OR `nics`.`ip` LIKE '%test%' OR `nics`.`ip` LIKE '%test%' OR `nics`.`mac` LIKE '%test%' OR `subnets`.`network` LIKE '%test%' OR `subnets`.`name` LIKE '%test%' OR `subnets`.`network` LIKE '%test%' OR `subnets`.`name` LIKE '%test%' OR `hosts`.`uuid` LIKE '%test%' OR `nics`.`mac` LIKE '%test%' OR `operatingsystems`.`name` LIKE '%test%' OR `operatingsystems`.`description` LIKE '%test%' OR `operatingsystems`.`title` LIKE '%test%' OR `operatingsystems`.`major` LIKE '%test%' OR `operatingsystems`.`minor` LIKE '%test%' OR `discovery_rules`.`name` LIKE '%test%' OR `salt_modules`.`name` LIKE '%test%' OR `salt_environments`.`name` LIKE '%test%' OR `smart_proxies`.`name` LIKE '%test%')) ORDER BY `hosts`.`name` ASC LIMIT 20 OFFSET 0

my guess is that now we search on both hosts and nic tables.

Could you run that query with explain?

thanks

One thing I forgot to mention. This is the same DB that was migrated from 1.7.1 to 1.13.1 and this fix applied to it - https://groups.google.com/forum/#!msg/foreman-users/qkmfeP2R-ss/vUE3FJHsBQAJ

Maybe I need to reindex? How, if so?

BTW, "explain analyze" fail for me with this kind of error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'analyze SELECT DISTINCT `hosts`.`id` FROM `hosts` LEFT OUTER JOIN `host_status` ' at line 1

If I just run "explain". I get this (if that helps at all):

1 SIMPLE hosts ALL PRIMARY,index_hosts_on_name,index_hosts_on_last_report,index_hosts_on_installed_at,host_arch_id_ix,host_os_id_ix,host_env_id_ix,host_medium_id_ix,host_group_id_ix,index_hosts_on_certname,index_hosts_on_type,hosts_compute_resource_id_fk,hosts_image_id_fk,hosts_model_id_fk,hosts_ptable_id_fk,hosts_puppet_ca_proxy_id_fk,hosts_puppet_proxy_id_fk,hosts_location_id_fk,hosts_organization_id_fk,index_hosts_on_compute_profile_id,hosts_realms_id_fk NULL NULL NULL 1620 Using where; Using temporary; Using filesort
1 SIMPLE host_status ref index_host_status_on_host_id index_host_status_on_host_id 4 foreman.hosts.id 1 Using index; Distinct
1 SIMPLE compute_resources eq_ref PRIMARY PRIMARY 4 foreman.hosts.compute_resource_id 1 Distinct
1 SIMPLE hostgroups eq_ref PRIMARY PRIMARY 4 foreman.hosts.hostgroup_id 1 Distinct
1 SIMPLE operatingsystems eq_ref PRIMARY PRIMARY 4 foreman.hosts.operatingsystem_id 1 Distinct
1 SIMPLE nics ref index_by_host index_by_host 5 foreman.hosts.id 20 Distinct
1 SIMPLE tokens ref index_tokens_on_host_id index_tokens_on_host_id 5 foreman.hosts.id 1 Using index; Distinct
1 SIMPLE models eq_ref PRIMARY PRIMARY 4 foreman.hosts.model_id 1 Distinct
1 SIMPLE primary_interfaces_hosts_join ref index_by_host index_by_host 5 foreman.hosts.id 20 Using where; Distinct
1 SIMPLE domains eq_ref PRIMARY PRIMARY 4 foreman.primary_interfaces_hosts_join.domain_id 1 Distinct
1 SIMPLE realms eq_ref PRIMARY PRIMARY 4 foreman.hosts.realm_id 1 Distinct
1 SIMPLE environments eq_ref PRIMARY PRIMARY 4 foreman.hosts.environment_id 1 Distinct
1 SIMPLE architectures eq_ref PRIMARY PRIMARY 4 foreman.hosts.architecture_id 1 Distinct
1 SIMPLE images eq_ref PRIMARY PRIMARY 4 foreman.hosts.image_id 1 Distinct
1 SIMPLE primary_interfaces_hosts ref index_by_host index_by_host 5 foreman.hosts.id 20 Using where; Distinct
1 SIMPLE primary_interfaces_hosts_join_2 ref index_by_host index_by_host 5 foreman.hosts.id 20 Using where; Distinct
1 SIMPLE subnets eq_ref PRIMARY,index_subnets_on_type PRIMARY 4 foreman.primary_interfaces_hosts_join_2.subnet_id 1 Using where; Distinct
1 SIMPLE primary_interfaces_hosts_join_3 ref index_by_host index_by_host 5 foreman.hosts.id 20 Using where; Distinct
1 SIMPLE subnet6s_hosts eq_ref PRIMARY,index_subnets_on_type PRIMARY 4 foreman.primary_interfaces_hosts_join_3.subnet6_id 1 Using where; Distinct
1 SIMPLE provision_interfaces_hosts ref index_by_host index_by_host 5 foreman.hosts.id 20 Using where; Distinct
1 SIMPLE discovery_rules eq_ref PRIMARY PRIMARY 4 foreman.hosts.discovery_rule_id 1 Distinct
1 SIMPLE host_salt_modules ALL NULL NULL NULL NULL 1 Using where; Distinct; Using join buffer (Block Nested Loop)
1 SIMPLE salt_modules eq_ref PRIMARY PRIMARY 4 foreman.host_salt_modules.salt_module_id 1 Distinct
1 SIMPLE salt_environments eq_ref PRIMARY PRIMARY 4 foreman.hosts.salt_environment_id 1 Distinct
1 SIMPLE smart_proxies eq_ref PRIMARY PRIMARY 4 foreman.hosts.salt_proxy_id 1 Using where; Distinct

Please see "explain extended" outputs I just posted at the request from Tomer Brisker:

https://groups.google.com/d/msg/foreman-users/oT68AXrskV0/XeqYmhQZCgAJ
https://groups.google.com/d/msg/foreman-users/oT68AXrskV0/dcc7ER4ZCgAJ