Indexing Strategies

MySQL & MariaDB Other MySQL® Questions
1


INTRO:
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.

INITIAL QUESTION:
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.)

2

If you can use one, a composite key is always much faster. I have never seen any query using index merge features that performed well, so I also hope to learn from this thread.

3

Yes, me either, but I am inheriting a DB and there are numerous instances of this, so I am wondering if there is something I don’t know about MySQL’s optimizer with reference to this, or if nothing else, what are the performance differences. As a related note, I am aware of the need and benefit to support INDEX MERGING when one accesses DATA-COLUMNS in multiple directions, but even then, having 2 (or maybe more) COMPOSITE INDEXES (historically) is still probably better, although, of course, this must be weighed against overhead associated with maintaining each index.

(NOTE: In my app, the data is not actually changing very often, and when it is, it is mostly internal admin staff, so I am less concerned with UPDATE, mainly data-retrieval performance.

It might not be a few weeks until I get to this stage, but I wanted to start this thread now and open the discussion.)

Thanks for the reply.