Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [index-design]

12 questions
12
votes
1 answer

When to use multiple tables in DynamoDB?

The DyanmoDB best practices make it clear that: You should maintain as few tables as possible in a DynamoDB application. Most well designed applications require only one table. I find it amusing then that just about every single tutorial I've seen…
David Eyk
  • 537
  • 1
  • 5
  • 11
9
votes
1 answer

Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong?

I am trying to improve this (sub-) query being part of a larger query: select SUM(isnull(IP.Q, 0)) as Q, IP.OPID from IP inner join I on I.ID = IP.IID where IP.Deleted=0 and (I.Status > 0 AND…
Magier
  • 4,827
  • 8
  • 48
  • 91
3
votes
0 answers

Modeling a database structure for a trip management business domain

I am sketching up a database design and it is giving me some troubles. Basically something just "smells" about this design but I can't seem to arrive at a better way to do it. For example, the Joins needed to get back to Person table could be…
thehnrytrapezoid
  • 31
  • 2
2
votes
2 answers

Is it needed to add additional column to make this clustered index unique?

I have a table as listed below in SQL Server 2012. There is a clustered index on RequisitionID – but this column is not unique. There can be many ProductID for one RequisitionID. CREATE TABLE [dbo].[RequisitionProducts]( [RequisitionID] [int]…
LCJ
  • 900
  • 3
  • 7
  • 30
1
vote
1 answer

Postgres too Many References to Single Table is good or bad design

I have a Postgres database schema for a web app that needs real-time data and hence a huge volume of select, update, insert queries. Data manipulation is done via Postgres stored procedure mostly I have designed each table to have {created, updated,…
Smarhacker
  • 11
  • 1
1
vote
0 answers

Elastic: Are Analyzers only for `text` type fields?

Most of the documentation about Elastic Analyzers assumes that you NEED an Analyzer and so the documentation talks mostly about how Analyzers work and the differences between the different Analyzers. But I had trouble finding authoritative…
Trevor Boyd Smith
  • 111
  • 6
1
vote
1 answer

Hundreds of millions of rows: Index by composite BIGINT, VARCHAR, or CHAR of HASH?

Given the following: Target platform: MySQL 5.7, using InnoDB. Scenario: Storing hundreds of millions of email addresses (plus some properties not used in queries). All queries will be done by knowing the email address beforehand. Proposed solution:…
marcelog
  • 111
  • 4
1
vote
1 answer

The Logical Type of The Document in CouchBase

How should the logical type of a document get specified in CouchBase? Using a field for the type? Or employing separators in keys like product::app::123id? Currently I'm putting a document type, inside the document itself, inside a string field…
Kaveh Shahbazian
  • 113
  • 4
1
vote
1 answer

Multiple Non-Indexed Views with INCLUDEs vs. Multiple Indexed Views in High Write Situations

When you want to encapsulate your T-SQL to select different subsets of data from the same base tables, is it more efficient to use plain views in conjunction with nonclustered indexes and INCLUDEs on the base tables, or is it better to use multiple…
SQLServerSteve
  • 123
  • 1
  • 2
  • 7
0
votes
0 answers

Is a columnstore index appropriate when all columns are (mostly) unique?

I have a longish table ( 60M rows ) where there are only 7 columns. One is a unique ID, two are datetimes, and two are notes and descriptions. There the notes and descriptions are very regular, except for a tag at the end of the text. So, they're…
Clay
  • 101
  • 6
0
votes
1 answer

Should I use composite key or primary key from other table

We have a Education Project which has following entities : Domain (e.g. Programming, UI/UX, AI, ML), each Domain has 5 Levels (1, 2, 3, 4, 5) Building Blocks - which are like small topics e.g. java, multi-threading, loops, prototyping, user…
j10
  • 309
  • 1
  • 8
  • 16
-1
votes
1 answer

Schema required to find a record in a table with null values

I have the following table. ID Country City Street No 1 UK 2 2 Lon 3 Oxf 19 4 UK Glas 5 US NY Wall 6 US NY 14 7 5 In this table, I have defined some address rules. For example. If Country is UK and house number…
Meysam
  • 101
  • 4