Which will give me better performance (Table design for Joins)

[prileep]

I have two tables Users and UserLogin. Here i will use two methods of table design. and which query will return me the result more fast.
The table size will be large that it may contain records in lakhs.

Method 1:
Tables:
Users ( UserID varchar(20) primary key, PassWord varchar(20))
UsersLogin(User ID varchar(20),Log inDate DateTime)
Query:

Select Users.UserID,Us ersLogin.LoginD ate
from Users U inner join UsersLogin UL
on Users.UserID = UsersLogin.User ID

Method 2:
Tables:
Users ( SysUserID Integer primary key,UserID varchar(20), PassWord varchar(20))
UsersLogin(SysU serID Integer, LoginDate DateTime)
Query:

Select Users.UserID,Us ersLogin.LoginD ate
from Users U inner join UsersLogin UL
on Users.SysUserID = UsersLogin.SysU serID

Here for the seccond method i made SysUserID additional which will be a system generated ID, and for joins which will give better performance.

[ronverdonk]

You can find that out by using the EXPLAIN SELECT ... in MySQL and study the results for each table.

Ronald :cool: