Lloyd Thomas wrote:
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;
Lloydie-T
You don't need do convert the times in juliandate, you can compare the strings; slsto what is "1 = '1'" ???
What's the cardinality of call_data and of users? Did you created some indexes on the tables?
Paolo
