Re: [sqlite] optimize table

[Ken & Deb Allen]

I cannot speak for SQLITE for certain, but I know with a number of 
commercial RDBMS packages the index is not used unless it contains a 
sufficient degree of distribution. Microsoft SQL Server, for example, 
is quite explicit in stating that if the distribution of values across 
the range of records in an index falls below a certain level, then the 
index is ignored, which is why you are used to put the most dynamic 
column first in a multi-column index. I do not know if SQLITE performs 
any of this analysis and makes decisions on whether to use the index or 
not, but it is possible, especially if a sort is involved, that the 
index is not helping much if all records have one of two values, and as 
the majority of he records have the same value, you are looking only 
for the minority records. I do not know if using a date/time for this 
files will speed it up or not, since your statistical inclusion query 
will be for records "WHERE statistic IS NULL" or "WHERE statistic = 0", 
and as the database grows in size this will be the minority set of 
records.

One 'trick' I have seen in the past was to create a second table that 
contained an entry for new records, also keyed by the same "id"; as 
each sale is created, an entry is also made in that table, possibly by 
a trigger. You select the records for statistics using a JOIN (inner), 
which will only return records that are in both tables. As the records 
are processed, the record is removed from the second table, and 
therefore the records will not be part of the JOIN in the future. In 
some databases this may be faster than using an index; I have not 
experimented with this in SQLITE, however.

-ken
On 15-Apr-05, at 4:30 AM, msaka msaka wrote:
i dont need working with dates and times..
i only set 1 records on which was done..
which index is better to create for this problem?
must i cut db files? or speed of statistic will be same now and after 
1000 000 records?


-----Pôvodná správa-----
Od: Gert Rijs [mailto:[EMAIL PROTECTED]
Odoslané: 15. apríla 2005 7:13
Komu: sqlite-users@sqlite.org
Predmet: Re: [sqlite] optimize table
msaka msaka wrote:
CREATE TABLE sale(
   id INTEGER NOT NULL UNIQUE PRIMARY KEY,
   bill_no integer,
   bill_item_no integer,
   item_name varchar(20),
   qty integer,
   amount DOUBLE,
   statistic integer
)

Why don't you replace "statistic" with a timestamp formatted like
yyyymmdd, perhaps add the time as well. If you add an index on it you
can efficiently retrieve the rows for certain date periods without
needing to update the table.
Gert