MySQL Forums
Forum List  »  Performance

No response to SELECT with IN clause
Posted by: James Cobban
Date: April 21, 2014 06:39PM

MySQL is hanging up on a query where the subcomponents of the query all seem to work as expected. The whole query is:

SELECT IDIR,Surname,GivenName,BirthD,IDLRBirth FROM tblIR WHERE IDIR IN (SELECT DISTINCT IDIR FROM tblER WHERE IDType=0 AND (IDLREvent=96526 || IDLREvent=95981 || IDLREvent=95701 || IDLREvent=12912 || IDLREvent=96008 || IDLREvent=96009 || IDLREvent=10692 || IDLREvent=95980 || IDLREvent=75204 || IDLREvent=95951 || IDLREvent=12815 || IDLREvent=3784 || IDLREvent=33777 || IDLREvent=96374 || IDLREvent=94456 || IDLREvent=66760 || IDLREvent=18117 || IDLREvent=79123 || IDLREvent=21376 || IDLREvent=96376 || IDLREvent=21411 || IDLREvent=94451 || IDLREvent=20714 || IDLREvent=33896 || IDLREvent=21471 || IDLREvent=80322 || IDLREvent=21487 || IDLREvent=21512 || IDLREvent=21525 || IDLREvent=21530 || IDLREvent=21532 || IDLREvent=21601 || IDLREvent=92640 || IDLREvent=4802 || IDLREvent=96078 || IDLREvent=19660 || IDLREvent=96336 || IDLREvent=33925 || IDLREvent=33805 || IDLREvent=24843 || IDLREvent=21787 || IDLREvent=21796 || IDLREvent=95948 || IDLREvent=20444 || IDLREvent=21837 || IDLREvent=94452 || IDLREvent=21877 || IDLREvent=95950 || IDLREvent=95949 || IDLREvent=96377 || IDLREvent=21882 || IDLREvent=21924 || IDLREvent=93743 || IDLREvent=21935 || IDLREvent=93714 || IDLREvent=21960 || IDLREvent=92647 || IDLREvent=21975 || IDLREvent=20449 || IDLREvent=96529 || IDLREvent=93180 || IDLREvent=93719 || IDLREvent=22013 || IDLREvent=96532 || IDLREvent=93713 || IDLREvent=96527 || IDLREvent=22050 || IDLREvent=39040 || IDLREvent=22083 || IDLREvent=96894 || IDLREvent=25196 || IDLREvent=22132 || IDLREvent=22145 || IDLREvent=22154 || IDLREvent=6144 || IDLREvent=92629 || IDLREvent=22155 || IDLREvent=22157 || IDLREvent=95630 || IDLREvent=93239 || IDLREvent=70696 || IDLREvent=22220 || IDLREvent=22223 || IDLREvent=22229 || IDLREvent=59488 || IDLREvent=22228 || IDLREvent=33926 || IDLREvent=22235 || IDLREvent=33932 || IDLREvent=33797 || IDLREvent=33795 || IDLREvent=96037 || IDLREvent=95929 || IDLREvent=22311 || IDLREvent=96766 || IDLREvent=13750 || IDLREvent=59487 || IDLREvent=33674 || IDLREvent=96036 || IDLREvent=22329 || IDLREvent=96075 || IDLREvent=22338 || IDLREvent=22344 || IDLREvent=22359 || IDLREvent=34261 || IDLREvent=96015 || IDLREvent=96034 || IDLREvent=95996 || IDLREvent=22387 || IDLREvent=22390 || IDLREvent=22391 || IDLREvent=96007 || IDLREvent=22402 || IDLREvent=96528 || IDLREvent=95992 || IDLREvent=95991 || IDLREvent=96035 || IDLREvent=22416 || IDLREvent=22422 || IDLREvent=22423 || IDLREvent=25382 || IDLREvent=59484 || IDLREvent=95994 || IDLREvent=96032 || IDLREvent=22440 || IDLREvent=22441 || IDLREvent=33771 || IDLREvent=33800 || IDLREvent=22489 || IDLREvent=22490 || IDLREvent=95995 || IDLREvent=24667 || IDLREvent=22497 || IDLREvent=96038 || IDLREvent=92639 || IDLREvent=22498 || IDLREvent=96767 || IDLREvent=22500 || IDLREvent=33770 || IDLREvent=96810 || IDLREvent=95595 || IDLREvent=95526 || IDLREvent=92635 || IDLREvent=4607 || IDLREvent=22604 || IDLREvent=34042 || IDLREvent=22665 || IDLREvent=33796 || IDLREvent=33851 || IDLREvent=75232 || IDLREvent=33801 || IDLREvent=22773 || IDLREvent=1486 || IDLREvent=1515 || IDLREvent=94454 || IDLREvent=95947 || IDLREvent=93098 || IDLREvent=39037 || IDLREvent=22820 || IDLREvent=2404 || IDLREvent=95946 || IDLREvent=22824 || IDLREvent=94885 || IDLREvent=23284 || IDLREvent=96010 || IDLREvent=33462 || IDLREvent=23593 || IDLREvent=96142 || IDLREvent=23626 || IDLREvent=94379 || IDLREvent=23632 || IDLREvent=96586 || IDLREvent=93175 || IDLREvent=24045 || IDLREvent=24131 || IDLREvent=20003 || IDLREvent=24196 || IDLREvent=24197 || IDLREvent=19259 || IDLREvent=66365 || IDLREvent=33570 || IDLREvent=16279 || IDLREvent=9727 || IDLREvent=6558 || IDLREvent=96077 || IDLREvent=17424 || IDLREvent=25233 || IDLREvent=25273 || IDLREvent=33170 || IDLREvent=92637 || IDLREvent=24503 || IDLREvent=24511 || IDLREvent=33891 || IDLREvent=96375 || IDLREvent=24551 || IDLREvent=33143 || IDLREvent=25228 || IDLREvent=419 || IDLREvent=96702 || IDLREvent=12775 || IDLREvent=7555 || IDLREvent=3943 || IDLREvent=11630 || IDLREvent=11945 || IDLREvent=45687 || IDLREvent=75202) ORDER BY IDIR) ORDER BY Surname,GivenName LIMIT 20 OFFSET 0

