----- Original Message -----
> From: "Larry Martell" <larry.martell@stripped>
> Subject: Re: Performance boost by splitting up large table?
> This table is queried based on requests from the users. There are 10
> different lookup columns they can specify, and they can provide any or
That makes it rather more of a bother, as MySQL can't (yet) skip columns in an index, as
far as I'm aware. Someone please correct me if I'm wrong here.
> all of these. Currently each one of the columns has an index on it.
I'm a bit fuzzy on multiple-index queries, but I think support isn't all too sharp.
> Would it be beneficial to create an index with all 10? Rarely are all
> 10 specified in the query. Typically it's 3 or 4. Would it be
> worthwhile to see which are much commonly specified and create an
> index with just those? Or would it be better to put the commonly
> selected columns on the index with the lookup columns?
You may want to grab a day or week's worth of queries (either general_log with all the
overhead and disk space that entails, or tcpdump) and pump that through pt-query-digest
(Percona Toolkit) to see which combinations of fields are most often used, and add the
necessary covering indices to help those queries.
A few points to keep in mind during analysis:
* order of fields in the where clause is largely irrelevant (although
most-selective-first is preferred)
* not all the fields in the index must be queried; but you MUST query a full prefix set
-> ie, for index (a, b, c, d) you can query (a, b) but for (a, c) only (a) will be
* every index constitutes a (small) performance penalty upon table updates, so don't go
too wild either :-)
* plenty of memory, at least enough to keep the working set in memory
* faster disks (SSD, or FusionIO things) helps bundles for data that doesn't fit in
* if you notice a tendency for multiple users (say, a dozen) to run identical queries
(and that means /bitwise/ identical query text, down to the last space), the query cache
might help. Don't make it too big, though, a couple of meg should suffice.
Unhappiness is discouraged and will be corrected with kitten pictures.