Thanks, Joe and Rao for your replies. Yes, 3.2.3 was the first release
with ANALYZE. Rao, are you thinking of "EXPLAIN"? ;-)
I'm using the Python ORM "SQLObject" in my current project. It is
generating the "Slow query" below which is taking about 20 seconds to
execute.
None of the tables have many records. Timesheet has about 4000.
employee has about 15. Client has about 100. project has about 200.
The "Fast query, below, executes in about a second.
I have ANALYZED and VACUUM'd, but no change.
Unfortunately, I have little control over what the ORM generates unless
I take full control and write the query myself.
(Which is OK, except that my current little project's goal is to do
everything "The Object Way" as a sort of educational excersize to see
how well or poorly that works on a small project.)
But I'm curious what is "wrong" with the "Slow query".
Schema and EXPLAIN results are also included.
SQLite version is 3.3.3.
===========================================
Fast query:
SELECT
* , timesheet.id as id, description as desc
FROM Timesheet, Client, Project, Employee
WHERE
(Timesheet.employee_id = 9 OR '1' = '1') AND
Timesheet.client_id = Client.id AND
Timesheet.project_id = Project.id AND
Timesheet.employee_id = Employee.id AND
Timesheet.date >= '2006-04-02' AND
Timesheet.date <= '2006-04-12' AND
(client.client_name like '%%' OR
project.project_desc like '%%' OR
employee.last_name like '%%' OR
employee.first_name like '%%' OR
timesheet.description like '%%' OR
timesheet.internal_note like '%%')
ORDER BY date
=========================================
Slow query:
SELECT
timesheet.id,
timesheet.date,
timesheet.time,
timesheet.description,
timesheet.internal_note,
timesheet.emp_bill_status,
timesheet.travel_time,
timesheet.regular_time,
timesheet.onsite_support,
timesheet.phone_support,
timesheet.remote_support,
timesheet.employee_id,
timesheet.client_id,
timesheet.project_id,
timesheet.inoffice_support
FROM
project, employee, client, timesheet
WHERE
(
(timesheet.date >= '2006-04-01 00:00:00') AND
((timesheet.date <= '2006-04-11 00:00:00') AND
((employee.id = timesheet.employee_id) AND
((client.id = timesheet.client_id) AND
((project.id = timesheet.project_id) AND
((client.id = project.client_id) AND
((client.client_name LIKE '%%') OR
((employee.last_name LIKE '%%') OR
((timesheet.description LIKE '%%') OR
(project.project_desc LIKE '%%' )
)))))))));
===============================================
EXPLAIN Results:
addr opcode p1 p2 p3
---- -------------- ---------- ----------
---------------------------------
0 Goto 0 74
1 Integer 0 0
2 OpenRead 0 4
3 SetNumColumns 0 5
4 Integer 0 0
5 OpenRead 2 2
6 SetNumColumns 2 2
7 Integer 0 0
8 OpenRead 1 3
9 SetNumColumns 1 3
10 Integer 0 0
11 OpenRead 3 5
12 SetNumColumns 3 15
13 Rewind 0 69
14 Column 0 4
15 MustBeInt 1 68
16 NotExists 2 68
17 Rewind 1 68
18 Rewind 3 67
19 Column 3 1
20 String8 0 0 2006-04-01 00:00:00
21 Lt 355 66 collseq(BINARY)
22 Column 3 1
23 String8 0 0 2006-04-11 00:00:00
24 Gt 355 66 collseq(BINARY)
25 Rowid 1 0
26 Column 3 11
27 Ne 355 66 collseq(BINARY)
28 Rowid 2 0
29 Column 3 12
30 Ne 355 66 collseq(BINARY)
31 Rowid 0 0
32 Column 3 13
33 Ne 355 66 collseq(BINARY)
34 String8 0 0 %%
35 Column 2 1
36 Function 1 2 like(2)
37 If 0 50
38 String8 0 0 %%
39 Column 1 2
40 Function 1 2 like(2)
41 If 0 50
42 String8 0 0 %%
43 Column 3 3
44 Function 1 2 like(2)
45 If 0 50
46 String8 0 0 %%
47 Column 0 1
48 Function 1 2 like(2)
49 IfNot 1 66
50 Rowid 3 0
51 Column 3 1
52 Column 3 2
53 Column 3 3
54 Column 3 4
55 Column 3 5
56 Column 3 6
57 Column 3 7
58 Column 3 8
59 Column 3 9
60 Column 3 10
61 Column 3 11
62 Column 3 12
63 Column 3 13
64 Column 3 14
65 Callback 15 0
66 Next 3 19
67 Next 1 18
68 Next 0 14
69 Close 0 0
70 Close 2 0
71 Close 1 0
72 Close 3 0
73 Halt 0 0
74 Transaction 0 0
75 VerifyCookie 0 15
76 Goto 0 1
77 Noop 0 0
==============================================
Schema:
CREATE TABLE client (
id INTEGER PRIMARY KEY,
client_name VARCHAR(50)
);
CREATE TABLE employee (
id INTEGER PRIMARY KEY,
child_name VARCHAR(255),
last_name VARCHAR(25),
first_name VARCHAR(15),
mi VARCHAR(1),
home_phone VARCHAR(12),
cell_phone VARCHAR(12),
status VARCHAR(1) CHECK (status in ('a', 'i')),
email_address2 VARCHAR(50)
, type VARCHAR(1) CHECK (status in ('r', 'a')), completed_thru DATE);
CREATE TABLE project (
id INTEGER PRIMARY KEY,
project_desc VARCHAR(50),
project_hours DECIMAL(6, 2),
billable VARCHAR(1) CHECK (billable in ('y', 'n', 'c')),
client_id INT
);
CREATE TABLE timesheet (
id INTEGER PRIMARY KEY,
date DATE,
time DECIMAL(5, 2),
description VARCHAR(4096),
internal_note VARCHAR(4096),
emp_bill_status VARCHAR(1) CHECK (emp_bill_status in ('b', 'n', 'y',
NULL, '')),
travel_time DECIMAL(5, 2),
regular_time DECIMAL(5, 2),
onsite_support TINYINT,
phone_support TINYINT,
remote_support TINYINT,
employee_id INT,
client_id INT,
project_id INT,
inoffice_support TINYINT
);
