List:General Discussion« Previous MessageNext Message »
From:Devon E Bowen Date:September 21 2005 5:06am
Subject:subquery hanging
View as plain text  
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 hangingDevon E Bowen21 Sep