Page 2 of 2

Re: MySQL Get records that have 3+ specific related records

Posted: Wed Oct 19, 2011 3:09 am
by batfastad
My original query, returns 100 results in about 0.02s

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`
EXPLAIN output...

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 filesort
The newer query, returns 100 results in about 0.3s

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`
EXPLAIN output...

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 	
Indexes...

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 	
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

Re: MySQL Get records that have 3+ specific related records

Posted: Wed Oct 19, 2011 3:15 am
by VladSun
Did you try using composite PK for company_tag table? And remove the DISTINCT also.

Re: MySQL Get records that have 3+ specific related records

Posted: Wed Oct 19, 2011 3:20 am
by batfastad
Just tried removing the DISTINCT and it made no difference.

I've not tried a composite PK. But there are composite normal indexes on company_id+tag_id and also the other way round tag_id+company_id

Cheers, B

Re: MySQL Get records that have 3+ specific related records

Posted: Wed Oct 19, 2011 3:24 pm
by VladSun
Well, my Explain query output (in case using composite PK) is:

Code: Select all

1, SIMPLE, company, ALL, PRIMARY, NULL, NULL, NULL, 4, Using filesort
1, SIMPLE, company_tag, ref, PRIMARY, PRIMARY, 4, test.company.id, 1, Using where; Using index
PS:

Code: Select all

CREATE TABLE `company` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

Code: Select all

CREATE TABLE `company_tag` (
  `company_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`company_id`,`tag_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Re: MySQL Get records that have 3+ specific related records

Posted: Sun Oct 23, 2011 7:53 pm
by batfastad
If I already have a composite index on company_id-tag_id (and the other way round... tag_id-company_id)
Would it theoretically improve speed if I made one of those indexes the PK instead of my current autoincrement field?

If there's already a composite index then shouldn't it get used? Is a primary key index faster than a normal index?

There are some places in the application where my autoincrement field is currently used so I can't get rid of it that easily. Although I do like the idea of getting rid of that field once I've re-written some other queries ;)

Looks like I'll be sticking with my INNER JOIN on a derived table at the moment though. The query is just about fast enough for our internal use. I was just wondering if there was a better syntax I should be using in case I wanted to roll it out onto our websites.

Cheers, B