Index Overview - Deepstash
Index Overview

Index Overview

What is it?:

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

What is it good for?:

  • Makes the SELECT/retrieval queries fast

Does it have any disadvantage?:

  • It requires disk space
  • It requires RAM to take advantage of it entirely
  • Causes maintenance overhead for write operations with each INSERT/UPDATE/DELETE

Creating index syntax in POSTGRESQL:

CREATE INDEX e_idx ON users (email)

STASHED IN:

7

STASHED IN:

6 Comments

MORE IDEAS FROM SQL - Use The Index

Single vs Concatenated Indexes

Terminology:

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

How indexes work:

  • Index is used to find data fast, therefore it looks for the WHERE clause that defines the search condition

When to use Single-Column Indexes:

  • When most of the time that indexed column is used in the WHERE clause

Index on (x)

SELECT * FROM table WHERE x = 11

When to use Multi-Column Indexes:

  • When more columns are used in the WHERE clause

Index on (t, x):

SELECT * FROM table WHERE t = 9 and x = 11

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:

Index on (t, x):

  • SELECT * FROM table WHERE x = 11

Example 2:

If queries looks like this:

SELECT * FROM table WHERE x = 11

  and

SELECT * FROM table WHERE t = 9 and x = 11

The reversed index is much better - (x, t)

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) 

Query:

SELECT COUNT(*) 

FROM users  

WHERE UPPER(email) = 'email@mail.com'

This will read the full index end to end which is only slightly better than reading full table. 

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 _) at the end:

  • SELECT * FROM table WHERE x LIKE 'sq%'

3. Less effective, but still useful, using wild card character in the middle:

  • SELECT * FROM table WHERE x LIKE 's_l'

When it is useless:

When wild card character is used in the beginning:

  • SELECT * FROM table WHERE x LIKE '%ex'

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 columns in Multi-Column Indexes is important
  • 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
  • Use LIKE filters wisely as explained in the previous idea

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:

RELATED IDEA

What is visual search?

Visual search uses artificial intelligence technology to help people search through the use of real-world imagery, rather than through text search. 

So, when a person takes a photograph of an object, using Google Lens, for instance, the software identifies the object within the picture and provides information and search results to the user.

STASHED IN:

3

Persuasion through storytelling

Stories are a very integral part of being persuasive. 

Stories trump data when it comes to persuasion because stories are easier to understand and relate to.

3

STASHED IN:

328

STASHED IN:

0 Comments

1) A website isn’t static; it’s dynamic . It’s ever-changing. The moment you accomplish something, you can add it to your website. When you complete a project, you can put it in your portfolio for all to see. You don’t need to print new copies of it and send it out to your contacts over and over; you just update it. People can continually come back and see what you’re up to.

2) Having a website makes you more findable. If all you have is a resume, you have to go out and hand it to people to get your name out. If someone wanted to look you up on the internet and you didn’t have a website, all they might get is a Facebook or Twitter profile.

However, if you have a website, you can be found by a much wider audience and control what it is they see first. This is key for establishing your personal brand and for highlighting your accomplishments.

I’ve been offered jobs, met clients for my web design work, and gotten interviews simply because I have a website. If I didn’t take the time to create one, I’m confident that I wouldn’t have been found.

6

STASHED IN:

27