Hello!
В сообщении от Tuesday 17 February 2009 13:45:34 Paolo Pisati написал(а):
> sqlite> .schema
> CREATE TABLE envelope (
> smtp_id unsigned int(10), date date, time time, mailq_sndr unsigned
> int(10), delivery_sndr unsigned int(10), customer_sndr unsigned int(10),
> rpath varchar(50), domain_rcvr varchar(25), user_rcvr varchar(25),
> size unsigned int(10), res unsigned int(10), msg varchar(250),
> ip unsigned int(10), vsmtp varchar(50), retries int(10)
> );
I think you can to try
1. Replace date date, time time to single REAL field:
date REAL NOT NULL DEFAULT (julianday('now'))
And use as
select date(date),time(date) from ...
2. Add NOT NULL constraint to all fields.
3. Do reduce indices count - SQLite is very fast engine and indexes is not
needed in most causes. Or
you can build complex indices. For example:
create table if not exists telephony_log (
nas_type text not null,
nas_name text not null,
username text not null,
port blob not null,
date_start real not null,
date_stop real not null,
duration integer not null,
origin text not null,
src integer not null,
dst integer not null,
code integer not null,
is_new integer not null default 1,
unique (nas_type,nas_name,port,date_start,date_stop,origin) on conflict ignore
);
CREATE INDEX if not exists telephony_log_date_start_idx on
telephony_log(date_start);
CREATE INDEX if not exists telephony_log_is_new_idx on
telephony_log(is_new);
CREATE INDEX if not exists telephony_log_src_date_start_idx on
telephony_log(src,date_start);
CREATE INDEX if not exists telephony_log_dst_date_start_idx on
telephony_log(dst,date_start);
SQLite did build autoindex by
(nas_type,nas_name,port,date_start,date_stop,origin) fields. In
complex index date fields must be after all other if you can planning to use
these for compares.
(src,date_start) is good for queries (src=... and date_start >...) or (src=...
and date_start
<...).
Use "explain query plan select ..." for testing queries.
4. Use in linux shell command
time my_command
for example time ./log_parser_ms_sqlite.pl
Best regards, Alexey.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
