----- Original Message ----- From: "Ulrik Petersen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, November 16, 2004 11:10 PM
Subject: Re: [sqlite] Speeding up quer
Hi again,
There are no indexes in may tables. Please find the following schemas for my tables. Would it make more sense to convert my datetime columns to microtime?. What other recommendations would you make for these tables? CREATE TABLE users ( user_id INTEGER PRIMARY KEY, extn_no varchar(16) default NULL, username varchar(255) default NULL, password varchar(255) default NULL, admin NOT NULL default 'No', depthead NOT NULL default 'No', user NOT NULL default 'Yes', firstname varchar(255) default NULL, surname varchar(255) default NULL, job_title varchar(255) default NULL, user_email varchar(255) default NULL, deleted NOT NULL default 'No' );
CREATE TABLE call_data ( call_id INTEGER PRIMARY KEY, direction NOT NULL default 'Out', group_no varchar(16) default NULL, start_no varchar(16) default NULL, extn_no varchar(16) NOT NULL default '', trunk_no varchar(16) NOT NULL default '', trans NOT NULL default 'No', ddi varchar(16) default NULL, dest varchar(32) NOT NULL default '', dest_name varchar(255) default NULL, duration varchar(8) NOT NULL default '', ring_time varchar(5) default NULL, call_time datetime default NULL, cost decimal(10,2) default NULL, band varchar(10) default NULL, site_id varchar(10) default NULL );
You could change the columns that seem to be boolean values to INTEGER and store "0" and "1" instead of "Yes" and "No". Because of the way SQlite 2 stores these things (namely as strings), this will most likely save you some space.
Ulrik -- Ulrik Petersen, Denmark Homepage: <http://www.hum.aau.dk/~ulrikp/>
