Hello,
I have an SQL query which fetches the requested data from the database,
but is using too much resources when doing so.
I would be happy to get advice on how to think / what to look for when
trying to optimize a query, views, adding indexes, optimizing schema
design and so on.
So far I've figured out that I could add appropriate indexes since the
'explain query plan' indicates the query is using many automatic
indices, but I haven't added any extra yet since I would like to provide
you with the original schema.
I'm not allowed to send the real database, but I've created a way to
recreate the schema layout & add needed test data below. I'm sorry it's
quite long, but since I'm not sure what optimizations might be done I
rather describe too much than too little (but I've stripped some tables
of columns I find irrelevant for the query in question).
I've also created an extra view 'query_view' which contains the query
I'm trying to optimize.
In the real application the 'v4.t1_searchvalue = 16' in the query_view
is changing from time to time and not a static value.
SELECT v2_id FROM query_view;
Should result in v2_id's in order: 1, 4, 6, 8, 11, 3
I'm using SQLite 3.8.1
I would be very happy if you help me with the optimization, but even
happier if you explain how you think to reach the goal.
It's also possible to tweak the database/view layout, but it depends on
how big impact it would make to the processing speed.
Best regards,
Daniel
PRAGMA FOREIGN_KEYS = 0;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t4;
DROP TABLE IF EXISTS t5;
DROP TABLE IF EXISTS t6;
DROP TABLE IF EXISTS t7;
DROP TABLE IF EXISTS t8;
DROP TABLE IF EXISTS t9;
DROP TABLE IF EXISTS t10;
DROP TABLE IF EXISTS t11;
DROP TABLE IF EXISTS t12;
DROP TABLE IF EXISTS t13;
DROP TABLE IF EXISTS t14;
DROP TABLE IF EXISTS t15;
DROP TABLE IF EXISTS t16;
DROP VIEW IF EXISTS v1;
DROP VIEW IF EXISTS v2;
DROP VIEW IF EXISTS v3;
DROP VIEW IF EXISTS v4;
PRAGMA FOREIGN_KEYS = 1;
CREATE TABLE t1 (
id INTEGER PRIMARY KEY,
t1_searchvalue INT
);
CREATE TABLE t2 (
id INTEGER PRIMARY KEY,
t2_searchvalue INTEGER NOT NULL,
t1_id INT NOT NULL,
CONSTRAINT "FK_t2" FOREIGN KEY( t1_id ) REFERENCES t1 ( id ) ON
DELETE CASCADE
);
CREATE TABLE t3 (
id INT NOT NULL,
t3_value_1 varchar( 10 ) NOT NULL,
disabled BOOLEAN,
t3_value_2 BOOLEAN,
CONSTRAINT "PK_t3" PRIMARY KEY(id)
);
CREATE TABLE t4 (
id INTEGER PRIMARY KEY,
t3_id INT NOT NULL,
t4_value_1 VARCHAR( 10 ) NOT NULL,
searchvalue INT NOT NULL,
t2_id INT NOT NULL,
CONSTRAINT "FK_t4_002" FOREIGN KEY( t3_id ) REFERENCES t3 ( id ),
CONSTRAINT "FK_t4_003" FOREIGN KEY( t2_id ) REFERENCES t2 ( id ) ON
DELETE CASCADE
);
CREATE TABLE t5 (
id INTEGER PRIMARY KEY,
name VARCHAR(20) NOT NULL
);
CREATE TABLE t6 (
id INTEGER PRIMARY KEY,
starttime DATETIME NOT NULL,
t5_id INT NOT NULL,
t6_value_1 BOOLEAN NOT NULL,
t6_value_2 BOOLEAN NOT NULL DEFAULT 0,
CONSTRAINT 'FK_t6_001' FOREIGN KEY( t5_id ) REFERENCES t5 ( id )
);
CREATE TABLE t7 (
key_part_1 INT NOT NULL,
key_part_2 INT NOT NULL,
last_seen DATETIME NOT NULL,
t1_searchvalue INT NOT NULL,
t2_searchvalue INT NOT NULL,
t4_searchvalue INT NOT NULL,
CONSTRAINT "PK_t7" PRIMARY KEY(key_part_1, key_part_2)
);
CREATE TABLE t8 (
t6_id INT NOT NULL,
t7_key_part_1 INT NOT NULL,
t7_key_part_2 INT NOT NULL,
CONSTRAINT "FK_t8_001" FOREIGN KEY( t6_id ) REFERENCES t6 ( id ) ON
DELETE CASCADE,
CONSTRAINT "FK_t8_002" FOREIGN KEY(t7_key_part_1, t7_key_part_2)
REFERENCES t7 ( key_part_1, key_part_2)
);
CREATE TABLE t9 (
id INTEGER PRIMARY KEY,
name VARCHAR(30),
enabled BOOLEAN NOT NULL
);
CREATE TABLE t10 (
t4_id INT NOT NULL,
t9_id INT NOT NULL,
CONSTRAINT 'PK_t10' PRIMARY KEY( t4_id, t9_id),
CONSTRAINT 'FK_t10_001' FOREIGN KEY( t4_id ) REFERENCES t4 ( id )
ON DELETE CASCADE,
CONSTRAINT 'FK_t10_002' FOREIGN KEY( t9_id ) REFERENCES t9 ( id )
ON DELETE CASCADE
);
CREATE TABLE t11 (
t3_id INT NOT NULL,
t9_id INT NOT NULL,
CONSTRAINT 'PK_t11' PRIMARY KEY( t3_id, t9_id),
CONSTRAINT 'FK_t11_001' FOREIGN KEY( t3_id ) REFERENCES t3 ( id )
ON DELETE CASCADE,
CONSTRAINT 'FK_t11_002' FOREIGN KEY( t9_id ) REFERENCES t9 ( id )
ON DELETE CASCADE
);
CREATE TABLE t12 (
t9_id INT NOT NULL,
t3_id INT NOT NULL,
CONSTRAINT 'PK_t12' PRIMARY KEY( t9_id, t3_id),
CONSTRAINT 'FK_t12_001' FOREIGN KEY( t9_id ) REFERENCES t9 ( id )
ON DELETE CASCADE,
CONSTRAINT 'FK_t12_002' FOREIGN KEY( t3_id ) REFERENCES t3 ( id )
ON DELETE CASCADE
);
CREATE TABLE t13 (
t9_id INT NOT NULL,
day INT NOT NULL,
starttime TIME NOT NULL,
endtime TIME NOT NULL,
CONSTRAINT 'PK_t13' PRIMARY KEY( t9_id, starttime, day),
CONSTRAINT 'FK_t13' FOREIGN KEY( t9_id ) REFERENCES t9 ( id ) ON
DELETE CASCADE
);
CREATE TABLE t14 (
t9_id INT NOT NULL,
t5_id INT NOT NULL,
CONSTRAINT 'PK_t14' PRIMARY KEY( t9_id, t5_id ),
CONSTRAINT 'FK_t14_001' FOREIGN KEY( t9_id) REFERENCES t9 ( id ) ON
DELETE CASCADE,
CONSTRAINT 'FK_t14_002' FOREIGN KEY( t5_id ) REFERENCES t5 ( id )
ON DELETE CASCADE
);
CREATE TABLE t15 (
t6_id INT NOT NULL,
t4_id INT NOT NULL,
CONSTRAINT "FK_t15_001" FOREIGN KEY( t6_id ) REFERENCES t6 ( id )
ON DELETE CASCADE,
CONSTRAINT "FK_t15_001" FOREIGN KEY( t4_id ) REFERENCES t4 ( id )
);
CREATE TABLE t16(
t5_id INT NOT NULL,
t16_value_1 INT NOT NULL,
priority INT NOT NULL,
t16_value_2 INT NOT NULL,
t16_value_3 INT NOT NULL,
t16_value_4 INT NOT NULL,
t16_value_5 INT NOT NULL,
t16_value_6 INT NOT NULL,
t16_value_7 INT NOT NULL,
t16_value_8 INT NOT NULL,
t16_value_9 INT NOT NULL,
t16_value_10 INT NOT NULL,
t16_value_11 INT NOT NULL
);
CREATE VIEW v1 AS
SELECT t6.id,
t4.t3_id,
t5_id,
t6_value_1,
t5.name AS t5_name,
t4.id AS t4_id,
starttime,
t6.t6_value_2
FROM t6, t15, t4, t5
WHERE t6.id = t15.t6_id
AND t15.t4_id = t4.id
AND t6.t5_id = t5.id
UNION
SELECT t6.id,
t4.t3_id,
t5_id,
t6_value_1,
t5.name AS t5_name,
t4.id AS t4_id,
starttime,
t6.t6_value_2
FROM t6, t8, t7, t4, t2, t1, t5
WHERE t6.id = t8.t6_id
AND t8.t7_key_part_1 = t7.key_part_1
AND t6.t5_id = t5.id
AND t4.t2_id = t2.id
AND t2.t1_id = t1.id
AND t7.t1_searchvalue = t1.t1_searchvalue
AND t7.t2_searchvalue = t2.t2_searchvalue
AND t7.t4_searchvalue = t4.searchvalue;
CREATE VIEW v2 AS
SELECT id,
t3_id,
v1.t5_id,
t6_value_1,
t5_name,
t4_id,
starttime,
priority,
t16_value_6,
t16_value_7,
t16_value_2,
t16_value_5,
t16_value_1,
t16_value_3,
t16_value_4,
t16_value_8,
t16_value_9,
t16_value_11,
v1.t6_value_2,
t16_value_10
FROM v1, t16
WHERE v1.t5_id = t16.t5_id;
CREATE VIEW v3 AS
SELECT DISTINCT t4_id, t3_id, t9_id
FROM ( SELECT t4.id AS t4_id,
t12.t3_id AS t3_id,
t9.id AS t9_id
FROM t4, t10, t9, t12
WHERE t4.id= t10.t4_id
AND t10.t9_id = t9.id
AND t9.id = t12.t9_id
AND t9.enabled = 1
UNION
SELECT t4.id AS t4_id,
t12.t3_id,
t9.id AS t9_id
FROM t4, t3, t11, t9, t12
WHERE t4.t3_id = t3.id
AND t3.id = t11.t3_id
AND t11.t9_id = t9.id
AND t9.id = t12.t9_id
AND t9.enabled = 1 )
WHERE t9_id IN (SELECT id
FROM t9
WHERE id NOT IN (SELECT t9_id FROM t13)
UNION
SELECT id
FROM t9, t13
WHERE t9.id = t13.t9_id
AND t13.day = strftime('%w', 'now', 'localtime')
AND t13.starttime <= strftime('%H:%M', 'now',
'localtime')
AND t13.endtime > strftime('%H:%M', 'now', 'localtime')
)
AND t3_id NOT IN (SELECT id FROM t3 WHERE disabled = 1);
CREATE VIEW v4 AS
SELECT t4.id AS t4_id,
t1.id AS t1_id,
t2.id AS t2_id,
t1.t1_searchvalue,
t2.t2_searchvalue,
t4.searchvalue
FROM t1, t2, t4
WHERE t1.id = t2.t1_id
AND t2.id = t4.t2_id;
--create testdata
pragma recursive_triggers = 1;
CREATE TRIGGER ins_trigger_1 after insert on t3
when new.id < 32
BEGIN
INSERT INTO t3 VALUES(new.id+1, new.t3_value_1, 0, 0);
END;
CREATE TRIGGER ins_trigger_2 after insert on t1
BEGIN
INSERT INTO t2 VALUES(NULL, 1 , new.id);
INSERT INTO t2 VALUES(NULL, 2 , new.id);
INSERT INTO t2 VALUES(NULL, 3 , new.id);
END;
CREATE TRIGGER ins_trigger_3 after insert on t2
BEGIN
INSERT INTO t4 VALUES(NULL,new.t1_id,'text',0,new.id);
END;
CREATE TRIGGER ins_trigger_4 after insert on t4
when new.searchvalue < 29
BEGIN
INSERT INTO t4
VALUES(NULL,new.t3_id,'text',new.searchvalue+1,new.t2_id);
END;
CREATE TRIGGER ins_trigger_5 after insert on t1
WHEN NEW.t1_searchvalue < 16
BEGIN
INSERT INTO t1 VALUES(NULL, new.t1_searchvalue+1);
END;
CREATE TRIGGER ins_trigger_6 after insert on t5
WHEN NEW.id < 32
BEGIN
INSERT INTO t5 VALUES(NULL, 'text');
END;
CREATE TRIGGER ins_trigger_7 after insert on t7
WHEN NEW.key_part_1 < 1000
BEGIN
INSERT INTO t7 VALUES(new.key_part_1+1, 1, datetime(), 1, 1, 1);
END;
CREATE TRIGGER ins_trigger_8 after insert on t16
WHEN NEW.t5_id < 32
BEGIN
INSERT INTO t16 VALUES(new.t5_id+1, 1, new.t5_id+1, 1, 0, 0, 1, 1,
1, 1, 0, 0, 0);
END;
INSERT INTO t3 VALUES(1, 'text', 0, 0);
INSERT INTO t5 VALUES(1, 'text');
INSERT INTO t1 VALUES(NULL, 1);
INSERT INTO t7 VALUES(1, 1, datetime(), 1, 1, 1);
INSERT INTO t16 VALUES(1, 1, 1, 1, 0, 0, 1, 1, 1, 1, 0, 0, 0);
pragma recursive_triggers = 0;
DROP TRIGGER IF EXISTS ins_trigger_1;
DROP TRIGGER IF EXISTS ins_trigger_2;
DROP TRIGGER IF EXISTS ins_trigger_3;
DROP TRIGGER IF EXISTS ins_trigger_4;
DROP TRIGGER IF EXISTS ins_trigger_5;
DROP TRIGGER IF EXISTS ins_trigger_6;
DROP TRIGGER IF EXISTS ins_trigger_7;
DROP TRIGGER IF EXISTS ins_trigger_8;
INSERT INTO t9 VALUES(1, 'conn 1 (enabled)', 1);
INSERT INTO t14 VALUES(1, 2);
INSERT INTO t9 VALUES(2, 'conn 2 (disabled)', 0);
INSERT INTO t14 VALUES(2, 2);
INSERT INTO t9 VALUES(3, 'conn 3 (inside constraint)', 1);
INSERT INTO t14 VALUES(3, 2);
INSERT INTO t13 VALUES(3, 0, '00:00', '23:59');
INSERT INTO t13 VALUES(3, 1, '00:00', '23:59');
INSERT INTO t13 VALUES(3, 2, '00:00', '23:59');
INSERT INTO t13 VALUES(3, 3, '00:00', '23:59');
INSERT INTO t13 VALUES(3, 4, '00:00', '23:59');
INSERT INTO t13 VALUES(3, 5, '00:00', '23:59');
INSERT INTO t13 VALUES(3, 6, '00:00', '23:59');
INSERT INTO t9 VALUES(4, 'conn 4 (outside constraint)', 1);
INSERT INTO t14 VALUES(4, 2);
INSERT INTO t13 VALUES(4, 0, '04:00', '04:01');
INSERT INTO t12 VALUES(1, 15);
INSERT INTO t12 VALUES(2, 14);
INSERT INTO t12 VALUES(3, 13);
INSERT INTO t12 VALUES(4, 12);
INSERT INTO t11 VALUES(16, 1);
INSERT INTO t11 VALUES(16, 2);
INSERT INTO t11 VALUES(16, 3);
INSERT INTO t11 VALUES(16, 4);
INSERT INTO t9 VALUES(5, 'conn 4 (added to node)', 1);
INSERT INTO t12 VALUES(5, 11);
INSERT INTO t14 VALUES(5, 2);
INSERT INTO t10 VALUES(1, 5);
INSERT INTO t6 VALUES(1, '2013-11-05 14:47:02', 2, 0, 0);
INSERT INTO t15 VALUES(1, 1);
INSERT INTO t6 VALUES(2, '2013-11-05 14:47:02', 3, 0, 0);
INSERT INTO t15 VALUES(2, 2);
UPDATE t16 SET priority = 2 WHERE t5_id = 4;
INSERT INTO t6 VALUES(3, '2013-11-05 14:47:02', 4, 0, 0);
INSERT INTO t15 VALUES(3, 2);
INSERT INTO t6 VALUES(4, '2013-11-05 14:48:02', 2, 0, 0);
INSERT INTO t8 VALUES(4, 1, 1);
UPDATE t7 SET t1_searchvalue = 16 where key_part_1 = 1;
INSERT INTO t6 VALUES(5, '2013-11-05 14:48:02', 5, 0, 0);
INSERT INTO t8 VALUES(5, 1, 1);
INSERT INTO t6 VALUES(6, '2013-11-05 14:47:02', 2, 0, 0);
INSERT INTO t15 VALUES(6, 91);
INSERT INTO t6 VALUES(7, '2013-11-05 14:47:02', 2, 0, 0);
INSERT INTO t15 VALUES(7, 181);
INSERT INTO t6 VALUES(8, '2013-11-05 14:47:02', 2, 0, 0);
INSERT INTO t15 VALUES(8, 271);
INSERT INTO t6 VALUES(9, '2013-11-05 14:47:02', 2, 0, 0);
INSERT INTO t15 VALUES(9, 361);
UPDATE t16 SET priority = 2 WHERE t5_id = 6;
INSERT INTO t6 VALUES(10, '2013-11-05 14:47:02', 6, 0, 0);
INSERT INTO t15 VALUES(10, 361);
INSERT INTO t6 VALUES(11, '2013-11-05 14:47:02', 2, 0, 0);
INSERT INTO t15 VALUES(11, 451);
INSERT INTO t6 VALUES(12, '2013-11-05 14:47:02', 1, 0, 0);
INSERT INTO t15 VALUES(12, 541);
INSERT INTO t6 VALUES(13, '2013-11-05 14:47:02', 2, 0, 0);
INSERT INTO t15 VALUES(13, 541);
CREATE VIEW query_view AS
SELECT temp_table.v2_id,
temp_table.t4_value_1,
temp_table.t3_id,
v4.t2_searchvalue AS t2_searchvalue,
v4.searchvalue AS t4_searchvalue,
t16_value_1,
t16_value_2,
temp_table.priority AS priority,
strftime('%s', datetime('now'), 'localtime') - strftime('%s',
temp_table.starttime) AS timer,
t3_value_2,
t6_value_1,
t3.t3_value_1,
t16_value_3,
t16_value_4,
t16_value_9,
t16_value_8,
t16_value_10,
t6_value_2,
(t5.id>=1000 AND t5.id<2000) as is_special_type
FROM v4,
(SELECT v2.id AS v2_id, v2.t3_id, priority, t5_id, t6_value_1,
t4.t4_value_1, t16_value_1, t16_value_2, t16_value_3, t16_value_4,
t16_value_9, t16_value_8, t6_value_2, t16_value_10, starttime, t4.id AS
t4_id
FROM v2, t4
WHERE v2.t4_id = t4.id ) AS temp_table,
(SELECT t3_id, min(priority) AS priority
FROM v1, t16
WHERE v1.t5_id = t16.t5_id
GROUP BY t3_id) AS mintable,
t5,
t3
WHERE temp_table.t3_id = mintable.t3_id
AND temp_table.priority = mintable.priority
AND t5.id = temp_table.t5_id
AND t3.id = temp_table.t3_id
AND v4.t4_id = temp_table.t4_id
AND temp_table.v2_id IN (SELECT v1.id
FROM v1, v3, v4
WHERE v1.t3_id = v3.t3_id
AND v1.t5_id IN (SELECT t5_id
FROM t14
WHERE t14.t9_id = v3.t9_id)
AND v3.t4_id = v4.t4_id
AND v4.t1_searchvalue = 16
UNION
SELECT v1.id AS v1_id
FROM v1, v4, t4
WHERE v1.t3_id = t4.t3_id
AND t4.id = v4.t4_id
AND v4.t1_searchvalue = 16 )
ORDER BY priority, t5_id
LIMIT 20;
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
