Re: MySQL Get records that have 3+ specific related records
Posted: Wed Oct 19, 2011 3:09 am
My original query, returns 100 results in about 0.02s
EXPLAIN output...
The newer query, returns 100 results in about 0.3s
EXPLAIN output...
Indexes...
It might be that the way I've written the query is the best method but I just wasn't sure if there was some sort of SQL syntax that I didn't know about that could improve things.
Cheers, B
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