What is index selectivity?


Index selectivity is a number the determines the effectiveness of index. Best possible selectivity is when all table records have a different value for the columns in index (this is typical for primary keys and unique constraints). Selectivity is quantified by index 'statistics' which is computed like this:

select count(*)/count(distinct(index_field)) from table;

Please note that this query would fail if the table has zero rows (due to division by zero). To prevent this, you can use NULLIF function:

select count(*)/nullif(count(distinct(index_field)), 0) from table;

As you can see, higher statistics mean that there aren't many different values in the table column. If you have a Y/N or true/false field, it would have statistics value of 0.5 which is worst possible selectivity. As statistics approach zero, index gets more effective.

Index statistics can be read from RDB$INDICES system table. Statistics range from zero (the best selectivity) to 0.5 (the worst) with a special value of 1 (one) which means that all table rows have the same value for index column. Having index on such field is completely useless. You would never get zero selectivity, the lowest value is actually: 1/number of table records.

Index selectivity should be recalculated periodically because inserting, updating and deleting records changes it. Firebird calculates it only when index is created and when database is restored from backup. So make sure you recompute statistics after big changes in data. It can be done by running:

SET STATISTICS INDEX index_name;

Using EXECUTE BLOCK in Firebird 2 or higher you can recalculate statistics for all indexes using this simple SQL statement:

set term !! ;

EXECUTE BLOCK AS
declare variable index_name VARCHAR(31);
BEGIN
for select RDB$INDEX_NAME from RDB$INDICES into :index_name do
execute statement 'SET statistics INDEX ' || :index_name || ';';
END!!

set term ; !!


Do you find this FAQ incorrect or incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page.

If you are a commercial tool maker and your tool features a great way to handle the issue written about in this FAQ, please check out our advertisement page.



All contents are copyright © 2007-2014 FirebirdFAQ.org unless otherwise stated in the text.


Links   Firebird   News   FlameRobin   Home Inventory powered by FB  
Add content   Advertise   About  

Categories
 Newbies
 SQL
 Installation and setup
 Backup and restore
 Performance
 Security
 Connectivity and API
 HOWTOs
 Errors and error codes
 Miscellaneous