1
Copyright MySQL AB
The World’s Most Popular Open Source Database
Coding and Indexing Strategies for Optimal Performance
Jay Pipes
Community Relations Manager, North America
(jay@mysql.com)
MySQL AB
2
Copyright MySQL AB
The World’s Most Popular Open Source Database
Agenda
•
Index Concepts
• What Makes a Good Index?
•
Identifying Good Candidates for Indexing
• Schema Design Considerations
•
Index Tips
• Multiple Key Caches
• SQL Coding Techniques and Tips
• Questions
3
Copyright MySQL AB
The World’s Most Popular Open Source Database
Index Concepts
• Advantages
– SPEED of SELECTs
– Reporting, OLAP, readintensive applications
• But...
– Slows down writes (more work to do...)
– heavy write applications (OLTP) be careful
– More disk space used
4
Copyright MySQL AB
The World’s Most Popular Open Source Database
Index Concepts (cont'd)
– The sort order
• Enables efficient searching algorithms
– Data Records vs. Index Records
• Index records “slimmer” than data records
– The layout
• Hash vs. BTree vs. Other
• Some layouts work well for different types of data access
– The structure
• Clustered vs. Nonclustered Data/Index Organization
5
Copyright MySQL AB
The World’s Most Popular Open Source Database
The Scan vs. Seek Choice
– Disk storage dictates choice
• “Random” access more expensive than sequential access
• Requires more work than loading a “chunk” of contiguous space and
reading through the chunk sequentially
– Threshold
• Optimizer chooses to scan vs. seek based on:
– The relative cost of doing a scan vs. a seek (internal)
– The number of records it estimates will be found by the query.
» ~30% of total count of records, optimizer will usually choose
to scan vs seek.
– ANALYZE TABLE
6
Copyright MySQL AB
The World’s Most Popular Open Source Database
Index Layouts
– Hash Layout
• Hashing algorithm produces an integer representing the index
record value
• MEMORY and InnoDB (Adaptive). Not MyISAM
• Very fast for key lookups. Bad for range lookups
7
Copyright MySQL AB
The World’s Most Popular Open Source Data