Q:

MySQL InnoDb: How solve speed issue with different order by columns needed

I have a fairly simple SQL statement like so:
SELECT * FROM table WHERE col1 = 'x' 
    AND col2 = 'y' AND col3 = 'z' 
    ORDER BY col4 LIMIT 0,40

If I have an index for col1_col2_col3 the query executes at 4.2 seconds (horrible).
If I have an index for col1_col2_col3_col4 the execution time is 0.003 seconds (great!).
So, bringing in the ORDER BY column into the index makes one hell of a difference.
Now for the question
I have the need to be able to sort it on col4 through col 14 (not in ONE query, in 10 different scenarios, depending on what the user picks for sort order) i.e. 10 different ORDER BY's.
Is it really meant to be that I have to create 10 indexes for this? Is that even adviceable? Isn't inserts slowed down by a magnitude from having that many indexes?
Any thoughts? Thanks.

A:

Right, you would need 10 different indexes.
Think of it this way: you're searching for row matching three specific values in column1, 2, and 3. So the set of matching rows is basically tied. The sort order you want can therefore be the tie-breaker column, which must be the fourth column of the index.
You can't use one index to order by more than one column in a given position of the index.
You're right that it costs some overhead to maintain indexes in a database. The more indexes, the more overhead. But there are a few things to keep in mind:

Typically, indexes don't cause "orders of magnitude" of overhead. Remember than one order of magnitude means 10x. People exaggerate the cost of indexes, and then spread fears that indexes should be avoided.
In a book The Art of SQL, the author tested performance overhead as he added indexes. He found that the overhead doesn't increase linearly with the number of indexes. The overhead diminishes, in a roughly logarithmic curve. In other words, it's true that more indexes has greater overhead, but 2x the number of indexes does not cost 2x as much. This kind of makes sense, because updating an index is an operation of logarithmic complexity, not linear complexity.

The overhead occurs on INSERT/UPDATE/DELETE. There's a slight overhead on SELECT too, because the optimizer may have to analyze more combinations of indexes. But that isn't usually a big bottleneck. Generally the overhead for writes is more significant than the overhead for reads.

InnoDB furthermore has an optimization to speed up write to secondary indexes, called change buffering. This happens automatically, and it benefits writes by queuing up changes and then merging them into the actual index gradually.

The amount of overhead therefore varies depending on what your ratio of reads vs. writes is. No one can tell you exactly how much the indexes cost in overhead because the answer will be different for every application.

Indexes help UPDATE and DELETE too. Any SQL statement with a WHERE clause or and ORDER BY clause will use an index to avoid doing table-scans and avoid doing expensive sorting. Some people claim "indexes help reads but penalize writes" but this is an oversimplification.

You just observed that a SELECT improved from 4.2 seconds to 0.0003 seconds, which is four orders of magnitude improvement.  Whereas the indexes might cause writes to cost double, or even 10x (just one order of magnitude). You must decide whether the tradeoff is worth it for your application.
For example, if you have frequent SELECT statements that need to be 10,000x faster, and you can tolerate it when your less-frequent writes takes 0.1 seconds instead of 0.01 seconds, then it seems like a good tradeoff.

