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. 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?
Rick James worked for Yahoo for 14 years. A main task was rooting out slowness on hundreds of different MySQL systems and proposing ways to speed them up -- tuning, schema changes, adding indexes, reformulating queries, even rearchitecting. Rick has addressed over 15K questions on various forums. Rick now does MySQL consulting, mostly in scaling and performance.