## Summary **No review yet** ## Minimized query ```sql PRAGMA index_list(users); PRAGMA table_info(users); PRAGMA application_id; PRAGMA optimize; .stats on CREATE TABLE t1(n int, log int); BEGIN; INSERT INTO t1 VALUES(((2 + 0) - 0), 1); INSERT INTO t1 VALUES(3, 1); INSERT INTO t1 VALUES(4, 1); INSERT INTO t1 VALUES(4, 3); SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) FROM t1; SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log; SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y); SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY log; SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2)+0, min(log,avg(n))+0; ``` ## Actual output ```sql 0 Memory Used: 78848 (max 80400) bytes Number of Outstanding Allocations: 162 (max 176) Number of Pcache Overflow Bytes: 16928 (max 16928) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 32 (max 88) Successful lookaside attempts: 180 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 13592 bytes Page cache hits: 7 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1008 bytes Statement Heap/Lookaside Usage: 2400 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 30 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2400 Bytes received by read(): 7232 Bytes sent to write(): 2 Read() system calls: 14 Write() system calls: 1 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 Memory Used: 76448 (max 80400) bytes Number of Outstanding Allocations: 150 (max 176) Number of Pcache Overflow Bytes: 16928 (max 16928) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 35 (max 88) Successful lookaside attempts: 183 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 13592 bytes Page cache hits: 0 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1008 bytes Statement Heap/Lookaside Usage: 2528 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 2 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2528 Bytes received by read(): 7326 Bytes sent to write(): 1295 Read() system calls: 16 Write() system calls: 2 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 Memory Used: 82152 (max 82152) bytes Number of Outstanding Allocations: 157 (max 176) Number of Pcache Overflow Bytes: 16928 (max 16928) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 38 (max 88) Successful lookaside attempts: 209 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 13592 bytes Page cache hits: 2 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1032 bytes Statement Heap/Lookaside Usage: 2912 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 14 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2912 Bytes received by read(): 7423 Bytes sent to write(): 2590 Read() system calls: 18 Write() system calls: 3 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 Memory Used: 82152 (max 82152) bytes Number of Outstanding Allocations: 157 (max 176) Number of Pcache Overflow Bytes: 16928 (max 16928) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 38 (max 88) Successful lookaside attempts: 226 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 13592 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1032 bytes Statement Heap/Lookaside Usage: 2912 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 10 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2912 Bytes received by read(): 7520 Bytes sent to write(): 3886 Read() system calls: 20 Write() system calls: 4 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 Memory Used: 82152 (max 82152) bytes Number of Outstanding Allocations: 157 (max 176) Number of Pcache Overflow Bytes: 16928 (max 16928) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 38 (max 88) Successful lookaside attempts: 243 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 13592 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1032 bytes Statement Heap/Lookaside Usage: 2912 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 10 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2912 Bytes received by read(): 7617 Bytes sent to write(): 5182 Read() system calls: 22 Write() system calls: 5 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 Memory Used: 82152 (max 82152) bytes Number of Outstanding Allocations: 157 (max 176) Number of Pcache Overflow Bytes: 16928 (max 16928) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 38 (max 88) Successful lookaside attempts: 260 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 13592 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1032 bytes Statement Heap/Lookaside Usage: 2912 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 10 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2912 Bytes received by read(): 7714 Bytes sent to write(): 6478 Read() system calls: 24 Write() system calls: 6 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 2|1|4|3|13|6|3.25|1.5 Memory Used: 84528 (max 84728) bytes Number of Outstanding Allocations: 158 (max 176) Number of Pcache Overflow Bytes: 16928 (max 16928) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 61 (max 96) Successful lookaside attempts: 353 Lookaside failures due to size: 1 Lookaside failures due to OOM: 0 Pager Heap Usage: 13592 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1032 bytes Statement Heap/Lookaside Usage: 8232 bytes Fullscan Steps: 3 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 101 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 8232 Bytes received by read(): 7811 Bytes sent to write(): 7774 Read() system calls: 26 Write() system calls: 7 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 1|4.0 3|5.0 Memory Used: 84528 (max 88840) bytes Number of Outstanding Allocations: 158 (max 176) Number of Pcache Overflow Bytes: 16928 (max 16928) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 58 (max 96) Successful lookaside attempts: 421 Lookaside failures due to size: 2 Lookaside failures due to OOM: 0 Pager Heap Usage: 13592 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1032 bytes Statement Heap/Lookaside Usage: 8792 bytes Fullscan Steps: 3 Sort Operations: 1 Autoindex Inserts: 0 Virtual Machine Steps: 105 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 8792 Bytes received by read(): 7908 Bytes sent to write(): 9093 Read() system calls: 28 Write() system calls: 8 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 7|1 3|3 Memory Used: 86104 (max 94672) bytes Number of Outstanding Allocations: 159 (max 176) Number of Pcache Overflow Bytes: 16928 (max 16928) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 58 (max 98) Successful lookaside attempts: 512 Lookaside failures due to size: 4 Lookaside failures due to OOM: 0 Pager Heap Usage: 13592 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1032 bytes Statement Heap/Lookaside Usage: 9168 bytes Fullscan Steps: 3 Sort Operations: 2 Autoindex Inserts: 0 Virtual Machine Steps: 135 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 9168 Bytes received by read(): 8005 Bytes sent to write(): 10402 Read() system calls: 30 Write() system calls: 9 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 Memory Used: 84528 (max 94672) bytes Number of Outstanding Allocations: 158 (max 176) Number of Pcache Overflow Bytes: 16928 (max 16928) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 56 (max 98) Successful lookaside attempts: 576 Lookaside failures due to size: 5 Lookaside failures due to OOM: 0 Pager Heap Usage: 13592 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1032 bytes Statement Heap/Lookaside Usage: 8536 bytes Fullscan Steps: 3 Sort Operations: 1 Autoindex Inserts: 0 Virtual Machine Steps: 93 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 8536 Bytes received by read(): 8103 Bytes sent to write(): 11708 Read() system calls: 32 Write() system calls: 10 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 1|3|3.0|6 3|1|4.0|10 Memory Used: 86440 (max 95120) bytes Number of Outstanding Allocations: 159 (max 176) Number of Pcache Overflow Bytes: 16928 (max 16928) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4360 bytes Lookaside Slots Used: 69 (max 115) Successful lookaside attempts: 688 Lookaside failures due to size: 7 Lookaside failures due to OOM: 0 Pager Heap Usage: 13592 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Schema Heap Usage: 1032 bytes Statement Heap/Lookaside Usage: 26992 bytes Fullscan Steps: 3 Sort Operations: 2 Autoindex Inserts: 0 Virtual Machine Steps: 168 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 26992 Bytes received by read(): 8202 Bytes sent to write(): 13006 Read() system calls: 34 Write() system calls: 11 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 ``` ## Expectation ```sql 0 Memory Used: 79400 (max 80936) bytes Number of Outstanding Allocations: 172 (max 186) Number of Pcache Overflow Bytes: 16936 (max 16936) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 48 (max 97) Successful lookaside attempts: 184 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 13608 bytes Page cache hits: 7 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 856 bytes Statement Heap/Lookaside Usage: 2296 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 30 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2296 Bytes received by read(): 7752 Bytes sent to write(): 2 Read() system calls: 14 Write() system calls: 1 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 Memory Used: 77104 (max 80936) bytes Number of Outstanding Allocations: 160 (max 186) Number of Pcache Overflow Bytes: 16936 (max 16936) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 51 (max 97) Successful lookaside attempts: 187 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 13608 bytes Page cache hits: 0 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 856 bytes Statement Heap/Lookaside Usage: 2528 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 3 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2528 Bytes received by read(): 7846 Bytes sent to write(): 1333 Read() system calls: 16 Write() system calls: 2 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 Memory Used: 82744 (max 82744) bytes Number of Outstanding Allocations: 167 (max 186) Number of Pcache Overflow Bytes: 16936 (max 16936) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 54 (max 97) Successful lookaside attempts: 213 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 13608 bytes Page cache hits: 2 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 864 bytes Statement Heap/Lookaside Usage: 2912 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 14 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2912 Bytes received by read(): 7943 Bytes sent to write(): 2666 Read() system calls: 18 Write() system calls: 3 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 Memory Used: 82744 (max 82744) bytes Number of Outstanding Allocations: 167 (max 186) Number of Pcache Overflow Bytes: 16936 (max 16936) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 54 (max 97) Successful lookaside attempts: 230 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 13608 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 864 bytes Statement Heap/Lookaside Usage: 2912 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 10 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2912 Bytes received by read(): 8040 Bytes sent to write(): 4000 Read() system calls: 20 Write() system calls: 4 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 Memory Used: 82744 (max 82744) bytes Number of Outstanding Allocations: 167 (max 186) Number of Pcache Overflow Bytes: 16936 (max 16936) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 54 (max 97) Successful lookaside attempts: 247 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 13608 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 864 bytes Statement Heap/Lookaside Usage: 2912 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 10 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2912 Bytes received by read(): 8137 Bytes sent to write(): 5334 Read() system calls: 22 Write() system calls: 5 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 Memory Used: 82744 (max 82744) bytes Number of Outstanding Allocations: 167 (max 186) Number of Pcache Overflow Bytes: 16936 (max 16936) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 54 (max 97) Successful lookaside attempts: 264 Lookaside failures due to size: 0 Lookaside failures due to OOM: 0 Pager Heap Usage: 13608 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 864 bytes Statement Heap/Lookaside Usage: 2912 bytes Fullscan Steps: 0 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 10 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 2912 Bytes received by read(): 8234 Bytes sent to write(): 6668 Read() system calls: 24 Write() system calls: 6 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 2|1|4|3|13|6|3.25|1.5 Memory Used: 85112 (max 85312) bytes Number of Outstanding Allocations: 168 (max 186) Number of Pcache Overflow Bytes: 16936 (max 16936) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 69 (max 103) Successful lookaside attempts: 348 Lookaside failures due to size: 1 Lookaside failures due to OOM: 0 Pager Heap Usage: 13608 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 864 bytes Statement Heap/Lookaside Usage: 8272 bytes Fullscan Steps: 3 Sort Operations: 0 Autoindex Inserts: 0 Virtual Machine Steps: 101 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 8272 Bytes received by read(): 8331 Bytes sent to write(): 8002 Read() system calls: 26 Write() system calls: 7 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 1|4.0 3|5.0 Memory Used: 85112 (max 89416) bytes Number of Outstanding Allocations: 168 (max 186) Number of Pcache Overflow Bytes: 16936 (max 16936) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 72 (max 103) Successful lookaside attempts: 415 Lookaside failures due to size: 2 Lookaside failures due to OOM: 0 Pager Heap Usage: 13608 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 864 bytes Statement Heap/Lookaside Usage: 9600 bytes Fullscan Steps: 3 Sort Operations: 1 Autoindex Inserts: 0 Virtual Machine Steps: 106 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 9600 Bytes received by read(): 8428 Bytes sent to write(): 9360 Read() system calls: 28 Write() system calls: 8 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 7|1 3|3 Memory Used: 86680 (max 95232) bytes Number of Outstanding Allocations: 169 (max 186) Number of Pcache Overflow Bytes: 16936 (max 16936) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 72 (max 119) Successful lookaside attempts: 508 Lookaside failures due to size: 4 Lookaside failures due to OOM: 0 Pager Heap Usage: 13608 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 864 bytes Statement Heap/Lookaside Usage: 24976 bytes Fullscan Steps: 3 Sort Operations: 2 Autoindex Inserts: 0 Virtual Machine Steps: 134 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 24976 Bytes received by read(): 8525 Bytes sent to write(): 10708 Read() system calls: 30 Write() system calls: 9 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 Memory Used: 85112 (max 95232) bytes Number of Outstanding Allocations: 168 (max 186) Number of Pcache Overflow Bytes: 16936 (max 16936) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 71 (max 119) Successful lookaside attempts: 572 Lookaside failures due to size: 5 Lookaside failures due to OOM: 0 Pager Heap Usage: 13608 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 864 bytes Statement Heap/Lookaside Usage: 8400 bytes Fullscan Steps: 3 Sort Operations: 1 Autoindex Inserts: 0 Virtual Machine Steps: 94 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 8400 Bytes received by read(): 8623 Bytes sent to write(): 12055 Read() system calls: 32 Write() system calls: 10 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 1|3|3.0|6 3|1|4.0|10 Memory Used: 87168 (max 95824) bytes Number of Outstanding Allocations: 175 (max 186) Number of Pcache Overflow Bytes: 16936 (max 16936) bytes Largest Allocation: 48000 bytes Largest Pcache Allocation: 4368 bytes Lookaside Slots Used: 77 (max 123) Successful lookaside attempts: 677 Lookaside failures due to size: 7 Lookaside failures due to OOM: 6 Pager Heap Usage: 13608 bytes Page cache hits: 1 Page cache misses: 0 Page cache writes: 0 Page cache spills: 0 Temporary data spilled to disk: 0 Schema Heap Usage: 864 bytes Statement Heap/Lookaside Usage: 28248 bytes Fullscan Steps: 3 Sort Operations: 2 Autoindex Inserts: 0 Virtual Machine Steps: 171 Reprepare operations: 0 Number of times run: 1 Memory used by prepared stmt: 28248 Bytes received by read(): 8722 Bytes sent to write(): 13392 Read() system calls: 34 Write() system calls: 11 Bytes read from storage: 0 Bytes written to storage: 0 Cancelled write bytes: 0 ``` ## Flag ``` ```