| Advice on Index Creation [message #509806] |
Wed, 01 June 2011 03:17  |
oraranjangmailcom
Messages: 67
Registered: April 2010
|
Member |
|
|
Hi
I have a table of 3 million records, I got a requirement of creating Index for a column. Column data details are as below.
Please advice, which Index type I should go for.
Column_Name Data_Count
A 4000 i.e., distinct rows
A 4500 i.e., actual rows
A 2995500 i.e., NULL rows
Thanks in Advance,
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Advice on Index Creation [message #509812 is a reply to message #509808] |
Wed, 01 June 2011 03:30  |
oraranjangmailcom
Messages: 67
Registered: April 2010
|
Member |
|
|
There are three scenarios and they are direct id search on single table.
1) SELECT <column_names>, ....
FROM <table_name> -- single table
WHERE <column_name> = <condition> -- indexed column
OR
A = <condition> -- Index to be decided
2) SELECT <column_names>, ....
FROM <table_name> -- single table
WHERE A = <condition> -- to be decided
3) SELECT <column_names>, ....
FROM <table_name> -- single table
WHERE <column_name> = <condition> -- indexed column
AND
A = <condition> -- index to be decided
--
|
|
|
|
|
|
|
|
| Re: Advice on Index Creation [message #509815 is a reply to message #509814] |
Wed, 01 June 2011 03:37 |
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
Though really you'd be better off just trying different index combos on your test DB and seeing what works best, rather than relying on us guessing based on limited information.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
