Posts Tagged - databases

Don’t create indexes in every column. This slows things down on insert, delete or update operations.

You may create an index for columns that are common in WHERE, ORDER BY or GROUP BY clauses. You may also consider adding an index in columns that are used to relate other tables JOIN.

Index are best used for big domain fields such as ids, names, surnames. Don’t use them for male/female fields.

A good recommendation is to measure performance time before and after the creation of that index. If your index doesn’t improve performance remove it as it causes overhead.

Reference(s)

https://stackoverflow.com/questions/7744038/decision-when-to-create-index-on-table-column-in-database

Read More

UNION

The union sentence is used to accumulate results for two SELECT sentences.

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2

We have the following tables

company1

company2

Read More

Find an exact match of a nested field (note that .keyword has to be added to string fields)

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "filemetadata.title.keyword": "File test.pdf"
          }
        }
      ]
    }
  },
  "size": 200
}

Read More