Tagged Questions

0
votes
0answers
8 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 ...
4
votes
3answers
329 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 ...
2
votes
1answer
136 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 ...
4
votes
1answer
67 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 ...
3
votes
1answer
181 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 ...
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
368 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
315 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 ...
4
votes
2answers
131 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 ...
7
votes
2answers
548 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
1answer
128 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
62 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
272 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
286 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 ...
3
votes
1answer
192 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
924 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
256 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 ...
7
votes
3answers
457 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 ...
6
votes
1answer
180 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 ...
6
votes
1answer
493 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 ...
3
votes
2answers
729 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
101 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
283 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 ...
1
vote
2answers
253 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 ...
1
vote
2answers
171 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 ...
9
votes
4answers
559 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. ...
5
votes
1answer
434 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
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 ...
4
votes
2answers
234 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 ...