Re: Performance of Join

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. Received on Fri Mar 09 2007 - 14:54:41 CET