SQL - Use The Index - Deepstash
Index Overview

What is it?:

  • A stand-alone database structure
  • Redundant information, well ordered, that acts as a map/reference to the table

Wha...

STASHED IN:

7

SQL - Use The Index

use-the-index-luke.com

STASHED IN:

6 Comments

Single vs Concatenated Indexes

Terminology:

  • Single Index = Single-Column Index
  • Concatenated Index = Multi-Column Index

How indexes work:

    STASHED IN:

    7

    Order in Multi-Column Index Matters

    Order of the columns matter

    The index is almost useless if the first column doesn't appear in the WHERE clause.

    Example 1:

    ...

    STASHED IN:

    7

    Functions on Indexes

    Wrapping the indexed column in a function nullifies the index almost completely. 

    Index:

    CREATE INDEX e_idx ON users (email)&nbs...

    1

    STASHED IN:

    7

    Index on Text Columns

    When it can be used:

    1. Exact matches:

    • SELECT * FROM table WHERE x = 'sql'

    2. Using wild card character (% or _

    STASHED IN:

    7

    What to keep in mind?
    • Avoid using functions in WHERE clause on indexes when it is not needed
    • Use LIMIT when partial results are needed
    • Order of co...

    STASHED IN:

    7

    Deepstash helps you become inspired, wiser and productive, through bite-sized ideas from the best articles, books and videos out there.

    GET THE APP: