Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I will try to explain what does this mean by example. The indexes based on B-tree is not something mongodb specific. In contrast it is rather common concept.</p> <p>So when you create an index - you show the database an easier way to find something. But this index is stored somewhere with a pointer pointing to a location of the original document. This information is ordered and you might look at it as binary tree which has a really nice property: the search is reduced from <code>O(n)</code> (linear scan) to <code>O(log(n))</code>. Which is much much faster because each time we trim our space in half (potentially we can reduce the time from 10^6 to 20 lookups). For example we have a big collection with field <code>{a : some int, b: 'some other things'}</code> and if we index it by a, we end up with another data structure which is sorted by <code>a</code>. It looks this way (by this I do not mean that it is another collection, this is just for demonstration):</p> <pre><code>{a : 1, pointer: to the field with a = 1}, // if a is the smallest number in the starting collection ... {a : 999, pointer: to the field with a = 990} // assuming that 999 is the biggest field </code></pre> <p>So right now we are searching for a field a = 18. Instead of going one by one through all elements we take something in the middle and if it is bigger then 18, then we are dividing the lower part in half and checking the element there. We continue till we will find a = 18. Then we look at the pointer and knowing it we extract the original field.</p> <p>The situation with compound index is similar (instead of ordering by one element we order by many). For example you have a collection:</p> <pre><code>{ "item": 5, "location": 1, "stock": 3, 'a lot of other fields' } // was stored at position 5 on the disk { "item": 1, "location": 3, "stock": 1, 'a lot of other fields' } // position 1 on the disk { "item": 2, "location": 5, "stock": 7, 'a lot of other fields' } // position 3 on the disk ... huge amount of other data { "item": 1, "location": 1, "stock": 1, 'a lot of other fields' } // position 9 on the disk { "item": 1, "location": 1, "stock": 2, 'a lot of other fields' } // position 7 on the disk </code></pre> <p>and want an index { "item": 1, "location": 1, "stock": 1 }. The lookup table would look like this (one more time - this is not another collection, this is just for demonstration):</p> <pre><code>{ "item": 1, "location": 1, "stock": 1, pointer = 9 } { "item": 1, "location": 1, "stock": 2, pointer = 7 } { "item": 1, "location": 3, "stock": 1, pointer = 1 } { "item": 2, "location": 5, "stock": 7, pointer = 3 } .. huge amount of other data (but not necessarily here. If item would be one it would be somewhere next to items 1) { "item": 5, "location": 1, "stock": 3, pointer = 5 } </code></pre> <p>See that here everything is basically sorted by item, then by location and then by pointer. The same way as with a single index we do not need to scan everything. If we have a query which looks for <code>item = 2, location = 5 and stock = 7</code> we can quickly identify where documents with <code>item = 2</code> are and then the same way quickly identify where among these items item with <code>location 5</code> and so on.</p> <p><strong>And right now an interesting part</strong>. Also we created just one index (although this is a compound index, it is still one index) we can use it to quickly find the element </p> <ul> <li>only by the <code>item</code>. Really all we need to do is only the first step. So there is no point to create another index {location : 1} because it is already covered by compound index.</li> <li>also we can quickly find only by <code>item and by location</code> (we need only 2 steps). </li> </ul> <p>Cool 1 index but helps us in three different ways. But wait a minute: what if we want to find by <code>item and stock</code>. Oh it looks like we can speed up this query as well. We can in log(n) find all elements with specific item and ... here we have to stop - magic has finished. We need to iterate through all of them. But still pretty good.</p> <p>But may it can help us with other queries. Lets look at a query by <code>location</code> which looks like was already ordered. But if you will look at it - you see that this is a mess. One in the beginning and then one in the end. It can not help you at all.</p> <p>I hope this clarifies few things:</p> <ul> <li>why indexes are good (reduce time from O(n) to potentially O(log(n))</li> <li>why compound indexes can help with some queries nonetheless we have not created an index on that particular field and help with some other queries.</li> <li>what indexes are covered by compound index</li> <li>why indexes can harm (it creates additional datastructure which should be maintained)</li> </ul> <p>And this should tell another valid thing: index is <strong>not a silver bullet</strong>. You can not speed up all your queries, so it sound silly to think that by creating indexes on all fields EVERYTHING would be super fast.</p>
    singulars
    1. This table or related slice is empty.
    1. POMongoDb: how to create the right (composite) index for data with many searchable fields
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USSalvador Dali
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload