Toad World® Forums

Just How Difficult *IS* Index Tuning?

Here's the Problem

In a recent blog post I discuss some difficulties that one of our customers had when attempting to index-tune a poorly performing report query: adding one new index to one table caused the report query to blaze , but it messed up a bunch of other SQL operations that the DBA team had to fix. Has that ever happened to you?

Index tuning is best done not by looking at an individual SQL statement or two, but by looking at the concept of a SQL workload (many SQL statements). Ah, but just finding new indexes and how they can be grouped—let alone testing which ones are the best—can be very difficult and time-consuming.

If you haven’t read the blog, I encourage you to read it, but even if you haven’t, it's ok...the quiz questions below might give you an appreciation for how difficult it can be to consider new indexes and their combinations.

Here's the scenario.

Suppose you have a SQL workload (say, a list of SQL statements). Those SQL statements only access five small tables. Only five. Each table has only three columns. Only three. Your goal? Find the best combination of indexes that maximize the performance of SQL in the workload against those five tables and their columns.

Sounds easy, right? You may be surprised. See if you can guess the answers to the questions below. To keep it simple, let’s consider only “vanilla” column-based indexes. Composite indexes are ok, but don’t consider clustered-type indexes, function-based, or other index types.

Here's the Quiz

Post your replies here in the forum by July 17, and we’ll send you some cool Toad stickers. Answers will be included in part 3 of my Turnpike blog post in July. Stay tuned!

  1. What is the total number of possible indexes on just one table? How about on all five tables?
    (Two questions in one because they’re easy!)

  2. What is the total number of different combinations of these indexes, choosing up to, say, three indexes at a time?

  3. How about the total number of index sets choosing up to, say, 10 indexes at a time?

  4. (Extra Credit) What is the total number of index sets, regardless of the number of indexes in each set?

3 Likes

Hey everyone, I'm not going to "grade" your answers here, so don't be shy about responding... just a bit of fun to lighten your day.

And i'll give you a head start... answer to question 1a: number of possible indexes for just one table is 15.

1 Like

Hi Gary,

ok. I'll bite....

1. One Table, How many indexes possible with three columns?

Single columns: A, B and C = 3 indexes.
Two columns: AB, AC, BC, BA, CA and CB = 6 indexes.
Three columns: ABC, ACB, BCA, BAC, CAB and CBA = 6 indexes.
Total = 3 + 6 + 6 = 15.

For 5 tables, all with 3 columns, 15 * 5 = 75 total indexes.

2. How many combinations of three indexes?

C(N,R)  = N! / (R! * (N - R)!)
C(75,3) = 75! / (3! * (75 - 3)!)
        = 75! / (6 * (72)!)

My calculator doesn't do 75! or 73!, so the anwer was cheatingly retrieved from an online calculator at https://www.omnicalculator.com/math/factorial.

Without duplicates, 67,525 different combinations. With duplicates 73,150 combinations.

(75! = 24,809,140,811,395,398,091,946,477,116,594,033,660,926,243,886,570,122,837,795,894,510,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000)

3. How many combinations of 10 indexes?

C(N,R)   = N! / (R! * (N - R)!)
C(75,10) = 75! / (10! * (75 - 10)!)
         = 75! / (10 * (65)!)

Online again, gives 828,931,106,355 unique combinations or 2,761,025,887,620 with duplicates. (That's a lot!)

4. Total number of index sets, regardless of how manuy indexes in the set?

Sorry, no idea. It was at this point my aging brain decided it had had enough and escaped through my ears, ran across the road and was last seen catcing a bus! :slight_smile:

Cheers,
Norm. [TeamT]

1 Like

Just discovered, I don't need the C(N,R) function/formula really. To pick 3 indexes from 75 options, it's simplified to:

(75*74*73)/(3*2*1)
= 405150/6
= 67,525

And the pick 10 from 75 is similar:

(75*74*73*72*71*70*69*68*67*66) / (10*9*8*7*6*5*43*2*1)
= 3.0080251e18 / 3628800
= 8.2893107e11

Cheers,
Norm. [TeamT]

2 Likes

I knew there's got to be folks out there who might want to take a crack at this! On the right track, sir... remember: answers coming up in the next blog!

1 Like

1: 6, 30
2: 30 * 29 * 28 = 24,360
3: 30* 29 * ... * 22 * 21 = 109,027,350,432,000‬
4: 30! = 2.6525285981219105863630848e+32‬
(I would argue you would never index all columns or you would just use an index organized table...)

So, the first quiz question I tried to keep simple, and even furnished the correct answer. However, I was wondering if anyone would catch the subtlety regarding practical implementation (IOTs aside). So kudos to you, Lawrence, for recognizing that it doesn't buy us anything to create a composite index on all three columns of each table. e.g. once you define a composite index on two of the three columns, then there's only one choice for the third column.

Responders, you're definitely on the right track here, although, Lawrence, 3+6= 9.

D'oh! Always get nailed by the simple things.

So did I. I indexed all three columns. Heavens above! (As almost nobody says these days!)

Cheers,
Norm. [TeamT]

Well, Gromit's owner, Wallace, says that all the time... :upside_down_face:
(for those who actually know who I'm referring to...)