MySQL not using index

[asmith]

Hello,

 

Using MySQL 5.5,

 

It is actually so simple, I think I'm missing something in front of my eyes. I have these 2 tables:

 

CREATE TABLE IF NOT EXISTS `records_members` (
  `id_record` mediumint( unsigned NOT NULL DEFAULT '0',
  `id_member` mediumint( unsigned NOT NULL DEFAULT '0',
  `type` tinyint(4) unsigned NOT NULL DEFAULT '0',
  KEY `id_record` (`id_record`),
  KEY `id_member` (`id_member`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `records` (
  `id_record` mediumint( unsigned NOT NULL AUTO_INCREMENT,
  `id_group`  mediumint( unsigned NOT NULL DEFAULT '0',
  `record` float NOT NULL,
  `downloads` mediumint( unsigned NOT NULL DEFAULT '0',
  `posted_time` int(10) unsigned NOT NULL DEFAULT '0',
  `is_activated` tinyint(4) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_record`),
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

 

I want to get all the record by a specific member:

EXPLAIN SELECT rm.id_member, COUNT(DISTINCT(r.id_group)) AS num
FROM records_members AS rm
   INNER JOIN records AS r ON r.id_record = rm.id_record
WHERE rm.id_member IN (95) AND rm.type = 0
GROUP BY rm.id_member

 

This works fine as it is supposed to:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	rm 	ref 	id_record,id_member 	id_member 	4 	const,const 	143 	 
1 	SIMPLE 	r 	eq_ref 	PRIMARY 	PRIMARY 	3 	test.rm.id_record 	1 	 

 

Same query, if I just request for much more members, it will does a full table scan:

EXPLAIN SELECT rm.id_member, COUNT(DISTINCT(r.id_group)) AS num
FROM records_members AS rm
   INNER JOIN records AS r ON r.id_record = rm.id_record
WHERE rm.id_member IN (95, 956, 739, 363, 204, 920, 286, 40, 332, 1239, 989, 443, 347, 1122, 11, 75, 2238, 4, 1073, 5, 193, 591, 880, 361, 850, 499, 2213, 592, 133, 1802, 1702, 20, 2111, 1929, 63, 868, 698, 1923, 22, 1881, 507, 143, 785, 2, 586, 2172, 433, 827, 2216, 1987) AND crp.type = 0
GROUP BY rm.id_member

 

EXPLAIN:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	rm 	ALL 	id_record,id_member 	NULL 	NULL 	NULL 	7701 	Using where; Using filesort
1 	SIMPLE 	r 	eq_ref 	PRIMARY 	PRIMARY 	3 	test.rm.id_record 	1 	  

[Christian F.]

Provided that the queries you've posted above are exactly the same as you're using, I suspect the problem lies with the use of AND crp.type = 0 in the WHERE clause of the second query. So, not quite the same query after all.

[asmith]

That's my typo mistake when I was typing the question. That is rm.type.

[fenway]

MySQL has decided that, with so many index lookups, it's faster to a do a table scan.  What percentage of the rows do these values represent? Are you statistics up to date?