Query Performance Optimization
Jul 23, 2010 Database
1.Slow Query Basics:Optimize Data Access
- Are you asking the database for data you do not need?
- Is MySQL examing too much data?(index solution)
2.Ways to restructure queries
- Complex queries Versus Many queries(few queries)
- Chopping up a query
- Join Decomposition(
- When application joins may be more efficient
- you cache and reuser a lot of data from earlier queries
- you use multiple MyISAM tables
- you distribute data across multiple severs
- you replace joins with in() lists on larger tables
- a join refers to the same table multiple times
- )
Notes on Storage Engine
Jul 23, 2010 Database
1.The MyISAM Storage Engine
- Table locks
- No automated data recovery
- No transactions
- Only indexes are cached in memory
- Compact storage
2.The memory storage Engine
- Table locks
- No dynamic rows(no BLOB and TEXT , even VARCHAR)
- Hash indexes are the default index type
- No index statistics
- Content is lost on restart
3.The InnoDB Storage Engine
- Transactional
- Foreign keys
- Row-level locks
- Multiversioning
- Clustering by primary key
- All indexes contain the primary key columns
- Optimized caching
- Unpached indexes
- Slow data load
- Blocking AUTO_INCREMENT
- No cached COUNT(*) value