1 Good Evening

2 Lecture begins at 9 05

3
Topic Indexes

Backup
Agenda
1 Indexes

Builtin Frs

Deadlocks
 Intro Indexes
of
Consider the table

Students

s EFFI 5E395

muggy
5 3
2

CPU 3 542 x 109 ops second

Registers Caches RAM s hand bis

T T T T
 will dat
1 90 operation being from
hand dish to RAM segment by segment
2 Once the data is in Ram it is

analysed by CPO rows are selected

to prepare result
ejected
student

AF id rogue
bid Pst

t Titi

bid 2

Fs
1000 2

IO Rows useful wasted

59 100 I 915
Oney Select a

Frost

5
Whare yd

Fact Date table is sorted

of a

Pk on the hard disk

by

id name bid psp j

A 2

II B M
Js
C
pi g Isar
I I
sp

Rows Useful wasted

IO
6 I 5
3
SUMMARY When clause on Pk will

always wah date

foster
is sorted Pk Ph is
by
unique also We will
stop reading
future segments were we will

find disind row

lookup table Index

Approachf
Kbl
For
every Mysal creaty
lookup tablas index
a
stand

disk
in R.AT aha.d
9
students
by I

T F
t
B t.IO

E
 i
It
DSA B Trees

FROM St dents

s Check lookup segment

for
2 Red segment

3 Process

IO Rows useful
wasted
t T t t

Question how fast will the seed

be in
lookup t.be
used to implement Index BTse
DSA

Case case 2

bid id id Ida d
 I
d
Select

t
SR bid 2

353
a indies
74
ft
for other columns su
as great
required y
d
I S1 S2 s

I 2 s
4 B tree

3 52,54

Io Rows Useful wasted

2 4 2 2

Pros Cons
Guidelines
Composite Indexes
 1018to10i25
1BREAK

noemoEf5gE.Jr
T DSA
for index
MET
3 Pros Cons

iii

DSA For India

Fit
 1h DSA

Cash nip unordered mad Hashtable

tramp ordered MD BST

Hash table
Aug constant
Balad BST 0 login

a
Black
red

50

251
I
75
si
iz 371 o

3
Faster

logan se ly

10 operations I comparisons
B Tres

Attn

ÉEE
Twin log
 Algorithm log n

degree

B Trees be as
high
dysu can

as woo

log n

2000

8xtotrows leg
2000
n

8.109
ly a
zoon
i

said rows 3 comparisons

 Indi es usu BTrees
1
simile to Bts
2 BThees a
s.npygn.gg

eigg

Ifowtoimpemantastbtrusff
ay

PROS CONS of Indexes

Ros COI
41 Extra span
optimised reads per lookup
of
I
jÉj
j
Insert Delete
segments we

the number of dig

Y2now
update might become
slower
 students

i
1 A
2 B
Js
3 C so
75
a D g Isa
5 E 3 90

n
I 75 753
sa id Cha
NFda bid SdaJob

FFittFitt
I 51 2 51,52
s
3 59 C 52 3 53
a 512 D 52
SB
6 SB E f
 GUIDELINES
1 Don t crate index while creating
Pli index
tables let th
get created
2 Analyse the
traffic on db

SOL Profiles
t

it
T
ÉF

3 Create india columns which

for
are
frequently used by when class

data logged Sol

as per by Profile
 Isi
Megha I I 352
Ii

yes
is

case I No index Sudo

Io Rows Useful wasted
Y 8 I 7

Meymh s
wasted
Io Roos Usfl it
Nighe
 Edling

Case 3 Judea stela name

on
of

tht

mania
wasted
IO Rows Useful Na 52,53
S2
4 I 3 me
2 Sm 1 S3
su
Mu

Cash India on 1st 3 chars

 Io row
W
2 1

Y
I I É

t
ion 2 char

y
71
FAT

a 263

É
Y L
 Backlog

2 Builtin Fns

4 Composite Indexes

it A

Prot

Beginning msso
se.gg
Robert view
us solsentfur
Advance Programmer
 IFF

25
114M
75
It I to

6 7 8 9 to
1 2 3 4 5

if