Performance of Join

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.

> 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?

Don't ask us, ask your server.
<snip>

> 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

So send this query to the server:

EXPLAIN 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


What you get is a table that tells you if any indexes are used, and if
so, which one. Also, in the MySQL documentation, there is an entire
chapter on query performance and how queries can be optimized. I suggest
you read it, as it is rather large to post here.
Back to the results of the EXPLAIN command: if it says "using index", it
is usually quite fast. If it says "using filesort", there _may_ be an
optimization possible by adding an index to the table. There's a lot
more to say on this, but this is a good start.

Best regards.