SQL - Query performance tuning

SQL - Query performance tuning


Cheesecake Labs

September 11, 2019


  1. SQL - QUERY PERFORMANCE TUNING Understanding how index works


  3. • Database indexing is a developer task • An index

    makes the query fast • It requires its own disk space and holds a copy of the indexed table data • Creating an index does not change the table data Index
  4. • The database combines two data structures to meet the

    challenge: a doubly linked list and a search tree • Each node refers to the preceding and the following node. The database can read the index in any direction. • Insert new entries without moving large amounts of data
  5. • The index leaf nodes are stored in an arbitrary

    order. A database needs a second structure to find the entry: a balanced search tree - the B-tree. • The doubly linked list establishes the logical order between the leaf nodes. The root and branch nodes support quick searching among the leaf nodes.
  6. • The structure is a balanced search tree because the

    tree depth is equal at every position; • The database maintains the index automatically. It applies every insert, delete and update to the index and keeps the tree in balance • The tree traversal is a very efficient operation. It works almost instantly - even on a huge data set. That is primarily because of the tree balance, which allows accessing all elements with the same number of steps, and secondly because of the logarithmic growth of the tree depth.
  7. • The first problem is the leaf node chain. The

    database must read the next leaf node to see if there are any more matching entries • The second problem is accessing the table. Even a single leaf node might contain many hits—often hundreds Slow indexes
  8. Slow indexes An index lookup requires three steps: 1. The

    tree traversal; 2. Following the leaf node chain; 3. Fetching the table data.

  10. Example

  11. What happens? Modifying

  12. Concatenated indexes In general, a database can use a concatenated

    index when searching with the leading (leftmost) columns
  13. Slow index

  14. • The first step is the tree traversal on the

    EMPLOYEES_PK index. That index does not cover the LAST_NAME column • The next step is the fetch the table data operation • Once the LAST_NAME column is available, the database can evaluate the remaining part of the where clause • The statement's response time does not depend on the result set size but on the number of employees in the particular subsidiary.

  16. Case sensitive Solution:

  17. Over-indexing • A single index cannot support both methods of

    ignoring the case. • To make one index suffice, you should consistently use the same function throughout your application

  19. THANK YOU!

  20. References https://use-the-index-luke.com/ https://www.amazon.co.uk/SQL-Performance-Explained-Markus-Winand/d p/3950307826/?_encoding=UTF8&m=AJFZUYB7XQGQN&tag=winandat-2 1&linkCode=ur2&camp=1634&creative=6738