Code: Select all
SELECT `companies`.`company_id`, `companies`.`company_name`, `companies`.`geo`, COUNT(DISTINCT `tags_data`.`tag_id`) AS `tags_found`
FROM `companies`
INNER JOIN `tags_data` ON `tags_data`.`company_id`=`companies`.`company_id` AND `tags_data`.`tag_id` IN (718, 724, 747)
GROUP BY `companies`.`company_id`, `companies`.`company_name`
HAVING `tags_found`=3
ORDER BY `companies`.`company_name`Code: Select all
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 100 Using temporary; Using filesort
1 PRIMARY companies eq_ref PRIMARY,company_id_geo PRIMARY 3 require_tags.company_id 1
2 DERIVED tags_data ALL tags_companies NULL NULL NULL 112129 Using where; Using temporary; Using filesortCode: Select all
SELECT `companies`.`company_id`, `companies`.`company_name`, `companies`.`geo`, COUNT(DISTINCT `tags_data`.`tag_id`) AS `tags_found`
FROM `companies`
INNER JOIN `tags_data` ON `tags_data`.`company_id`=`companies`.`company_id` AND `tags_data`.`tag_id` IN (718, 724, 747)
GROUP BY `companies`.`company_id`, `companies`.`company_name`
HAVING `tags_found`=3
ORDER BY `companies`.`company_name`Code: Select all
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tags_data range tags_companies,companies_tags tags_companies 4 NULL 26626 Using where; Using index; Using temporary; Using filesort
1 SIMPLE companies eq_ref PRIMARY,company_id_geo PRIMARY 3 dwcintranet.tags_data.company_id 1 Code: Select all
SHOW INDEXES FROM companies
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
companies 0 PRIMARY 1 company_id A 39929 NULL NULL BTREE
companies 1 geo 1 geo A 137 NULL NULL YES BTREE
companies 1 company_name 1 company_name A 39929 NULL NULL YES BTREE
companies 1 geo_company_id 1 geo A 137 NULL NULL YES BTREE
companies 1 geo_company_id 2 company_id A 39929 NULL NULL BTREE
companies 1 company_id_geo 1 company_id A 39929 NULL NULL BTREE
companies 1 company_id_geo 2 geo A 39929 NULL NULL YES BTREE
SHOW INDEXES FROM tags_data
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
tags_data 0 PRIMARY 1 tagdata_id A 112129 NULL NULL BTREE
tags_data 1 tags_companies 1 tag_id A 709 NULL NULL YES BTREE
tags_data 1 tags_companies 2 company_id A 112129 NULL NULL YES BTREE
tags_data 1 companies_tags 1 company_id A 37376 NULL NULL YES BTREE
tags_data 1 companies_tags 2 tag_id A 112129 NULL NULL YES BTREE Cheers, B