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 );
Hi there,
I am have a problem with a query which may well have over 200,000 records.
I
have building a website using PHP and PHP is timing out after 30secs due
the
the size of the call_data table (I think). Is there anyway I can improve
the
following query so that it is faster. I think I am using sqlite 2.8.14
(not
sure).
SELECT call_id, C.extn_no AS extn_no, dest, dest_name, call_time, duration, cost, U.firstname AS firstname, U.surname AS surname FROM call_data as C LEFT JOIN users as U on C.extn_no = U.extn_no WHERE 1 = '1' AND julianday(call_time) >= julianday('2004-10-16 09:00:00') AND julianday(call_time) <= julianday('2004-11-16 17:29:59') AND direction = 'Out' ORDER BY cost desc LIMIT 0,16;
Two things:
1) Why do you have the "1 = '1'" expression? It's just wasting processor time.
2) You can do preprocessing of the julian dates, converting the expressions (like '2004-11-16 17:29:59') into whatever format is stored in the database, then comparing that directly. This will allow you to use an index on call_time.
Be aware, however, that maintaining an index can be time-consuming if you have many records going in and out.
Right now, it is my guess that every record in the table will have to be read, in order to apply the juliandate() function. Thus no index is used, and every record is read. This is just an uneducated guess, though -- I haven't checked the EXPLAIN output.
Ulrik P.
