If you sort or search on both fieldA and fieldB frequently, a single index on the both of them will work better than one index for each of them

I've found out the hard way that this isn't always true -- if you're using inequality matching on both columns, and they both vary greatly, then you're often better using two separate indexes.

In my particular case, I was testing for overlap of the time of a series of telescope images. (about 10M records). Because of the nature of the fields being searched on (start time and send time; essentially infinitely variable), the compound index was useless for the second field and was ignored. Two indexes gave better results, but depending on the size of the range being asked for, no indexes were better for excessively large matches.

(although, I got even better performance by using the knowledge that the end date was always within 1 minute of the start date, so isolating the range using just a single field's index, and then _not_ having an index on the second field for the second weeding.)

...

Anyway, the point was -- nothing's absolute when it comes to tuning. Use whatever benchmarking tools you have, test a few indexing strategies -- sometimes, you'll want to use hints or the equivalent to avoid indexes for some queries. And as nothing's absolute -- in many cases, you won't need to do this unless things are taking too long.

In reply to Re^2: OT: Indexes in MySQL by jhourcle
in thread OT: Indexes in MySQL by isync

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.