Re: Performance of Join

On Mar 9, 10:54 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> Mitul wrote:
> > 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.
>
> Hi Mitul,
>
> You will get better answers if you post your question on a MySQL newsgroup.
>
> When I look at the above, it looks like case 2 says no user can send
> more than one message and no user can receive more than one message. Is
> that correct? Is that really what you want?
>
> As a general rule of thumb and given MySQL's poor physical independence,
> a restrict before join strategy will probably perform best for the
> above. Having an index on msg_touserid might reduce a table scan to an
> index search for the restrict, but the cost of the join will remain the
> same with both cases using the index on user_id.
>
> However, I have no idea if those are the execution plans MySQL will come
> up with.

Use

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

and post us again Received on Tue Mar 13 2007 - 19:09:53 CET