468,510 Members | 1,745 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,510 developers. It's quick & easy.

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

20
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(UserID varchar(20),LoginDate DateTime)
Query:
Select Users.UserID,UsersLogin.LoginDate
from Users U inner join UsersLogin UL
on Users.UserID = UsersLogin.UserID
Method 2:
Tables:
Users ( SysUserID Integer primary key,UserID varchar(20), PassWord varchar(20))
UsersLogin(SysUserID Integer, LoginDate DateTime)
Query:
Select Users.UserID,UsersLogin.LoginDate
from Users U inner join UsersLogin UL
on Users.SysUserID = UsersLogin.SysUserID
Here for the seccond method i made SysUserID additional which will be a system generated ID, and for joins which will give better performance.
Feb 26 '07 #1
1 1224
ronverdonk
4,258 Expert 4TB
You can find that out by using the EXPLAIN SELECT ... in MySQL and study the results for each table.

Ronald :cool:
Feb 26 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Randell D. | last post: by
4 posts views Thread by Igor | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.