Uploaded image for project: 'Documentation'
  1. Documentation
  2. DOCS-31

Document how to write performant queries

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Last comment by Customer:
      true

      Description

      (Documentation written by Doug Green) Mongo uses btrees, and for the most part, indexes work the same in mongo as they do in mysql. For most examples below assume an index on a,b,c.

      1. The sort column must be the last column used in the index.

      • good:
      • find(a=1).sort(a)
      • find(a=1).sort(b)
      • find(a=1,b=2).sort(c)
      • bad:
      • find(a=1).sort(c)
      • even though c is the last column in the index, a is that last column used, so you can only sort on a or b.
        1. The range query must also be the last column in an index, this is an axiom of 1 above.
      • good:
      • find(a=1,b>2)
      • find(a>1 and a<10)
      • find(a>1 and a<10).sort(a)
      • bad:
      • find(a>1, b=2)
        1. Only use a range query or sort on one column.
      • good:
      • find(a=1,b=2).sort(c)
      • find(a=1,b>2)
      • find(a=1,b>2 and b<4)
      • find(a=1,b>2).sort(b)
      • bad:
      • find(a>1,b>2)
      • find(a=1,b>2).sort(c)
        1. Conserve indexes by re-ordering columns used in straight = queries
      • If you have the following two queries, then you only need a single index, on a,b,d,c
      • find(a=1,b=1,d=1)
      • find(a=1,b=1,c=1,d=1)
      • When you need to sort this gets more difficult, and you might need two indexes
        1. Never use Mongo's $ne or $nin operator's
      • When excluding just a few documents, it's better to retrieve extra rows from Mongo and do the exclusion in php
        1. Never use Mongo's $exists operator
      • If you need to check for the existance of something, then you'll have to denormalize this value.

      The doc is already in wiki notation so it must be easy to move.

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            chx Karoly Negyesi
            Participants:
            Last commenter:
            Jonathan Dahl Jonathan Dahl
            Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Days since reply:
              11 years, 1 week, 3 days ago
              Date of 1st Reply: