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 ) ) ) ) ;
| Thread |
|---|
| • subquery hanging | Devon E Bowen | 21 Sep |
