Tagged Questions

0
votes
0answers
6 views

Fine grained (milliseconds) temporal indexes in neo4j

What are the best practices for modeling fine grained timelines in neo4j? If I were to use milliseconds as the grain in the pattern described here and here would it prove somehow problematic and ...
2
votes
1answer
120 views

Bitmask Flags with Lookup Tables Clarification

I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries ...
3
votes
1answer
173 views

Schema design: Use of association (aka: bridge/junction) table vs foreign key constraint with composite index containing a non-key field

This is an inventory database for IT assets. The models used are trimmed in order to focus on the problem at hand. Using SQL Server 2008. Thanks for taking the time to read and for any input you can ...
4
votes
3answers
326 views

Should I seperate frequently updated columns

I have a users table that contains users' information and a column named credits that is frequently updated. On index page I'm showing list of users with basic user information but I don't need ...
4
votes
1answer
64 views

How does PostgreSQL physically order new records on disk (after a cluster on primary key)?

Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
0
votes
1answer
61 views

Book / Tutorials / Website - Creating Custom C Database [closed]

I want to create the custom database for my project. Kindly recommend me Good Book or Tutorials or Website which provide the detailed information. The features want to implement are full text ...
3
votes
1answer
363 views

Very Slow MySQL Queries, even with indexes

I have a relatively large 4-deep relational data setup like this: client_applications: (potentially 1,000's of records)    - ...    - account_id    - ...
3
votes
3answers
308 views

Is a surrogate key better than a natural key in this case

I copied this code from here: CREATE TABLE records( email TEXT REFERENCES users(email), lat DECIMAL, lon DECIMAL, depth TEXT, upload_date TIMESTAMP, comment TEXT, PRIMARY ...
7
votes
2answers
513 views

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? ...
4
votes
2answers
126 views

What happens to the index of a primary key after a DROP CONSTRAINT?

I am running PostgreSQL 9.1.4. I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to : 1 - Remove the pkey constraint on the ...
4
votes
1answer
125 views

MySQL table design of logging tables

I'm not a DBA by any means, but I have a basic understanding of some of the underlying principles of database optomization. Currently we have a table that logs session data, it writes about ...
1
vote
1answer
60 views

Issues on defining a PK on a nvarchar column and indexing the FK referring this PK

Someone from the team, has defined two reference tables with a few records in each, and has defined the PK of these tables on a column of type nvarchar(255). The value exists in this columns are ...
3
votes
2answers
260 views

Index max row size error

Is there a upper bound for an array column? I am getting this error when inserting into the array field - PG::Error: ERROR: index row size 3480 exceeds maximum 2712 for index "ix_data" Here's my ...
3
votes
1answer
281 views

Surrogate key / Primary key: Better to use an existing unique data field or create a key field?

I am not sure if this question has been asked or not. At least I couldn't find it. I am curious about a primary key in terms of efficiency with data searching and retrieval. This is a hypothetical ...
6
votes
1answer
177 views

Performance of query with a range condition and order by

We have the following table (in SQLite on Android) which a tree structure (Nested Set model) of words and their frequencies: lexikon ------- _id integer PRIMARY KEY word text frequency integer ...
7
votes
3answers
452 views

Can spatial index help a “range - order by - limit” query

Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes. We have the following table with a tree structure (Nested Set model) of words and their ...
3
votes
1answer
188 views

Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning

I'm working on an inventory database that uses supertype/subtype model. My question regards creating multiple relationships with the same tables using the primary key and composite indexes that also ...
0
votes
1answer
909 views

How can primary keys in a Netezza table be indexed?

What is the best way to index a Netezza fact table with lots of primary keys. For now, let's suppose we have 10 primary keys. This table is mainly used to store meta data about our database. ...
3
votes
2answers
252 views

What exactly is “pinning” in relation to indexes?

When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions. It is part of a test question, where ...
6
votes
1answer
488 views

Are there any performance benefits to using a hash table with no clustered index?

I have this table: CREATE TABLE [dbo].[relatea] ( [mid] [varchar](16) NOT NULL, [sid] [varchar](16) NOT NULL ) It stores hash matches. Is there any benefit to having this as a heap? It has ...
4
votes
2answers
232 views

Clustered indexing is now must - why?

Earlier, there were non-conclusive to me debates/discussions on whether to (always) engage/avoid clustered indexes. Well, I understood that they are to be used sometimes with proper + specific ...
1
vote
2answers
170 views

Best index strategies for read-only table?

I have a table in SQL Server which has following characteristics: will contain about 1.2M records. first is empty, will be inserted in batch many times (precisely, 64 times), 20-100k records/each ...
1
vote
2answers
153 views

What is wrong in debunking myths about clustered indexes?

What is wrong in code. or its results, illustrating that clustered index is evil [1]? and how to de-debunk, i.e. to return to customary myths and best practices? [1] Debunking myths about ...
3
votes
2answers
716 views

MySQL: Storing unique URLs

I am creating a table wich will contain user-provided URLs. I want those to be unique, so when the user gives me a URL I will first check if the URL exists and if so return the ID for the entry. If ...
0
votes
1answer
100 views

What would be the best way to design a table in Mysql that handles read items in an RSS feed reader?

I'm working on an RSS feed reader, it is mostly functionnal. But after some time beta testing it, I noticed that its very slow running some queries on a table that handles all the items that have been ...
1
vote
1answer
158 views

Design Tagging module in the database

I've a database system which we want to define tags for many tables into it. For example I've videos, photos, ... different entities that we wish to make them taggable. The tags could be anything. By ...
1
vote
1answer
281 views

Does composite cluster index affect performance of seek/scan non-cluster index which is part of the composite?

I have bank transaction table with columns Division Brcode TrDate ProjectCode Accountnumber EffectiveDate DrCr Amount EndBalance In the other table the Brcode itself is unique and is assigned under ...
5
votes
1answer
433 views

Can filtered indexes help improve queries that are based on a time entered or should this be avoided?

I'm just learning about the new filtered indexes in MS SQL 2008 and I'm trying to understand where they would hurt and where they would help. I can see that adding a filtered index to an employees ...
1
vote
2answers
250 views

Indexing of Data Warehouse tables?

Maybe I have misunderstood something but I can recall reading an article which said that there is no reason to create non-clustered indexes on very large data warehousing tables? That is because ...
3
votes
2answers
115 views

What would be the best way to model my simple table?

What would be faster: searching for the right row through a table with 17 different columns, each with a tinyint value of 1-4? Or, searching for the right row through a table with only 1 column with ...
9
votes
4answers
553 views

Where should one put indexes in a time dimension table?

After reading the Questions and Answers from this website about indexes, a question came to my mind. What if, one is using a time dimension table with the lower level of granularity being the day. ...