Seperate or Combined, the speed consideration

[CryingClinton]

To help making indexes & planning the table constructions & writing SQL
statements correctly and effectively, I am thinking about how the SQL server
actually implements a table join query.

------------------------------------------------------
For Example:

A: person names, B: products each person has, products could be thourands of
kinds, table B stores the product IDs only.

SELECT something FROM A INNER JOIN B ON A.id=B.id
WHERE A_where_condition AND B_where_condition
LIMIT 0,20
Assuming:

A contains 1,000,000 records and B contains around 4,000,000 records,
and
the A_where_condition will retrieve 300,000 rows from A
the B_where_condition will retrieve 100,000 rows from B
and
altogether, the where_conditions can give a result of, let's say: 2,000
rows

-----------------------------------------------------
Let's see how MySQL will treat this query:

According to the MySQL manaul, MySQL will firstly set table A to be
dependent on table B, cos table B gives less rows than A.
It will:

1. use B_where_condition to retrieve that 100,000 rows from B and fully load
them into memory.
2. then join A onto it the loaded temp table in memory.

---NOTE.
2.a. Dont know when it will use the A_where_condition, during the join
process or after the join? However it is the matter of MySQL itself, not the
point I am trying to figure out.
2.b. Dont know how MySQL will use the LIMIT contidion, during the join
process or after the join finished? I mean if it counting the rows joined
into the temp table, and after (with or without the word DISTINCT) 20 rows
have being retrieved, the query finish? I guess so but not sure.
3.c If there is an ORDER BY, how to set the index and/or pre-sort the
physical stored datas to avoid MySQL from loading the whole table A and
re-sorting the results before the LIMIT applies?

As the discussion above, I think the JOIN action gonna cost a lot because
the table was seperated, and MySQL will forced to load one of them fully
(under the where_condition). If they are not seperated but one single table,
this will not happen.

Seperated table give the advantage of unlimited number of products each
person can has. But if use a single table, have to limit the number of
products of each person.

Anyone got any idea? Am I right or wrong here?

Thx.

ccton

[CryingClinton]

Well I said thourands of kinds of products, which means you can not set a
single column with SET data type, that can contains a collection of 64
items.

So it seems to use columns like:

product1, product2,... in table A if want to use a single table and get high
preformance.
:-)

or a stupid way like column SET1,SET2,...

haha... 100 columns can contain a collection of 6400 products... impossible
to be done like this!

[CryingClinton]

Well I said thourands of kinds of products, which means you can not set a
single column with SET data type, that can contains a collection of 64
items.

So it seems to use columns like:

product1, product2,... in table A if want to use a single table and get high
preformance.
:-)

or a stupid way like column SET1,SET2,...

haha... 100 columns can contain a collection of 6400 products... impossible
to be done like this!

[CryingClinton]

Well I said thourands of kinds of products, which means you can not set a
single column with SET data type, that can contains a collection of 64
items.

So it seems to use columns like:

product1, product2,... in table A if want to use a single table and get high
preformance.
:-)

or a stupid way like column SET1,SET2,...

haha... 100 columns can contain a collection of 6400 products... impossible
to be done like this!