Table Statistics

Table statistics are used by the SQL optimizer and encompass the following data:

Entity	Parameter	Description	Location
Columns	unique	Approximate proportion of uniqueness in the column. Where 1 means all fields are unique and 0 would (theoretically) mean that all fields were equal	system.syscolumns.unique
	avg	Approximate average size of the column's fields' data. Blob and text columns are not calculated.	system.syscolumns.avg
Indices	height	Height of the B+Tree	system.sysindexes.height
	dpages	Number of data leaf pages in the B+Tree.	system.sysindexes.dpages
Tables	count	Approximate number of rows	system.systables.count

Statistics are stored individually on each instance for local data only. That is, these statistics are not aggregated.

Query Optimizer

The SQL query optimizer is used to convert SQL queries into DQL. It works by generating all combinations of query plan and quickly analyzing which would take the least amount of processing time and data page hits. This plan is then presented to the user, or executed.

The query optimizer will evaluate approximately how many rows will be returned for each table. Then, depending on selection can determine most optimal ordering of the joins to open the minimal amount of pages.

For example

SELECT t1.col1,t2.col2
  FROM t1 JOIN t2 ON t1.col1=t2.col1
  WHERE t2.col3='some value' AND t1.col2>5;

The query optimizer will estimate how many rows t1 selection will return. Then evaluate t2 selection, say col3 has a unique key, then the selection will be one row. So it would be more effective to select from t2 first, then output the join against t1.

Related