The subquery, ridiculous as it is, takes only 0.54 seconds to execute against a table with 43,233 rows and returns 468 values of IDIR. But the query as a whole never completes.

EXPLAIN reports:
+----+--------------------+-------+------+---------------+------+---------+------+-------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+-------+------------------------------+
| 1 | PRIMARY | tblIR | ALL | NULL | NULL | NULL | NULL | 76372 | Using where; Using filesort |
| 2 | DEPENDENT SUBQUERY | tblER | ALL | NULL | NULL | NULL | NULL | 43233 | Using where; Using temporary |
+----+--------------------+-------+------+---------------+------+---------+------+-------+------------------------------+
2 rows in set (0.00 sec)

Since the subquery returns a list of unique keys for the main table, how do I restructure this SELECT so that it can use the index?

The definition of tblER is:
CREATE TABLE `tblER` (
`IDER` int(10) unsigned NOT NULL AUTO_INCREMENT,
`IDIR` int(10) unsigned DEFAULT NULL,
`IDET` int(10) unsigned DEFAULT NULL,
`Order` smallint(5) DEFAULT NULL,
`EventD` varchar(100) DEFAULT NULL,
`EventSD` int(10) DEFAULT NULL,
`IDLREvent` int(10) unsigned DEFAULT NULL,
`Desc` longtext,
`GEDTag` varchar(30) DEFAULT NULL,
`EventExclude` tinyint(3) unsigned,
`IDType` tinyint(3) unsigned DEFAULT NULL,
`IDAR` int(10) unsigned DEFAULT NULL,
`Description` varchar(255) DEFAULT NULL,
`SentenceOverride` varchar(255) DEFAULT NULL,
`qsTag` tinyint(3) unsigned DEFAULT NULL,
`RGExclude` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`IDER`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

The definition of tblIR is:

CREATE TABLE `tblIR` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`IDIR` int(10) DEFAULT NULL,
`FSID` varchar(255) DEFAULT NULL,
`Surname` varchar(120) DEFAULT NULL,
`SoundsLike` varchar(4) DEFAULT NULL,
`GivenName` varchar(120) DEFAULT NULL,
`Prefix` varchar(120) DEFAULT NULL,
`Title` varchar(120) DEFAULT NULL,
`NameNote` longtext,
`Gender` tinyint(3) unsigned DEFAULT NULL,
`BirthD` varchar(100) DEFAULT NULL,
`BirthSD` int(10) DEFAULT NULL,
`IDLRBirth` int(10) DEFAULT NULL,
`ChrisD` varchar(100) DEFAULT NULL,
`ChrisSD` int(10) DEFAULT NULL,
`IDLRChris` int(10) DEFAULT NULL,
`ChrTerm` varchar(100) DEFAULT NULL,
`DeathD` varchar(100) DEFAULT NULL,
`DeathSD` int(10) DEFAULT NULL,
`IDLRDeath` int(10) DEFAULT NULL,
`BuriedD` varchar(100) DEFAULT NULL,
`BuriedSD` int(10) DEFAULT NULL,
`IDLRBuried` int(10) DEFAULT NULL,
`Cremated` tinyint(3) unsigned DEFAULT NULL,
`IDARBirth` int(10) DEFAULT NULL,
`IDARChris` int(10) DEFAULT NULL,
`IDARDeath` int(10) DEFAULT NULL,
`IDARBuried` int(10) DEFAULT NULL,
`BirthNote` longtext,
`ChrisNote` longtext,
`DeathNote` longtext,
`BuriedNote` longtext,
`Living` tinyint(3) unsigned DEFAULT NULL,
`BaptismD` varchar(100) DEFAULT NULL,
`BaptismSD` int(10) DEFAULT NULL,
`BaptismKind` tinyint(3) unsigned DEFAULT NULL,
`IDTRBaptism` int(10) DEFAULT NULL,
`BaptismNote` longtext,
`LDSB` tinyint(3) unsigned DEFAULT NULL,
`ConfirmationD` varchar(100) DEFAULT NULL,
`ConfirmationSD` int(10) DEFAULT NULL,
`ConfirmationKind` tinyint(3) unsigned DEFAULT NULL,
`IDTRConfirmation` int(10) DEFAULT NULL,
`ConfirmationNote` longtext,
`LDSC` tinyint(3) unsigned DEFAULT NULL,
`InitiatoryD` varchar(100) DEFAULT NULL,
`InitiatorySD` int(10) DEFAULT NULL,
`IDTRInitiatory` int(10) DEFAULT NULL,
`InitiatoryNote` longtext,
`LDSI` tinyint(3) unsigned DEFAULT NULL,
`EndowD` varchar(100) DEFAULT NULL,
`EndowSD` int(10) DEFAULT NULL,
`IDTREndow` int(10) DEFAULT NULL,
`EndowNote` longtext,
`LDSE` tinyint(3) unsigned DEFAULT NULL,
`TempleTag` tinyint(3) unsigned DEFAULT NULL,
`IDMRPref` int(10) DEFAULT NULL,
`IDMRParents` int(10) DEFAULT NULL,
`IDAR` int(10) DEFAULT NULL,
`AncInterest` tinyint(3) unsigned DEFAULT NULL,
`DecInterest` tinyint(3) unsigned DEFAULT NULL,
`Tag1` tinyint(3) unsigned DEFAULT NULL,
`Tag2` tinyint(3) unsigned DEFAULT NULL,
`Tag3` tinyint(3) unsigned DEFAULT NULL,
`Tag4` tinyint(3) unsigned DEFAULT NULL,
`Tag5` tinyint(3) unsigned DEFAULT NULL,
`Tag6` tinyint(3) unsigned DEFAULT NULL,
`Tag7` tinyint(3) unsigned DEFAULT NULL,
`Tag8` tinyint(3) unsigned DEFAULT NULL,
`Tag9` tinyint(3) unsigned DEFAULT NULL,
`TagGroup` tinyint(3) unsigned DEFAULT NULL,
`TagAnc` tinyint(3) unsigned DEFAULT NULL,
`TagDec` tinyint(3) unsigned DEFAULT NULL,
`SaveTag` tinyint(3) unsigned DEFAULT NULL,
`SrchTag` tinyint(3) unsigned DEFAULT NULL,
`SrchTagIGI` tinyint(3) unsigned DEFAULT NULL,
`SrchTagRG` tinyint(3) unsigned DEFAULT NULL,
`SrchTagFS` tinyint(3) unsigned DEFAULT NULL,
`qsTag` tinyint(3) unsigned DEFAULT NULL,
`ReminderTag` tinyint(3) unsigned DEFAULT NULL,
`ReminderTagDeath` tinyint(3) unsigned DEFAULT NULL,
`TreeNum` smallint(5) DEFAULT NULL,
`LTMP1` int(10) DEFAULT NULL,
`LTMP2` int(10) DEFAULT NULL,
`AlreadyUsed` tinyint(3) unsigned DEFAULT NULL,
`UserRef` varchar(50) DEFAULT NULL,
`AncestralRef` varchar(20) DEFAULT NULL,
`Notes` longtext,
`References` longtext,
`Medical` longtext,
`DeathCause` varchar(255) DEFAULT NULL,
`PPCheck` tinyint(3) unsigned DEFAULT NULL,
`Imported` tinyint(3) unsigned DEFAULT NULL,
`Added` int(10) DEFAULT NULL,
`AddedTime` varchar(5) DEFAULT NULL,
`Updated` int(10) DEFAULT NULL,
`UpdatedTime` varchar(5) DEFAULT NULL,
`Relations` varchar(20) DEFAULT NULL,
`NeverMarried` tinyint(3) unsigned DEFAULT NULL,
`DirectLine` tinyint(3) unsigned DEFAULT NULL,
`STMP1` varchar(255) DEFAULT NULL,
`ColorTag` tinyint(3) unsigned DEFAULT NULL,
`IntelliShare` varchar(50) DEFAULT NULL,
`Private` tinyint(3) unsigned DEFAULT NULL,
`PPExclude` varchar(50) DEFAULT NULL,
`RGExclude` tinyint(3) unsigned DEFAULT NULL,
`DNA` longtext,
`FSSync` tinyint(3) unsigned DEFAULT NULL,
`FSDups` tinyint(3) unsigned DEFAULT NULL,
`FSOrdinance` tinyint(3) unsigned DEFAULT NULL,
`FSLinks` longtext,
PRIMARY KEY (`ID`),
UNIQUE KEY `IDIR` (`IDIR`),
KEY `Surname` (`Surname`),
KEY `GivenName` (`GivenName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Options: ReplyQuote


Subject
Views
Written By
Posted
No response to SELECT with IN clause
2171
April 21, 2014 06:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.