CODEWITHSUNDEEP
sqlsql-serverperformance
Greg Bala
Greg Bala

Reputation: 3903

Most efficient way to SELECT DISTINCT ColA FROM LargeTableWithFewValuesForColA

I have a large table (millions of rows).

I have to often get DISTINCT values of some columns. In my case, those columns actually have very few distinct values (a few to a few dozen)

What is the most efficient way of doing this?

Upvotes: 0

Views: 31

Answers (2)

Himanshu
Himanshu

Reputation: 3970

To add to Gordons answer in large databases you could partition your data in addition to the index as well. Partitioning of data is like

  Table_1 (id) 
  Select distinct records from table
  Where id <1000
  Table_2 (id) 
  Select distinct records from table
  Where id >1000

 Actual table =table_1+table_2 (id)

Just a sample to illustrate this partition is not extra its actually the same table or db just that it gets split up on basis of unique column

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Add an index on the column and then run:

select distinct column
from t;

Upvotes: 2

Related Questions