Joe Wilson <[EMAIL PROTECTED]> wrote:
--- "Sergey M. Brytsko" wrote:
> But what about the following values:
>
> 1 1 2 2 100 100 100 100 100 100 100 100 100 100 100 111 111
I guess SQLite's query optimizer could take the cardinality of the
column into account via its ANALYZE statistics for inequality comparisons.
It's just the small matter of someone writing the code to do it.
** The result is a single row of the sqlite_stat1 table. The first
** two columns are the names of the table and index. The third column
** is a string composed of a list of integer statistics about the
** index. The first integer in the list is the total number of entires
** in the index. There is one additional integer in the list for each
** column of the table. This additional integer is a guess of how many
** rows of the table the index will select. If D is the count of distinct
** values and K is the total number of rows, then the integer is computed
** as:
**
** I = (K+D-1)/D
**
** If K==0 then no entry is made into the sqlite_stat1 table.
** If K>0 then it is always the case the D>0 so division by zero
** is never possible.
Joe, you can go one further...
By adding an additional table (or another set of rows).... to store column
level stats based upon an individual columns values. (Oracle calls these
histograms). In that way the where clause values can be used to gain even
better index selection.
Table A has 1 1 100 100 100 100 100 100 100 100 100 101 102
select * from a where C=100... -> Ignor index, full scan
select * from a where C= 101 -> Use index.
