Hello Friends,

I would like to know performance of Join. So give me your feedback on
following two CASES. which one is good and why? Also explain me Query
How it will execute,STEP BY STEP for both CASE?

Following is the Main User Table.

TABLE `tbl_user` (
`usr_id` int(11) NOT NULL auto_increment,
`usr_firstname` varchar(50) NOT NULL,
`usr_lastname` varchar(50) NOT NULL,
`usr_gender` enum('M','F') NOT NULL default 'M'
PRIMARY KEY (`usr_id`)
) ENGINE=MyISAM

CASE 1:

CREATE TABLE `tbl_mail_inbox` (
`msg_id` int(11) NOT NULL auto_increment,
`msg_touserid` int(11) default NULL,
`msg_fromuserid` int(11) default NULL,
`msg_msg` varchar(250) default NULL
PRIMARY KEY (`msg_id`)
) ENGINE=MyISAM

CASE 2:

CREATE TABLE `tbl_mail_inbox` (
`msg_id` int(11) NOT NULL auto_increment,
`msg_touserid` int(11) default NULL,
`msg_fromuserid` int(11) default NULL,
`msg_msg` varchar(250) default NULL
PRIMARY KEY (`msg_id`),
KEY `msg_fromuserid` (`msg_fromuserid`),
KEY `msg_touserid` (`msg_touserid`)
) ENGINE=MyISAM

Please note Index key difference in both keys. Those are most
important part of my question.

Now Query looks like this:
SELECT m.*, usr_id, usr_firstname, usr_lastname FROM
tbl_mail_inbox
as m JOIN tbl_user ON msg_fromuserid=usr_id WHERE msg_touserid =13

Can you explain me query for above 2 cases... I am very confused
regarding Join Query performance.

Thanks & Regards,
Mitul Patel.

Case 1: Data will be accessed with a full table scan. This will
generally not perform very well.
Case 2: Data will be accessed using indexes on the msg_touserid and
msg_fromuserid. This will perform much better.

-T

On Mar 9, 7:21 am, "Mitul" <patel.mitu... (AT) gmail (DOT) com> wrote:

On Mar 9, 7:21 am, "Mitul" <patel.mitu... (AT) gmail (DOT) com> wrote:
For performance questions there are MANY factors that influence
results:
* Operating system
* DBMS product
* Schema structure (tables/indices/views/...)
* statistics kept by the DBMS
* How the optimizer uses those statistics (e.g. are they stale?) and
how they relate to the schema (identifying whether it can
determine
if using an index will help or not).
* of course the statistics are derived from that actual data
* finally the query itself (structure of the query, values being
searched for, how the optimizer parses the query)

You do not mention the OS or DBMS used (this is comp.databases
afterall, so we cannot assume any particular one). We can assume these
are the same for both cases. You do give a schema description, and a
sample query. But we still lack information on the data.

There is no way for us to answer your question. The BEST I can offer
is:
TRY IT YOURSELF.

That is the best answer to any performance question. The other maxim
for performance is KNOW THY DATA.


HTH,
ed

yiata wrote:
Please do not top post.

"Mitul" wrote:

If you weren't using "ENGINE=MyISAM" you could define foreign key
relationships, thus enhancing data integrity. Using "ENGINE=InnoDB" or
switching to a real DBMS will give you that capability.

-- Lew

Ed Prochak wrote:
[...]
Wonder what DBMS that might accept that kind of syntax ;-) FWIW, I'm
just testing if I can post to this group from a new news account.

[...]


/Lennart