Hello,

To be more explicit I will give an example from my current work.

I have three tables, COMPCLASS, COMPMARCA, COMPMODEL.

COMPCLASS
------------
CLASSID CLASS
1 Processor
2 Motherboard

COMPMARCA CLASSNO=FK of CLASSID
--------------
MARCAID CLASSNO MARCA
1 1 AMD
2 1 Intel
3 2 Epox
4 2 Asus
5 2 Gigabyte

COMPMODEL MARCANO=FK of MARCAID
---------------
MODELID MARCANO MODEL
1 1 XP2200+
2 1 Sempron3000+
3 2 Intel P4 2Ghz
4 3 8RDA3I
5 4 K8U-X


There are other tables which relate to the COMPMODEL table on MODELID
field, for example:

ENTRYPROD
------------
EID COMP(notnull) PRICE
1 5 324
2 2 34
4 1 53
etc

COMP field will always have a MODELID corespondent in the COMPMODEL
table, i have some triggers to insure consistency

My usual queries are something like

SELECT ENTRYPROD.*, CLASS, MARCA, MODEL
FROM ENTRYPROD
LEFT JOIN COMPMODEL ON COMP=MODELID
LEFT JOIN COMPMARCA ON MARCANO=MARCAID
LEFT JOIN COMPCLASS ON CLASSNO=CLASSID

This query seems the logical one, but for the sake of testing I was
doing something like

SELECT ENTRYPROD.*, CLASS, MARCA, MODEL
FROM ENTRYPROD
JOIN COMPMODEL ON COMP=MODELID
JOIN COMPMARCA ON MARCANO=MARCAID
JOIN COMPCLASS ON CLASSNO=CLASSID

which return the same amount of records (the actual tables have
thousands of records).
From the test I made, the query execution is fairly the same, however
the JOIN makes faster fetches.

LEFT JOIN (48662 records)
-------------
Prepare time = 0ms
Execute time = 20ms
Avg fetch time = 0.95 ms
Current memory = 2,746,816
Max memory = 2,846,608
Memory buffers = 10,240
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 6,215


JOIN (48662 records)
-------------------
Prepare time = 10ms
Execute time = 10ms
Avg fetch time = 0.48 ms
Current memory = 2,746,476
Max memory = 2,846,608
Memory buffers = 10,240
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 6,215



What is your opinion about these?

I hope I made myself understood

TIA
Radu