Db System Implementation

Summary

• Data First Implementation
• To index Or Not
  • Size of Table
  • Reads v. Writes
  • Advice

Data First Implementation

LAMP/WAMP/MAMP stack demos

I don’t really understand the point of this section

To index Or Not

Many things to consider:

• Size of the table
• Access Paths
  • A single table can be in the access paths of multiple queries/updates
• Frequency of reads and/or writes
• Already indexed?
  • Some DBMSs, like MySQL, don’t give you a choice

Size of Table

Consider the following table:

RegularUser(
  Email varchar(50),
  Sex char(1),
  Birthdate datetime,
  CurrentCity varchar(50),
  Hometown varchar(50),
);

And assume our DBMS uses 4k blocks and tries to fill them to 80%.

That would give us the following stats:

• Record size: 159 bytes
• Records per block: ~20
• Number of records: 4 million
• Number of blocks: ~200,000
  • Could cost 200,000 x 0.01sec = 33min to scan if not clustered and/or indexed
• Table size: ~800MB

Reads v. Writes

In general, indexing makes reads faster and writes slower.

Updates (and some other writes) require first reading, then writing. Inserting generally only needs to write.

Advice

Leo’s Advice:

• Pay attention to size
• Multiple queries add up
• Writes suffer when indices must be maintained
• Some DBMS come with tools to measure performance
• Some DBMS show you the query graphs
• Think globally, act locally
• Compare before and after performance
• Learn about efficiency, physical organization, query optimization