Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE Table1 (
- Key INTEGER NOT NULL,
- ... several other fields ...,
- Status CHAR(1) NOT NULL,
- Selection VARCHAR NULL,
- CONSTRAINT PK_Table1 PRIMARY KEY (Key ASC))
- CREATE Table2 (
- Key INTEGER NOT NULL,
- Key2 INTEGER NOT NULL,
- ... a few other fields ...,
- CONSTRAINT PK_Table2 PRIMARY KEY (Key ASC, Key2 ASC))
- CREATE INDEX IDX_Table1_Status ON Table1 (Status ASC, Key ASC)
- CREATE INDEX IDX_Table1_Selection ON Table1 (Selection ASC, Key ASC)
- SELECT COUNT(*) FROM Table1
- Time: 105 sec
- QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Selection(~1000000 rows)
- SELECT COUNT(Key) FROM Table1
- Time: 153 sec
- QP: SCAN TABLE Table1 (~1000000 rows)
- SELECT * FROM Table1 WHERE Key = 5123456
- Time: 5 ms
- QP: SEARCH TABLE Table1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
- SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1
- Time: 16 sec
- QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Status (Status=?) (~3 rows)
- SELECT * FROM Table1 WHERE Selection = 'SomeValue' AND Key > 5123456 LIMIT 1
- Time: 9 ms
- QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Selection (Selection=?) (~3 rows)
- SELECT COUNT(*) FROM Table2
- Time: 528 sec
- QP: SCAN TABLE Table2 USING COVERING INDEX sqlite_autoindex_Table2_1(~1000000 rows)
- SELECT COUNT(Key) FROM Table2
- Time: 249 sec
- QP: SCAN TABLE Table2 (~1000000 rows)
- SELECT * FROM Table2 WHERE Key = 5123456 AND Key2 = 0
- Time: 7 ms
- QP: SEARCH TABLE Table2 USING INDEX sqlite_autoindex_Table2_1 (Key=? AND Key2=?) (~1 rows)
- SELECT COUNT(*) FROM Table1
- Time: 104 sec
- QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Selection(~7848023 rows)
- SELECT COUNT(Key) FROM Table1
- Time: 151 sec
- QP: SCAN TABLE Table1 (~7848023 rows)
- SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1
- Time: 5 ms
- QP: SEARCH TABLE Table1 USING INTEGER PRIMARY KEY (rowid>?) (~196200 rows)
- SELECT COUNT(*) FROM Table2
- Time: 529 sec
- QP: SCAN TABLE Table2 USING COVERING INDEX sqlite_autoindex_Table2_1(~51152542 rows)
- SELECT COUNT(Key) FROM Table2
- Time: 249 sec
- QP: SCAN TABLE Table2 (~51152542 rows)
- CREATE INDEX IDX_Table1_Key ON Table1 (Key ASC)
- SELECT COUNT(*) FROM Table1
- Time: 4 sec
- QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Key(~1000000 rows)
- SELECT COUNT(Key) FROM Table1
- Time: 167 sec
- QP: SCAN TABLE Table2 (~1000000 rows)
- SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1
- Time: 17 sec
- QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Status (Status=?) (~3 rows)
- CREATE TABLE Table1 (
- Key INTEGER PRIMARY KEY ASC NOT NULL,
- ... several other fields ...,
- Status CHAR(1) NOT NULL,
- Selection VARCHAR NULL)
- SELECT COUNT(*) FROM Table1
- Time: 6 sec
- QP: SCAN TABLE Table1 USING COVERING INDEX IDX_Table1_Selection(~1000000 rows)
- SELECT COUNT(Key) FROM Table1
- Time: 28 sec
- QP: SCAN TABLE Table1 (~1000000 rows)
- SELECT * FROM Table1 WHERE Status = 73 AND Key > 5123456 LIMIT 1
- Time: 10 sec
- QP: SEARCH TABLE Table1 USING INDEX IDX_Table1_Status (Status=?) (~3 rows)
- SELECT MAX(_ROWID_) FROM "table" LIMIT 1;
- select count(my_column) from (select * from my_table) group by my_column having count(my_column) > 1; -- < 1 second
- -- For reference, the following query took > 10 seconds
- select count(my_column) from my_table group by my_column having count(my_column) > 1; -- > 10 seconds
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement