| Author |
Topic |
|
swraman
Starting Member
5 Posts |
 Posted - 2013-04-20 : 01:29:57
|
| Hi,I'm trying to learn more about sql, and am very new to it.I have many junction tables that link the primary keys of two other tables. My question is which should I put an index on? Is there a general consensus on how to handle cases like this? Example would be viewing the permissions granted to a certain user, then viewing the permissions a user belongs to.Is there any way to make these both run efficiently?Is there any way to have SQL create an index on both tables separately, so it two index lists, one sorted by each index? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-20 : 02:34:06
|
| the choice of indexes depends on what queries you frequently apply on tables, which columns queries target, selectivity of values in the table etc factors. So you need to first analyse and find the columns targeted by frequent queries------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
swraman
Starting Member
5 Posts |
Posted - 2013-04-20 : 13:51:36
|
| Thanks. Exactly what I needed to know.Is there any reason that SQL doesn't implement maintaining two indexed tables, one sorted by one column and the other sorted by another column? I can see many applications where that would be useful - for instance when searches are often done both ways but items are rarely added to the table. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-22 : 01:35:46
|
| sql will maintain separate index tables for non clustered indexes. for clustered index it will sort the original table itself based on index columns which is why you can have only a single clustered index on a table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
swraman
Starting Member
5 Posts |
Posted - 2013-04-22 : 23:15:08
|
| So, if I have two columns in a table that I will be doing searches on, say" email address" and "username".If I create an index on Email Address and on username, it will maintain sorted tables of both of these (assuming neither is my clustered index)? |
|
|
|
swraman
Starting Member
5 Posts |
Posted - 2013-04-22 : 23:19:38
|
| Or, would it be better to have two tables: The User table with the index on Email Address, then a second 2 column table that maps username to email address (1:1 mapping). |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-23 : 12:43:17
|
quote:
Originally posted by swraman
So, if I have two columns in a table that I will be doing searches on, say" email address" and "username".If I create an index on Email Address and on username, it will maintain sorted tables of both of these (assuming neither is my clustered index)?
not sorted tables. but sorted list of just the index column values and each of value will point to clustered index position (if one present) or to actual position where the rows containing value exists------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
Join Table Indexing