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_conditi on AND B_where_conditi on
LIMIT 0,20
Assuming:

A contains 1,000,000 records and B contains around 4,000,000 records,
and
the A_where_conditi on will retrieve 300,000 rows from A
the B_where_conditi on will retrieve 100,000 rows from B
and
altogether, the where_condition s 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_conditi on 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_conditi on, 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!