Paste: sqlite3perf_log

Author: hoverhell
Mode: factor
Date: Wed, 19 Nov 2014 13:38:19
Plain Text | 
| 162338 hhell@dev01f:~:0$ sqlite3 perf1t.db
SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
sqlite> CREATE INDEX filt_word_node_id ON file(word, node_id);
Error: no such table: main.file
sqlite> CREATE INDEX "filt_word_node_id" ON "filt" ("word", "node_id"); VACUUM; ANALYZE;
sqlite> .timer ON
sqlite> select node.loc, node.weight from node
   ...> inner join filt on (node.id = filt.node_id)
   ...> inner join filt T5 on (node.id = T5.node_id)
   ...> where (filt.word = 'aaa' and T5.word = 'aasvogel')
   ...> order by node.weight desc limit 10;
abaxial aatech aatech abeigh aaa ablators abbate abatised abbate abannition aasvogel abbe aatech ablaqueate abbate abadite aasvogel|131.4
aasvogel abided aatech abbate ablators abatised abkhasian abbe abey abaca aatech aaa abbe abaca abaca aasvogel abrasiometer abadite abadite abscessroot abadite abaya abandonees abdominocentesis|112.2
abakumov abatised abandonees abaya aaa abaxial aasvogel abaca abakumov abigael abigael aatech abandonees aboideau abadite abbate abkhasian abaca abbe abaca abadite abbreviations aboideau ablators abandonees absents absents abided abatised|108.3
abadite abandonees acroter abaca acculturize abdominocentesis abaxial aasvogel aborter abdominocentesis abaca ablaqueate abaca abadite aaa abscessroot abannition aaa abadite abbe abollas acculturize abey aaa abigael aatech|101.9
abkhasian abey abaca abbotships aasvogel abided abatised abaca abaca abaca aatech aaa abannition abkhasian abannition abbate abbotships abigael abaxial abaca abroma aboideau aatech ablators abannition abbate abaca abigael|98.0
abadite ablators abannition abatised aasvogel abatised abandonees aaa abannition abannition abaxial aatech abakumov abakumov abbe abandonees abolla abkhasian|97.1
abrogations aasvogel abided aaa abaca ablators aaa abeigh abbotships aaa|96.4
aasvogel abadite abrasiometer abannition abbotships abaca aatech aaa abolla|94.8
abannition abaya aatech abadite aaa aasvogel aaa abakumov abannition abaya ablators abbe abthainrie abbe abadite absa aaa abbate abandonees abbe|92.4
aasvogel abbate abatised abakumov abatised abannition aaa abadite abaya abaya abakumov abatised aaa aasvogel abaca abaca abbate aasvogel aaa abadite ablaqueate ablaqueate abaxial abey absentmindedly abey aatech|91.3
Run Time: real 0.563 user 0.536034 sys 0.024002
sqlite> select node.loc, node.weight from node
   ...> inner join filt on (node.id = filt.node_id)
   ...> inner join filt T5 on (node.id = T5.node_id)
   ...> where (filt.word = 'aaa' and T5.word = 'aasvogel')
   ...> order by node.weight desc limit 10;
