I tried the subquery below.
When I use explain it says that it uses the primary index on table1 byt by some reason it still does a full tablescan on that table.
SELECT id1, id2
FROM table1
WHERE (
id1, id2
)
IN (
SELECT id1, id2
FROM table2
WHERE min >=20
AND min <=20
)
table1 primary: id1 id2
table2 primary: id1 id2
table2 index: min
number of rows in both tables are 20000.
number of rows returned by inner select are 1152
it does a full tablescan on table1, even if explain says it uses index ................... WHY
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 select above.
3. Since the returned id1,id2 is the primary key in table1 I thought table 1 would use it and not do a full tablescan.
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?
My problem is:
Why the full tablescan on table1?
Why does it not use the primary index on table1?
Explain on the query says:
sel_typ table type p_keys key k_len ref rows Extra
PRIMARY table1 index NULL PRIMARY 4 NULL 21158 Using where; Using index
DEPENDENT SUBQUERY table2 unique_subquery PRIMARY id1-id1,min PRIMARY 4 func,func 1 Using index; Using where
Okay... originally I want more fields returned by table1.
If I add one more field to the outer select like this:
SELECT id1, id2, title
FROM table1
An explain now says: Type: All, and the rest of row: NULL, NULL, NULL, NULL, 20 000, Using where
Hmmm Does not look like it actually takes more time to run, it still does a full table scan on table1, but now it does not say : using index any more.
When I use explain it says that it uses the primary index on table1 byt by some reason it still does a full tablescan on that table.
SELECT id1, id2
FROM table1
WHERE (
id1, id2
)
IN (
SELECT id1, id2
FROM table2
WHERE min >=20
AND min <=20
)
table1 primary: id1 id2
table2 primary: id1 id2
table2 index: min
number of rows in both tables are 20000.
number of rows returned by inner select are 1152
it does a full tablescan on table1, even if explain says it uses index ................... WHY
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 select above.
3. Since the returned id1,id2 is the primary key in table1 I thought table 1 would use it and not do a full tablescan.
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?
My problem is:
Why the full tablescan on table1?
Why does it not use the primary index on table1?
Explain on the query says:
sel_typ table type p_keys key k_len ref rows Extra
PRIMARY table1 index NULL PRIMARY 4 NULL 21158 Using where; Using index
DEPENDENT SUBQUERY table2 unique_subquery PRIMARY id1-id1,min PRIMARY 4 func,func 1 Using index; Using where
Okay... originally I want more fields returned by table1.
If I add one more field to the outer select like this:
SELECT id1, id2, title
FROM table1
An explain now says: Type: All, and the rest of row: NULL, NULL, NULL, NULL, 20 000, Using where
Hmmm Does not look like it actually takes more time to run, it still does a full table scan on table1, but now it does not say : using index any more.