This is intended to be the beginning of an ongoing discussion of MySQL's Optimizer and how best to configure indexes. I am fairly new to analyzing MySQL's optimizer, but spent a number of years running a Data Warehouse on the old Sybase System 9/10/11, (back in the day, when their's completely crashed for OLAP usage ... I could tell you many "war-stories" and one of the big reasons Sybsae lost their dominance, lol) and subsequently, on MS SQL, with some isolated experience on Oracle and Terradata, so my understanding of Performance and Optimization principles in RDBMS's is strong, but I need to determine exactly what is being done differently on MySQL then previously. I have been reading from a number of difference sources, but any good additional links would be appreciated:
-- MySQL Performance Blog
-- 10 tips for optimizing MySQL queries that don't suck
-- Optimizing MySQL Queries and Indexes
I am currently developing a large website (Innodb) and will be developing comprehensive benchmarks to test the different approaches.
As the topic description mentions, my initial question has to do with the choice of using a *Composite Key* vs. a series of separate keys utilizing the *Index Merging* feature.
Historically, I would go with a *Composite Key*, and of course, if one knows that one is always traversing the data in a certain direction, one should always use the [Composite Key], but I would like to explore the performance differences between these two, and in the coming weeks, I will be testing both to see the difference. (As a side note, when I was first coding SQL, Sybase didn't even support the *Index Merging* feature, and I believe they only introduced it in System 4.10 or 4.11.)