Nested Query Problem (Not using Index) 1

[Barbaro]

Does anybody know why the explain of a query that contains a nested query shows that the "dependant" query does not use indexes when it should?? Does the query use indexes at all?
ie:
Table1 structure:
fielda rimary key
fieldb

Table2 structure:
fieldb rimary key
fieldc

select fielda,
(select table2.fieldc from table2 where table2.fieldb=table1.fieldb)
from table1

The nested query always does a full table scan of table2!!
Used force index and it´s the same as not using it.

 

[sleipnir214]

What version of MySQL are you running? As far as I know, that is not a well-formed MySQL query.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Barbaro]

MySQL 4.1 (It supports nested queries)

 

[sleipnir214]

You do know that 4.1 is also alpha-revision software, right?

I have no experience with version 4.1, but it looks to me like you have columns selected from two tables (one directly, one the result of the subselect) but you have no relation between the two tables. MySQL should interpret that as a cross join.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Barbaro]

The relation is given by "table2.fieldb=table1.fieldb"

select fielda,
(select table2.fieldc from table2 where
table2.fieldb=table1.fieldb)
from table1

 

[sleipnir214]

That relates the two tables in the inner select. What about the outer select? What relates records between table1 and the derived table of the inner select?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Barbaro]

Should it have relation?

 

[sleipnir214]

Any time you have "tablename, tablename" in a query, unless you state the field(s) which should relate the tables, MySQL assumes a cross join.

Your nested select is going to return a derived table. I assume that if you don't specify the columns which define the relation between table1 and the derived table returned by the nested select, MySQL will attempt a cross join.

I'm no expert on the behavior of MysQL 4.1. I've never used it because I don't use alpha- or beta- revision software on my production boxes, and I don't develop on a version of MySQL that I'm not going to run.

The thing is you're using a nested select when you don't have to. That query should be functionally equivalent to:

select t1.fielda, t2.fieldc
from table1 t1, table2 t2
where t1.fieldb = t2.fieldb


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Barbaro]

To clarify my idea:
If i could use left joins i would, but that´s not the point!!
Here I give the tables and the query.

# Server version 4.1.0-alpha-log
#
# Table structure for table 'demandexam'
#
CREATE TABLE demandexam (
sku_code char(3) NOT NULL default '0',
quantity tinyint(3) unsigned default '0',
PRIMARY KEY (sku_code)
) TYPE=MyISAM CHARSET=latin1;

INSERT INTO demandexam VALUES("aaa", "34"
INSERT INTO demandexam VALUES("bbb", "34"
INSERT INTO demandexam VALUES("ccc", "65"

#
# Table structure for table 'skuexam'
#

CREATE TABLE skuexam (
code char(3) NOT NULL default '0',
replacementcode char(3) default NULL,
PRIMARY KEY (code)
) TYPE=MyISAM CHARSET=latin1;

INSERT INTO skuexam VALUES("aaa", NULL);
INSERT INTO skuexam VALUES("bbb", "ccc"
INSERT INTO skuexam VALUES("ccc", NULL);
INSERT INTO skuexam VALUES("eee", NULL);


select
s.code,
if (isnull(s.replacementcode),0,
(select sum(e.quantity) from demandexam e where e.sku_code=s.code))as quantity
from skuexam s

The dependant subselect is not using the index!!!

 

[sleipnir214]

Couldn't you just perform something like:

select
s.code, sum(e.quantity) as quantity
from
skuexam s left join demandexam e on s.code = e.skucode
group by s.code

?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!