MySQL Forums
Forum List  »  New in 4.1: Subqueries

Problem with subquery
Posted by: Tore Krudtaa
Date: February 03, 2005 06:23PM

MySql 4.1.9, WinXP. InnoDB tables.

I tried the subquery below and by some reason it did not use primary index in table1.

table1: id1 id2 (PRIMARY)
table2: id1 id2 (PRIMARY)
table2: min (INDEX nonunique)

Number of rows in both tables: 40981

Executing a single select like this:

SELECT id1, id2
FROM table2
WHERE min >= 20
AND min <= 30

And get the result: Showing rows 0 - 29 (2100 total, Query took 0.0003 sec)

With explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table2 range min min 9 NULL 3888 Using where; Using index

Why the difference in rows? Excution=2100 and Explain=3888

Then I execute the subquery below:

SELECT id1, id2, title
FROM table1
WHERE (
id1, id2
)
IN (
SELECT id1, id2
FROM table2
WHERE min >= 20
AND min <= 30
)

Execution= Showing rows 0 - 29 (2100 total, Query took 0.0003 sec)

Explain:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table1 ALL NULL NULL NULL NULL 41111 Using where
2 DEPENDENT SUBQUERY table2 unique_subquery PRIMARY,min PRIMARY 4 func,func 1 Using index; Using where

Number of rows in both tables are still 40981. Why is it reporting 41111 for table1 in explain?
Why does it a full tablescan on table1 and does not use any index in table1................... WHY?

Then I add "USE INDEX (min) to inner select and get:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table1 ALL NULL NULL NULL NULL 41111 Using where
2 DEPENDENT SUBQUERY table2 range min min 9 NULL 3888 Using where; Using index

Why did the subquery not use the min index in my previous example (without the USE INDEX)?

I thought this query was supposed to work like this:
1. Execute the innermost select: and return those id1,id2 pairs that match.
2. Execute the outmost select based on the returned id1,id2 pairs from inner select.
3. Since the returned id1,id2 is the primary key in table1 and table2 I though table 1 would use it and not do a full tablescan.

Why this full tablescan?

As a last try I added USE INDEX (PRIMARY) to the outer select:
Got the same result as the last explain report above...??

I do not want this converted to an INNER JOIN query. I just want to know why this subquery does not use the primary index for table1. What do I do wrong here?

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem with subquery
4544
February 03, 2005 06:23PM


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.