3

I have a query that joins across multiple tables which has poor performance and so I'm using trial and error techniques to create composite indexes to try and improve the perf based on the columns I'm filtering on but I'm not getting the desired results. I am using The EXPLAIN plan to try and determine a suitable indexing strategy.

Can anyone suggest another tool that can help with this?

dre
  • 1,004
  • 1
  • 11
  • 27
  • Answers are given at http://stackoverflow.com/questions/1823685/when-should-i-use-a-composite-index – jdiver May 12 '14 at 11:30
  • Saw that and have been through it. I have edited my question to include the fact that I'm already using EXPLAIN but was wondering if there was anything else out there that helps. Looks like the answer is no. – dre May 12 '14 at 14:35
  • This community (not me) is quite good at analysing EXPLAINs, but they do like to see proper DDLs – Strawberry May 12 '14 at 14:42

1 Answers1

8

There is no tool to tell you the right indexes. There are some tools that claim to do "query analysis" but as far as I've seen, all they do is run EXPLAIN for you, and perhaps identify the queries that account for the greatest response time on your server.

So the solution is to learn how indexes work, and how to build composite indexes. There is a relatively straightforward method of adding indexes to a composite index. I describe it in my answer to Finding the Optimal Order of Multi-Column Index and in my presentation How to Design Indexes, Really.

You can use a similar method for joined tables.

Community
  • 1
  • 1
Bill Karwin
  • 499,602
  • 82
  • 638
  • 795
  • Thanks @Bill. I'm happy to further my knowledge on the subject. Just checking to make sure that there wasn't some sort of industry standard tool that I've been living without. Sometimes you find there is.. sometimes not. I'll have a read of your presentation. Much appreciated. I'll mark this as the accepted answer. – dre May 12 '14 at 17:39