: Oracle, MySQL, PostgreSQL, etc. ◦ Multiple languages : PHP, Java, etc. • Written by Markus Winand • Available in EN, JP, GE, FR, SP ◦ Japanese ver. translator = me • Buy at http://sql-performance-explained.com/ ◦ JP : http://sql-performance-explained.jp/ • Free Web Edition: http://use-the-index-luke.com/
write SQL queries (w/ Ruby on Rails) ◦ Buy and read “SQL Performance Explanined” ;-) ◦ Read AR manual • How to tune SQL queries ◦ Depends on a DBMS you’re using
a book • To get data quickly and efficiently • Redundant, just pointing a piece of data stored somewhere • Sorted by a specific order Different from an index of a book • Updated frequently
popular index structure for relational databases • Balanced tree, not binary tree Doubly linked list • Can change an order by changing pointers • Can connects leaf nodes
P Q R S T E F G H A B C D U V W X Y Z C F U X L R O I Z Ice cream Hand Nose Girl X’mas Dog Man Rain Fish Cat Orange Pen Egg Boy Van Water Sugar Apple Queen Tree Yellow Kite Lamp Jet Zoo Umbrella 18 14 10 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 6 13 9 4 2 1 24 22 23 7 3 11 12 19 8 17 20 26 15 16 5 21 25 (B Tree) Index Data … … … … … … … … … … … … … … … … … … … … … … … … … … …
P Q R S T E F G H A B C D U V W X Y Z C F U X L R O I Z 18 14 10 6 13 9 4 2 1 24 22 23 7 3 11 12 19 8 17 20 26 15 16 5 21 25 Root node Branch nodes Leaf nodes Ice cream Hand Nose Girl X’mas Dog Man Rain Fish Cat Orange Pen Egg Boy Van Water Sugar Apple Queen Tree Yellow Kite Lamp Jet Zoo Umbrella 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 … … … … … … … … … … … … … … … … … … … … … … … … … … …
Cat Orange Pen Egg Boy Van Water Sugar Apple Queen Tree Yellow Kite Lamp Jet Zoo Umbrella 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 … … … … … … … … … … … … … … … … … … … … … … … … … … … I R Z I J K L M N O P Q R S T E F G H A B C D U V W X Y Z C F U X L R O I Z 18 14 10 6 13 9 4 2 1 24 22 23 7 3 11 12 19 8 17 20 26 15 16 5 21 25 (B Tree) Index Data
a number of disk accesses • Reduces the amount of disk accesses • Finds records in a same time when data is getting bigger • Is smaller than data itself = can fit in memory
When writing a query ◦ Think about how indexes work ◦ Think about how the query works when data is getting bigger • When creating/updating/deleting an index ◦ Think about the balance ▪ How much the index makes queries fast ▪ How much the cost of modifying the index