How to calculate Column Selectivity

Question

I want to create an index on a table and I'm trying to decide the order of the columns in the index. Normally, I go by gut feeling and my knowledge of the data and of the queries the application will typically generate, but in this case looking at the data and the queries I think I need to create and index based on column selectivity. So that the most selective column (the one that narrows further choices down the most) will be the first in the index, followed by the second most selective column etc.

My problem is calculating selectivity of the column i.e. math. I would post it on mathoverflow but I don't think they necessarily know what indexes and columns are. Please help.

Answer 1

It's a simple ratio for each column:

(Number of Unique Values for the Column) to (Number of Rows In Table)

Calculating the numbers depends on your RDBMS. For SQL Server, you could get the numbers as follows:

SELECT COUNT(DISTINCT ColumnName) FROM TableName

SELECT COUNT(*) FROM TableName

Answer 2

How to get the selectivity of an index