MySQL Bugs: #8139: subselect index not used for "in" but used for "="

Bug #8139	subselect index not used for "in" but used for "="
Submitted:	26 Jan 2005 7:49	Modified:	2 Nov 2008 18:26
Reporter:	John Swapceinski	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.8	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
query with subselect very slow when the main query uses "in", but fast when the main query uses "=".

How to repeat:
I have a query including a subselect in which the subselect returns a single row.  If I use = (equals) in the main select, the query returns very quickly:

mysql> select * from Ratings where rid = (select rid from Ratings where rid=1111111);
1 row in set (0.00 sec)

If I change the main select's "=" (equals) to "in", I get:

mysql> select * from Ratings where rid in (select rid from Ratings where rid=1111111);
1 row in set (20.68 sec)    <----SLOW

If I do an explain on the queries, I get these results:

mysql> explain select * from Ratings where rid = (select rid from Ratings where rid=1111111);
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | Ratings | const | PRIMARY       | PRIMARY |       4 | const |    1 |             |
|  2 | SUBQUERY    | Ratings | const | PRIMARY       | PRIMARY |       4 |       |    1 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.17 sec)

mysql> explain select * from Ratings where rid in (select rid from Ratings where rid=1111111);
+----+--------------------+---------+-------+---------------+---------+---------+-------+---------+-------------+
| id | select_type        | table   | type  | possible_keys | key     | key_len | ref   | rows    | Extra       |
+----+--------------------+---------+-------+---------------+---------+---------+-------+---------+-------------+
|  1 | PRIMARY            | Ratings | ALL   | NULL          | NULL    |    NULL | NULL  | 3114176 | Using where |
|  2 | DEPENDENT SUBQUERY | Ratings | const | PRIMARY       | PRIMARY |       4 | const |       1 | Using index |
+----+--------------------+---------+-------+---------------+---------+---------+-------+---------+-------------+
2 rows in set (0.00 sec)

Hi,

Thank you for the report.
Culd you provide structure of the table Ratings?

Here are the relevant fields for the queries I provided:

CREATE TABLE `Ratings` (
  `RID` int(11) NOT NULL auto_increment,
   PRIMARY KEY  (`RID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Here is the entire table, just in case it's relevant:

CREATE TABLE `Ratings` (
  `RID` int(11) NOT NULL auto_increment,
  `TID` int(11) NOT NULL default '0',
  `PID` int(11) default NULL,
  `REasy` smallint(6) NOT NULL default '0',
  `RHelpful` smallint(6) NOT NULL default '0',
  `RClarity` smallint(6) NOT NULL default '0',
  `RDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `RComments` text NOT NULL,
  `RSession` varchar(40) default NULL,
  `RClass` varchar(15) NOT NULL default '',
  `RIP` varchar(30) NOT NULL default '',
  `RStatus` smallint(6) NOT NULL default '0',
  `RType` smallint(6) NOT NULL default '0',
  `RCommentsOrig` text,
  `RErrorMsg` varchar(80) default NULL,
  `AdminPID` int(11) default NULL,
  `RComputerID` varchar(50) default NULL,
  `RClassOrig` varchar(10) default NULL,
  `RReferer` varchar(100) default NULL,
  `RUserAgent` varchar(100) default NULL,
  `RInterest` smallint(6) default NULL,
  PRIMARY KEY  (`RID`),
  KEY `idx_rdate` (`RDate`),
  KEY `idx_rpid` (`PID`),
  KEY `idx_rsession` (`RSession`),
  KEY `idx_tid_rstatus` (`TID`,`RStatus`),
  KEY `idx_ratings_adminpid` (`AdminPID`),
  KEY `idx_rstatus_rid` (`RStatus`,`RID`),
  KEY `idx_rcomputerid` (`RComputerID`),
  KEY `idx_rclass` (`RClass`),
  KEY `idx_rip` (`RIP`),
  KEY `idx_ruseragent` (`RUserAgent`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I think this is the same bug as Bug #9021

It probably is the same bug, yes.
I have the same problem, where I use PK ids in the subquery after the IN..
Is it possible to rewrite these single-table queries as joins too, which seemed to be the implied workaround for this problem when it involved multiple tables?
If so, how would I do it for the example above??

Kind regards
Spinner

Duplicate of bug #9021.