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]
