What is it?:<ul><li>A stand-alone database structure</li><li>Redundant information, well ordered, that acts as a map/reference to the table</li></ul>What is it good for?:<ul><li>Makes the SELECT/retrieval queries fast</li></ul>Does it have any disadvantage?:<ul><li>It requires disk space</li><li>It requires RAM to take advantage of it entirely</li><li>Causes maintenance overhead for write operations with each INSERT/UPDATE/DELETE</li></ul>Creating index syntax in POSTGRESQL:<code>CREATE INDEX e_idx ON users (email)</code>

Index Overview

Terminology:<ul><li>Single Index = Single-Column Index</li><li>Concatenated Index = Multi-Column Index</li></ul>How indexes work:<ul><li>Index is used to find data fast, therefore it looks for the WHERE clause that defines the search condition</li></ul>When to use Single-Column Indexes:<ul><li>When most of the time that indexed column is used in the WHERE clause</li></ul>Index on (x):&nbsp;SELECT * FROM table WHERE x = 11 When to use Multi-Column Indexes:<ul><li>When more columns are used in the WHERE clause</li></ul>Index on (t, x):SELECT * FROM table WHERE t = 9 and x = 11

Single vs Concatenated Indexes

Order of the columns matter.&nbsp;The index is almost useless if the first column doesn't appear in the WHERE clause. Example 1:Index on (t, x):<ul><li>SELECT * FROM table WHERE x = 11</li></ul>Example 2:If queries looks like this:SELECT * FROM table WHERE x = 11&nbsp;&nbsp;andSELECT * FROM table WHERE t = 9 and x = 11 The reversed index is much better - (x, t)

Order in Multi-Column Index Matters

Wrapping the indexed column in a function nullifies the index almost completely.&nbsp; Index:CREATE INDEX e_idx ON users (email)&nbsp; <code>Query:</code>SELECT COUNT(*)&nbsp;FROM users&nbsp;&nbsp;WHERE UPPER(email) = 'email@mail.com' This will read the full index end to end which is only slightly better than reading full table.&nbsp;

Functions on Indexes

When it can be used:1. Exact matches:<ul><li>SELECT * FROM table WHERE x = 'sql'</li></ul>2. Using wild card character (% or _) at the end:<ul><li>SELECT * FROM table WHERE x LIKE 'sq%'</li></ul>3. Less effective, but still useful, using wild card character in the middle:<ul><li>SELECT * FROM table WHERE x LIKE 's_l'</li></ul>When it is useless:When wild card character is used in the beginning:<ul><li>SELECT * FROM table WHERE x LIKE '%ex'</li></ul>

Index on Text Columns

<ul><li>Avoid using functions in WHERE clause on indexes when it is not needed</li><li>Use LIMIT when partial results are needed</li><li>Order of columns in Multi-Column Indexes is important</li><li>Extra conditions in WHERE clause can ruin the use of index-only scan, which doesn't access the actual table at all. Even if the filter limits the results more, it requires more processing</li><li>Use LIKE filters wisely as explained in the previous idea</li></ul>

What to keep in mind?

SQL indexing and tuning tutorial for developers. No unnecessary database details—just what developers need to know. Covers all major SQL databases.

Adrian-Danut Nicolae
@adn

Index Overview

SQL - Use The Index

Adrian-Danut Nicolae@adn

Index Overview

SQL - Use The Index

Adrian-Danut Nicolae
@adn