| View previous topic :: View next topic |
| Author |
Message |
danm
Intermediate
Joined: 29 Jun 2004
Posts: 170
Topics: 73
|
 Posted: Thu Mar 17, 2011 11:55 am Post subject: Number of index |
 |
|
I konw that one should limit the number of indexes on a table. If I have queries that reference different columns on a table:
| Code: |
query 1: WHERE COLA = 'X'
query 2: WHERE COLB = 'S'
query 3: WHERE COLC = 'X1'
query 4: WHERE COLA = 'W' and COLC = 'X2'
query 5: WHERE COLB = 'T' and COLC = 'X5'
Number of distinct values in: COLA = 8, COLB = 5, COLC = 10
|
Would a single inedx with COLA, COLB and COLC be sufficient? If one index is sufficient, what should be the clolumn sequence in the index (e.g. COLB with the least distinct value should be first)? |
|
| Back to top |
|
 |
Dibakar
Advanced
Joined: 02 Dec 2002
Posts: 700
Topics: 63
Location: USA
|
| Posted: Thu Mar 17, 2011 2:36 pm Post subject: |
|
|
I beleive if you want to use index for all these queries then first three would need different indexes, so you got at least three.
_________________
Regards,
Diba |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12382
Topics: 75
Location: San Jose
|
| Posted: Thu Mar 17, 2011 2:50 pm Post subject: |
|
|
| danm wrote: | | Number of distinct values in: COLA = 8, COLB = 5, COLC = 10 |
Danm,
Do you really need an index for such low volume of data?
Kolusu |
|
| Back to top |
|
|
danm
Intermediate
Joined: 29 Jun 2004
Posts: 170
Topics: 73
|
| Posted: Thu Mar 17, 2011 3:05 pm Post subject: |
|
|
Kolusu,
Don't I need index on a table that contains over 400,000 rows? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12382
Topics: 75
Location: San Jose
|
| Posted: Thu Mar 17, 2011 3:23 pm Post subject: |
|
|
| danm wrote: | Kolusu,
Don't I need index on a table that contains over 400,000 rows? |
You would need an index but the counts you have shown earlier doesn't translate to 400k records.
In any case I would suggest reading this excellent article about Index design which will give you a better idea
Kolusu |
|
| Back to top |
|
|
danm
Intermediate
Joined: 29 Jun 2004
Posts: 170
Topics: 73
|
| Posted: Mon Mar 21, 2011 1:04 pm Post subject: |
|
|
Kolusu,
Thanks. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
