Cookbook for Creating INDEXes -- All about Indexing
Indexing is a black art? Not really. But there are a lot of subtle issues and interactions to learn.
This tutorial presents virtually everything there is to know about indexes. It takes the "cookbook" approach of saying "this SELECT begs for this INDEX", then deep dives into "why".
This lecture moderately fast-paced. The novice will learn the basics that demystify INDEX creation. The advanced user will learn subtleties.
This covers MyISAM and InnoDB. So if you are thinking about moving from MyISAM to InnoDB/XtraDB, do so. This lecture will point out the many things act differently, and can affect performance.
Scaling? A tiny table can get away without any indexes. But if the table will get big, you need to understand when the indexes will help and when they won't.
PARTITIONing? INDEXes must be rethought when adding PARTITIONing. For that matter, will PARTITIONing help at all?