Performance of Join
Date: 9 Mar 2007 04:21:48 -0800
Message-ID: <1173442908.239970.36320_at_8g2000cwh.googlegroups.com>
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`),
) 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.
Received on Fri Mar 09 2007 - 13:21:48 CET