I'm not an SQL expert and have a subquery problem. I have a query that consists of a query inside of a query inside of a query. The two inner subqueries work fine together and give me a list of "ids" for matching records. And they do it very quickly. If I then take their results and feed them to the outer query by hand, it also works fine - very fast. But if I combine them so that they are nested, the system just locks up. This doesn't seem rational to me. Can someone give me a hint as to what I might be doing wrong?
In the attached output, I first show the indexes for each of the tables in question. Then I show the two inner queries and the list they return. Then how it works if I merge the results by hand. Then the final command that locks the system. Oh, and the MySQL server version is 4.1.11. Any help would be greatly appreciated... Devon ------------------- mysql> show index from Stage_Entries; +---------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+ | Stage_Entries | 0 | PRIMARY | 1 | ID | A | 176092 | NULL | NULL | | BTREE | | | Stage_Entries | 0 | Entries_SearchName_1 | 1 | SearchName | A | 176092 | NULL | NULL | YES | BTREE | | | Stage_Entries | 0 | Entries_SearchName_1 | 2 | ID | A | 176092 | NULL | NULL | | BTREE | | | Stage_Entries | 1 | EntryID | 1 | EntryID | A | 176092 | NULL | NULL | YES | BTREE | | | Stage_Entries | 1 | ContinentID | 1 | ContinentID | A | 17 | NULL | NULL | | BTREE | | | Stage_Entries | 1 | SubcontinentID | 1 | SubcontinentID | A | 88046 | NULL | NULL | YES | BTREE | | | Stage_Entries | 1 | CountryID | 1 | CountryID | A | 2229 | NULL | NULL | YES | BTREE | | | Stage_Entries | 1 | RegionID | 1 | RegionID | A | 58697 | NULL | NULL | YES | BTREE | | | Stage_Entries | 1 | LastModifiedBy | 1 | LastModifiedBy | A | 10 | NULL | NULL | YES | BTREE | | | Stage_Entries | 1 | Status | 1 | Status | A | 5 | NULL | NULL | YES | BTREE | | | Stage_Entries | 1 | LastModifiedDate | 1 | LastModifiedDate | A | 19565 | NULL | NULL | YES | BTREE | | | Stage_Entries | 1 | SearchName | 1 | SearchName | NULL | 88046 | NULL | NULL | YES | FULLTEXT | | | Stage_Entries | 1 | EntryText | 1 | EntryText | NULL | 176092 | NULL | NULL | YES | FULLTEXT | | +---------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+ 13 rows in set (0.00 sec) mysql> show index from Stage_EntryIsTypeOfPlace; +--------------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Stage_EntryIsTypeOfPlace | 0 | PRIMARY | 1 | ID | A | 182769 | NULL | NULL | | BTREE | | | Stage_EntryIsTypeOfPlace | 1 | PlaceTypeID | 1 | PlaceTypeID | A | 180 | NULL | NULL | YES | BTREE | | +--------------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec) mysql> show index from Stage_EntryHasPopulation; +--------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Stage_EntryHasPopulation | 0 | PRIMARY | 1 | ID | A | 182302 | NULL | NULL | | BTREE | | | Stage_EntryHasPopulation | 1 | EntryID | 1 | EntryID | A | 182302 | NULL | NULL | | BTREE | | +--------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec) mysql> SELECT EntryID FROM Stage_EntryIsTypeOfPlace WHERE PlaceTypeID = 21 AND EntryID IN ( SELECT ID FROM Stage_Entries WHERE ( MATCH ( SearchName ) AGAINST ( 'north*' IN BOOLEAN MODE ) ) AND ( ( Stage_Entries.Status = 1 ) OR ( Stage_Entries.Status = 2 ) OR ( Stage_Entries.Status = 3 ) OR ( Stage_Entries.Status = 5 ) ) ) ; +---------+ | EntryID | +---------+ | 112132 | | 111729 | | 111748 | | 111750 | | 111759 | | 111762 | | 111763 | | 111807 | | 111827 | | 111831 | | 111838 | | 111857 | | 111876 | | 111891 | | 111923 | | 111946 | | 111963 | | 111964 | | 111970 | | 111982 | | 111985 | | 111987 | | 111998 | | 112000 | | 112016 | | 112037 | | 112038 | | 112050 | | 112051 | | 112052 | | 112064 | | 112091 | | 112107 | | 112109 | | 112140 | | 112152 | | 112153 | | 112178 | | 112221 | | 112222 | | 112235 | | 112237 | | 112245 | | 117817 | +---------+ 44 rows in set (0.62 sec) mysql> SELECT EntryID FROM Stage_EntryHasPopulation WHERE Population > 10000 AND EntryID IN ( 112132, 111729, 111748, 111750, 111759, 111762, 111763, 111807, 111827, 111831, 111838, 111857, 111876, 111891, 111923, 111946, 111963, 111964, 111970, 111982, 111985, 111987, 111998, 112000, 112016, 112037, 112038, 112050, 112051, 112052, 112064, 112091, 112107, 112109, 112140, 112152, 112153, 112178, 112221, 112222, 112235, 112237, 112245, 117817 ) ; +---------+ | EntryID | +---------+ | 111729 | | 111729 | | 111748 | | 111748 | | 111750 | | 111750 | | 111759 | | 111762 | | 111763 | | 111807 | | 111807 | | 111827 | | 111827 | | 111831 | | 111831 | | 111838 | | 111838 | | 111857 | | 111876 | | 111876 | | 111891 | | 111891 | | 111923 | | 111923 | | 111963 | | 111963 | | 111964 | | 111964 | | 111970 | | 111970 | | 111982 | | 111982 | | 111985 | | 111985 | | 111987 | | 111987 | | 111998 | | 111998 | | 112000 | | 112000 | | 112016 | | 112016 | | 112037 | | 112037 | | 112038 | | 112038 | | 112050 | | 112050 | | 112052 | | 112064 | | 112091 | | 112091 | | 112107 | | 112140 | | 112152 | | 112153 | | 112153 | | 112178 | | 112221 | | 112221 | | 112235 | | 112235 | | 112237 | | 112237 | | 112245 | | 112245 | | 117817 | +---------+ 67 rows in set (0.01 sec) mysql> SELECT EntryID FROM Stage_EntryHasPopulation WHERE Population > 10000 AND EntryID IN ( SELECT EntryID FROM Stage_EntryIsTypeOfPlace WHERE PlaceTypeID = 21 AND EntryID IN ( SELECT ID FROM Stage_Entries WHERE ( MATCH ( SearchName ) AGAINST ( 'north*' IN BOOLEAN MODE ) ) AND ( ( Stage_Entries.Status = 1 ) OR ( Stage_Entries.Status = 2 ) OR ( Stage_Entries.Status = 3 ) OR ( Stage_Entries.Status = 5 ) ) ) ) ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]