abaxial aatech aatech abeigh aaa ablators abbate abatised abbate abannition aasvogel abbe aatech ablaqueate abbate abadite aasvogel|131.4
aasvogel abided aatech abbate ablators abatised abkhasian abbe abey abaca aatech aaa abbe abaca abaca aasvogel abrasiometer abadite abadite abscessroot abadite abaya abandonees abdominocentesis|112.2
abakumov abatised abandonees abaya aaa abaxial aasvogel abaca abakumov abigael abigael aatech abandonees aboideau abadite abbate abkhasian abaca abbe abaca abadite abbreviations aboideau ablators abandonees absents absents abided abatised|108.3
abadite abandonees acroter abaca acculturize abdominocentesis abaxial aasvogel aborter abdominocentesis abaca ablaqueate abaca abadite aaa abscessroot abannition aaa abadite abbe abollas acculturize abey aaa abigael aatech|101.9
abkhasian abey abaca abbotships aasvogel abided abatised abaca abaca abaca aatech aaa abannition abkhasian abannition abbate abbotships abigael abaxial abaca abroma aboideau aatech ablators abannition abbate abaca abigael|98.0
abadite ablators abannition abatised aasvogel abatised abandonees aaa abannition abannition abaxial aatech abakumov abakumov abbe abandonees abolla abkhasian|97.1
abrogations aasvogel abided aaa abaca ablators aaa abeigh abbotships aaa|96.4
aasvogel abadite abrasiometer abannition abbotships abaca aatech aaa abolla|94.8
abannition abaya aatech abadite aaa aasvogel aaa abakumov abannition abaya ablators abbe abthainrie abbe abadite absa aaa abbate abandonees abbe|92.4
aasvogel abbate abatised abakumov abatised abannition aaa abadite abaya abaya abakumov abatised aaa aasvogel abaca abaca abbate aasvogel aaa abadite ablaqueate ablaqueate abaxial abey absentmindedly abey aatech|91.3
Run Time: real 0.568 user 0.536034 sys 0.028002
sqlite> VACUUM; ANALYZE;
Run Time: real 25.466 user 9.792612 sys 3.124195
sqlite> select node.loc, node.weight from node inner join filt on (node.id = filt.node_id) inner join filt T5 on (
(node.id = T5.node_id) inner join filt T6 on (node.id = T6.node_id) where (filt.word = 'aaa' and T5.word = 'aasvogel' and T6.word = 'abaca') order by node.weight desc limit 10;
aasvogel abided aatech abbate ablators abatised abkhasian abbe abey abaca aatech aaa abbe abaca abaca aasvogel abrasiometer abadite abadite abscessroot abadite abaya abandonees abdominocentesis|112.2
abakumov abatised abandonees abaya aaa abaxial aasvogel abaca abakumov abigael abigael aatech abandonees aboideau abadite abbate abkhasian abaca abbe abaca abadite abbreviations aboideau ablators abandonees absents absents abided abatised|108.3
abadite abandonees acroter abaca acculturize abdominocentesis abaxial aasvogel aborter abdominocentesis abaca ablaqueate abaca abadite aaa abscessroot abannition aaa abadite abbe abollas acculturize abey aaa abigael aatech|101.9
abkhasian abey abaca abbotships aasvogel abided abatised abaca abaca abaca aatech aaa abannition abkhasian abannition abbate abbotships abigael abaxial abaca abroma aboideau aatech ablators abannition abbate abaca abigael|98.0
abrogations aasvogel abided aaa abaca ablators aaa abeigh abbotships aaa|96.4
aasvogel abadite abrasiometer abannition abbotships abaca aatech aaa abolla|94.8
aasvogel abbate abatised abakumov abatised abannition aaa abadite abaya abaya abakumov abatised aaa aasvogel abaca abaca abbate aasvogel aaa abadite ablaqueate ablaqueate abaxial abey absentmindedly abey aatech|91.3
abaca aatech aasvogel abaxial abatised abbate aaa aaa aatech abadite aatech|90.3
abdominocentesis abadite accreditee abollas abandonees aasvogel abkhasian abeigh aasvogel abaca abannition abakumov abadite abatised abbotships abbate abaca abbreviations aasvogel ablutions abaya abaya abolla aaa aaa abaca abaya|89.9
abbate abaca aasvogel aatech abaca abandonees abaxial aasvogel abaca aasvogel aaa abkhasian abadite ablutions abannition abaca abeigh abandonees abaxial aasvogel abey aasvogel abaxial abaxial ablaqueate abbotships aasvogel aatech aaa|89.6
Run Time: real 0.672 user 0.616039 sys 0.052003

Annotation: sqlite3perf_log query plan

Author: hoverhell
Mode: factor
Date: Wed, 19 Nov 2014 14:28:38
Plain Text | 
sqlite> explain query plan select node.loc, node.weight from node inner join filt on (node.id = filt.node_id) inner join filt T5 on (node.id = T5.node_id) inner join filt T6 on (node.id = T6.node_id) where (filt.word = 'aaa' and T5.word = 'aasvogel' and T6.word = 'abaca') order by node.weight desc limit 10;
0|0|1|SEARCH TABLE filt USING COVERING INDEX filt_word_node_id (word=?)
0|1|0|SEARCH TABLE node USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE filt AS T5 USING COVERING INDEX filt_word_node_id (word=? AND node_id=?)
0|3|3|SEARCH TABLE filt AS T6 USING COVERING INDEX filt_word_node_id (word=? AND node_id=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY

New Annotation

Summary:
Author:
Mode:
Body: