SQL Performance explained

["MySQL Distinguishing Access and Filter-Predicates In the following example, the entire where clause is used as access predicate: CREATE TABLE demo ( id1 NUMERIC , id2 NUMERIC , id3 NUMERIC , val NUMERIC); INSERT INTO demo VALUES (1,1,1,1); INSERT INTO demo VALUES (2,2,2,2); CREATE INDEX demo_idx ON demo (id1, id2, id3); EXPLAIN SELECT * FROM demo WHERE id1=1 AND id2=1; +------+----------+---------+------+-------+ | type | key | key_len | rows | Extra | +------+----------+---------+------+-------+ | ref | demo_idx | 12 | 1| | +------+----------+---------+------+-------+ There is no \u201cUsing where\u201d or \u201cUsing index condition\u201d shown in the \u201cExtra\u201d column. The index is, however, used (type=ref, key=demo_idx) so you can assume that the entire where clause qualifies as access predicate. You can use the key_len value to verify this. It shows that the query uses the first 12 bytes of the index definition. To map this to column names, you \u201cjust\u201d need to know how much storage space each column needs (see \u201cData Type Storage Requirements\u201d in the MySQL documentation). In absence of a NOT NULL constraint, MySQL needs an extra byte for each column. After all, each NUMERIC column needs 6 bytes in the example. Therefore, the key length of 12 confirms that the first two index columns are used as access predicates. Ex Libris GHEORGHE GABRIEL SICHIM <[email protected]> 191","Appendix A: Execution Plans When filtering with the ID3 column (instead of the ID2) MySQL 5.6 and later use an index filter predicate (\u201cUsing index condition\u201d): EXPLAIN SELECT * FROM demo WHERE id1=1 AND id3=1; +------+----------+---------+------+-----------------------+ | type | key | key_len | rows | Extra | +------+----------+---------+------+-----------------------+ | ref | demo_idx | 6 | 1 | Using index condition | +------+----------+---------+------+-----------------------+ In this case, the key length of six means only one column is used as access predicate. Previous versions of MySQL used a table level filter predicate for this query\u2014 identified by \u201cUsing where\u201d in the \u201cExtra\u201d column: +------+----------+---------+------+-------------+ | type | key | key_len | rows | Extra | +------+----------+---------+------+-------------+ | ref | demo_idx | 6 | 1 | Using where | +------+----------+---------+------+-------------+ 192 Ex Libris GHEORGHE GABRIEL SICHIM <[email protected]>","Index count(*) often as index-only scan, 120 Symbols Oracle requires NOT NULL constraint, 57 2PC, 89 COUNT STOPKEY, 145 ?, :var, @var (see bind parameter) cursor sharing (Oracle), 39 A D Access Predicate, 44 data transport object (DTO), 105 access predicates DATE recognizing in execution plans efficiently working with, 62 Oracle, 170 DBMS_XPLAN, 167 PostgreSQL, 177 DEALLOCATE, 174 SQL Server, 185 DEFERRABLE constraint, 11 DETERMINISTIC (Oracle), 30 adaptive cursor sharing (Oracle), 75 distinct, 97 auto parameterization (SQL Server), 39 distinct() B in JPA and Hibernate, 97 DML, 159 B-tree (balanced search tree), 4 doubly linked list, 2 between, 44 dynamic-update (Hibernate), 164 bind parameter, 32 E contraindications histograms, 34 eager fetching, 96 LIKE filters, 47 eventual consistency, 89 partitions, 35 execution plan, 10, 165 for execution plan caching, 32 cache, 32, 75 type safety, 66 creating bind peeking (Oracle), 75 bitmap index, 50 MySQL, 188 Bitmap Index Scan (PostgreSQL), 175 Oracle, 166 Brewer\u2019s CAP theorem, 89 PostgreSQL, 172 SQL Server, 180 C operations MySQL, 188 CAP theorem, 89 Oracle, 167 cardinality estimate, 27 PostgreSQL, 174 CBO (see optimizer, cost based) SQL Server, 182 clustered index, 122 explain MySQL, 188 transform to SQL Server heap table, Oracle, 166 127 PostgreSQL, 172 clustering factor, 21, 114 automatically optimized, 133 F clustering key, 123 collation, 24 FBI (see index, function-based) commit FETCH ALL PROPERTIES (HQL), 105 deferrable constraints, 11 fetch first, 144 implicit for truncate table, 163 filter predicates two phase, 89 compiling, 18 effects (chart), 81 computed columns (SQL Server), 27 recognizing in execution plans constraint deferrable, 11 Oracle, 170 NOT NULL, 56 PostgreSQL, 177 cost value, 18 SQL Server, 185 Ex Libris GHEORGHE GABRIEL SICHIM <[email protected]> 193","full table scan, 13 INDEX FULL SCAN, 168 All (MySQL), 189 Index Only Scan (PostgreSQL), 174 Seq Scan (PostgreSQL), 174 INDEX RANGE SCAN, 167 TABLE ACCESS FULL (Oracle), 168 Index Scan (PostgreSQL), 174 Table Scan (SQL Server), 183 Index Seek, 182 INDEX UNIQUE SCAN, 167 functions, 24 deterministic, 29 when accessing an IOT, 124 in partial indexes, 52 INTERNAL_FUNCTION, 67 window, 156 IOT (index-organized table), 122 G J group by, 139 join, 91 with PostrgesSQL and the Oracle full outer, 109 database and an ASC\/DESC index not pipelined, 140 K H Key Lookup (Clustered), 182 hash join, 101 L HASH GROUP BY, 169 HASH JOIN (Oracle), 169 lazy fetching HASH Join (PostgreSQL), 175 for scalar attributes (columns), 104 Hash Match, 183 Hash Match (Aggregate), 184 leaf node, 2 heap table, 3, 122 split, 160 creating in SQL Server, 127 LIKE, 45 Hibernate alternatives, 48 as index filter predicate, 112 eager fetching, 96 on DATE column, 67 ILIKE uses LOWER, 98 on DATE columns, 67 updates all columns, 164 hint, 19 limit (MySQL, PostgreSQL), 144 logarithmic scalability, 7 I LOWER, 24 IMMUTABLE (PostgreSQL), 30 M index Merge Join, 109 covering, 117 PostgreSQL, 175 fulltext, 48 SQL Server, 183 function-based, 24 MERGE JOIN (Oracle), 169 case insensitive, 24 multi-block read to index mathematical calculations, 77 for a full table scan, 13 join, 50 for a INDEX FAST FULL SCAN, 168 limits MVCC, 163 MySQL, Oracle, PostgreSQL, 121 affects PostgreSQL index-only scan, 174 SQL Server, 122 myths merge, 49 dynamic SQL is slow, 72, 74 multi-column, 12 most selective column first wrong order (effects), 81 partial, 51 disproof, 43 prefix (MySQL), 121 origin, 49 secondary, 123 Oracle cannot index NULL, 56 index in MySQL execution plans, 189 index-only scan, 116 N index-organized table, 122 database support, 127 N+1 problem, 92 Index Cond (PostgreSQL), 177 Nested Loops, 92 INDEX FAST FULL SCAN, 168 PostgreSQL, 175 SQL Server, 183 NESTED LOOPS (Oracle), 168 194 Ex Libris GHEORGHE GABRIEL SICHIM <[email protected]>","NOSORT row sequencing, 113 SORT GROUP BY, 140 row values, 153 WINDOW, 157 ROWID, 3 ROWNUM (Oracle pseudo column), 144, 148 NULL ROW_NUMBER, 156 indexing in Oracle, 54 S O scalability, 81 offset (MySQL, PostgreSQL), 148 horizontal, 87 optimizer, 18 logarithmic, 7 cost based, 18 Scalability, 79 hint, 19 Seek Predicates (SQL Server), 185 rule based, 18 select *, avoid to statistics, 21 OPTIMIZE FOR (SQL Server), 76 enable index-only scans, 120 OPTION (SQL Server), 76 improve hash join performance, 104 OR Seq Scan, 174 to disable filters, 72 Sort (SQL Server), 184 order by, 130 SORT GROUP BY, 169 ASC, DESC, 134 NOSORT, 140 NULLS FIRST\/LAST, 137 SORT ORDER BY, 130 support matrix, 138 STOPKEY, 145 OVER(), 156 SQL area, 75 SQL injection, 32 P SSD (Solid State Disk), 90 statistics, 21 paging, 147 for Oracle function-based indexes, 28 offset method, 148 STATISTICS PROFILE, 181 seek method, 149 STOPKEY approximated, 152 COUNT, 145 SORT ORDER BY, 146 parameter sniffing (SQL Server), 76 WINDOW, 157 parsing, 18 Stream Aggregate, 184 partial index, 51 partial objects (ORM), 104 T partitions and bind parameters, 35 pipelining, 92 top (SQL Server), 145 PLAN_TABLE, 166 Top-N Query, 143 predicate information, 20 TO_CHAR(DATE), 66 TRUNC(DATE), 62 access vs. filter predicates, 44 truncate table, 163 in execution plans triggers not executed, 163 MySQL, 190 Oracle, 170 U SQL Server, 185 prepare (PostgreSQL), 172 UPPER, 24 primary key w\/o unique index, 11 V Q Vaccum (PostgreSQL), 163 query planner (see optimizer) virtual columns for NOT NULL constraints on FBI, 58 R W RBO (see optimizer, rule based) RECOMPILE (SQL Server hint), 76 where, 9 result set transformer, 98 conditional, 72 RID, 3 in SQL Server execution plan, 187 RID Lookup (Heap), 183 root node, 5 window functions, 156 split, 160 Ex Libris GHEORGHE GABRIEL SICHIM <[email protected]> 195","SQL Performance Explained! \u2014 Now what? Maybe you still have some questions or a very specific problem that \u201cSQL Performance Explained\u201d did not answer satisfactory? Instant Coaching is the solution for you. Instant Coaching Instant Coaching is the fast, easy and hassle-free way for developers to resolve difficult SQL database performance issues via desktop sharing. Instant Coaching doesn\u2019t use made up examples; real cases are presented as this is the optimal way to help you in solving your current problems with slow SQL databases. Instant Coaching is vendor independent and efficient\u2026 and offered at a very affordable price! Give it a try! Now is the time to learn more about Instant Coaching! Visit our web site for more information: http:\/\/winand.at 196 Ex Libris GHEORGHE GABRIEL SICHIM <[email protected]>","SQL Performance explained SQL Performance explained helps developers to improve database perfor- mance. The focus is on SQL\u2014it covers all major SQL databases without getting lost in the details of any one speciic product. Starting with the basics of indexing and the where clause, SQL\u00a0Performance explained guides developers through all parts of an SQL statement and explains the pitfalls of object-relational mapping (orm) tools like Hibernate. Topics covered include: \u00bb Using multi-column indexes \u00bb correctly applying SQL functions \u00bb eicient use of LIKE queries \u00bb optimizing join operations \u00bb clustering data to improve performance \u00bb Pipelined execution of order by and group by \u00bb Getting the best performance for pagination queries \u00bb Understanding the scalability of databases Its systematic structure makes SQL Performance explained both a textbook and a reference manual that should be on every developer\u2019s bookshelf. covers SQL Server\u00ae mySQL PostgreSQL oracle\u00ae Database about markus Winand markus Winand has been developing SQL applications since 1998. His main interests include performance, scalability, reliability, and generally all other technical aspects of software quality. markus currently works as an independent trainer and coach in Vienna, austria. http:\/\/winand.at\/ ISbN 978-3-9503078-2-5 eUr 29.95 GbP 26.99 9 783950 307825"]