Tagged Questions

A database structure that can improve the speed of queries at the cost of disk space and slower inserts/updates. It stores a copy of one or more columns but structures the data differently to allow for faster access.

learn more… | top users | synonyms (2)

1
vote
2answers
39 views

Using sp_BlitzCache - Diagnosed as “Index Hoarder: Addicted to nulls” [duplicate]

Context: MS SQL Server 2012 In using Kendra Little's awesome tool, sp_BlitzCache, a number of tables were diagnosed as "Index Hoarder: Addicted to nulls". Is it best practice, in most cases, to ...
0
votes
1answer
23 views

schema for mysql database with 30million rows

We am building a SaaS backend for restaurants. We integrate directly with POS, so each POS keeps sending customer orders that we store in a following table in mysql. CREATE TABLE name ( `id` ...
1
vote
0answers
59 views

Duplicate Key Entry

I've got a problem on SQL Server 2008 where I'm getting a duplicate key entry error on a process that's worked fine in the past. What is really weird is that if I drop the clustered index, insert the ...
0
votes
1answer
19 views

Does remove document automatically remove the index that reference this document?

I am wondering if removing the document will automatically update the index if this document is referenced?
1
vote
2answers
75 views

What Columns to create Indexes on?

I have a table , which contains upto 20 columns. The data is selected, inserted, and deleted by 3 columns: Username, StartDate, and EndDate. These 3 columns are NOT unique. The table is a ...
-3
votes
0answers
39 views

Indexing in SQL Server [on hold]

I'm having a doubt regarding index creation in SQL Server. Scenario: In my database we have an index which is being used (or supposed to be used) by a query in stored procedure (SP1), but I have ...
0
votes
0answers
29 views

How best to index large table with many index keys

In our datawarehouse load, we bring data in the following manner. 1. from source to stage 2. stage to a consolidation layer 3. from consolidation layer to operational data store (ODS) ...
0
votes
1answer
58 views

Does the order of a mysql multi-column MySQL index matter - if you're always using all columns in the query?

I'm aware that when you define a MySQL index that the order of the fields matters if the query is only using part of the index, as described here. But if your query is always going to use all columns ...
0
votes
1answer
35 views

Does view take advantage of table indexes

I have a table with a clustered index and 2 non clustered index. Now, in my view I am selecting everything from my table create view dbo.MyView as Select * from MyTable Now, when I am using this ...
0
votes
1answer
29 views

MySQL uses intersection instead compound index

I have compound index on following columns: (country_id, lazy, has_avatar, inactive, gender, id, age, challenge_count) When I run following query: SELECT COUNT(*) FROM `user_profile` WHERE ...
0
votes
1answer
8 views

Record in table won't show in form

I have an Access 2010 database that has "frmCatalog" which is sourced from the Catalog table. The Catalog table shows a record with the primary key "Acq" = "5203." But the form does not show 5203. ...
0
votes
2answers
51 views

Solution on a complex sub select query SQL Server

I created a SQL query that requires me to pull data from 2 separate databases, and I noticed that what I did is this: Select *, (Select Z.f From DatabaseZ.TblZ Z where Z.w = A.w) as valueZA, ...
3
votes
2answers
83 views

Why postgres is using an odd index with no relation to the query

(Debian 7, Postgres 9.3, dedicated machine with huge cache) I have one big table called process_data (14gb) and another tiny lookup table called process_location. I'm doing a query between these two ...
1
vote
1answer
38 views

What happens if an index build is cut half way through?

Today I ran a create NC index but stopped the execution after running for 1 hour 46 minutes. The main reason being I started to get worried about the disk space getting lower. It consumed roughly 10 ...
1
vote
0answers
26 views

MySQL table partition strange behavior (slow query suddenly)

(I asked this question in "stack overflow" and was recommended to ask in here). (MySQL version: 5.6.15) I have a huge table (Table_A) with 10M rows, in entity-attribute-value model. It has a ...
2
votes
2answers
72 views

What is difference between is_unique column and is_unique_constraint columns in sys.indexes

We have two columns is_unique and is_unique_constraint for unique constraints in sys.indexes and I want to clarify few concepts based on these two columns Create statement for both types of ...
0
votes
1answer
46 views

Removing some English token words (such as “are” or “or” etc.) from the system stoplist of full text search?

One of our table has full text catalog on it. It contains some data in a language other than English which makes some words such as "are", "or" useful. There is no stop list defined but these words ...
1
vote
0answers
24 views

MySQL not using multicolumn index on self join

I'm trying to optimise a query that performs an inner join with itself. The table size is currently 10GB ~50 million rows. /* Count all occurrences between start and end site id of a given speed ...
0
votes
0answers
20 views

Join does not not use index in PostgreSQL?

I have a statement that selects from two tables news and status. It seems that the index on the column status of the table news is not used. This table has about 70.000 rows and the query costs too ...
0
votes
1answer
25 views

Only Filtered condition gets deleted from index after datatype change using SSMS

In SSMS if I create a filtered index we can see filtered condition in index properties. But if I change column type of a column involved in filtered index using SSMS , then after column type changes ...
0
votes
1answer
27 views

Reducing size of datatype involved in index fails where as increasing size work

This question is related to my previous question Database events that will cause deletion of index For example if i create a table like CREATE TABLE dbo.Customers (ID INT IDENTITY(1,1) PRIMARY KEY ...
1
vote
1answer
33 views

Cancelling / Stopping ALTER INDEX REORGANIZE

ALTER INDEX [myIndex] ON [dbo].[myTable] REORGANIZE WITH ( LOB_COMPACTION = ON ) I have the above query running for 16 days (still running), the table is a dummy table used for benchmark tests, it ...
1
vote
1answer
32 views

How to index a query `WHERE field IS NULL` (postgres)

So I basically have a table with lots of inserts, setting one of the fields (uploaded_at) to NULL. Then a periodic task selects all the tuples WHERE uploaded_at IS NULL, processes them and updates, ...
3
votes
1answer
68 views

Overlapping indexes

I have two non-clustered indexes on one of my dbs and their definition are: CREATE NONCLUSTERED INDEX [NC_Index1] ON [dbo].[anyTable] ( [X] ASC, [Y] ASC, [Z] ASC ) INCLUDE (A , ...
6
votes
1answer
149 views

Query optimizer recommends adding index instead of using existing index

I am trying to determine why the query optimizer in SQL Server is recommending creating a new index instead of using an existing index that appears to be sufficient for the query. First the table. ...
4
votes
1answer
100 views

Proving the usefulness of a specific index

I'm looking for a way to prove that some recently added indexes are positively impacting a production system. I've collected some dmv statistics to show that the indexes are indeed being used ...
0
votes
1answer
164 views

What is fragmenting my index on a table with stable traffic?

I am using Ola Hallengren's solution for optimizing indexes. I run it on Sunday every week. The index has had low fragmentation for the last 6 months and it hasn't needed reorganization. The way it's ...
2
votes
0answers
56 views

work/cache table issues, heavy inserts with heavy reads

we have a work table that stores availability of products. This table is updated with millions of inserts every night and medium amounts of inserts throughout the day. during the updates, we delete ...
5
votes
1answer
178 views

What is a non-clustered index scan

I know what table scan, clustered index scan and index seek is but my google skills let me down to find a precise explanation into non clustered index scans. Why and when a query uses a non clustered ...
1
vote
2answers
57 views

Optimizing for data that does not fit in memory

We have a database that contains around 235 GB of data (calculcated using pg_total_relation_size for each table) on a server with around 77GB of memory. The server is used for nothing else (aside from ...
3
votes
0answers
37 views

Find the current task of a long time running indexes reorganize operation

In a scheduled maintenance window, I've manually rebuilt/reorganized some indexes of a SQL Server 2012 database. It was an offline task, since my SQL Server license does not allow online ...
2
votes
3answers
56 views

Additional space required for INDEX REORGANIZE

MSDN states: ALTER INDEX REORGANIZE; however, log space is required. The database recovery mode is Simple, does that implies the log space is actually NOT required ?.
1
vote
3answers
56 views

Performance first low, then high after adding index, then keep high after drop index

I am testing performance a complicated select query. It is very slow. using explain, I have found a ALL in type COLUMN. Then I ADD INDEX. result comes out fast. However, when I DROP the INDEX I ...
2
votes
1answer
40 views

1 query using OR operator in where clause vs multiple query equal condition where clause in mysql

case 1: 1 query using OR operator in where clause like select * from users where name='smith' or nick='smith' (using index_merge ) case 2: multiple query equal condition in where clause select * ...
0
votes
3answers
63 views

index rebuild/reorganize frequency

I am a dba by accident. I am doing index rebuild/reorganize. I would love to use some scripts once I become comfortable doing these tasks. I did go through some of the forum pages regarding this ...
1
vote
3answers
37 views

Postgres inherit indexes for partitioned tables

I have a table with approximately 60 million rows that I have partitioned by state into 53 sub-tables. These tables "inherit" the big table like so: CREATE TABLE b2b_ak (LIKE b2b including indexes, ...
0
votes
1answer
39 views

MongoDB Benchmark with write-intensive on geo-indexed DB

I am using PostgreSQL 9.3 on AWS RDS to store geo information into an indexed table of points. My data is write-oriented, meaning that I write nearly 1,000 points per each geo search query. I am ...
0
votes
1answer
38 views

[sys].[dm_db_index_usage_stats] returns more entries in some databases

I am using the following query to get some indexes usage statistics: SELECT DB.[name] ,OBJECT_NAME(IDX.[object_id]) AS [Table] ,IDX.[name] AS [Index] ,US.[user_seeks] ...
1
vote
1answer
40 views

Choice of clustered key when almost all columns would be part of a unique index anyway

I am creating the following table CREATE TABLE dbo.COMM_SHELF_BLOCK_ACCESS ( KeyId uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT DF_COMM_SHELF_BLOCK_ACCESS_KeyId default NEWSEQUENTIALID(), ...
3
votes
1answer
49 views

PL/pgSQL - Indexes vs Collation vs Pattern Ops

I'm using PostgreSQL 9.3. Having already read articles and answers on DBA stackexchange: Why would you index text pattern ops on a text column PostgreSQL documentation: Indexes - Opclass Pattern ...
1
vote
0answers
30 views

Best index to apply for modifier query in i2b2 [closed]

Below is a query being run by a tool called i2b2. I'm unable to change how the query is run. What can I do to the indices in "observation_fact" and/or "modifier_dimension" tables so that this query ...
0
votes
0answers
35 views

TEMP Tablespace Exceeded when DBMS_REDIFINITION

Oracle Environment. Table size is 500Gb. Free space left on hard drive - 300Gb. TEMP Tablespace is currently 20GB Task: do the DBMS_Redefinition for the table (partition by month) and optimize ...
0
votes
1answer
23 views

Does PGStrom calculate relational comparisons?

I prefer index calculation and comparison be executed on the GPU. I have stumbled upon PGStrom. It seems one could infer that it will calculate index comparisons, but the claim doesn't seem to be ...
0
votes
1answer
50 views

What is the cost of adding a foreign key in mysql?

I have an application which has a product_def table defining all products. The recrods in product_def are fixed and will never be deleted. CREATE TABLE `product_def` ( `product_id` INT( 5 ) ...
0
votes
1answer
58 views

MySql: Slow join and group by on indexed columns

The following query takes 15-20s to run SELECT "Trade_US"."ID" AS "Trade_US.ID", "Server_US"."Name" AS "Server_US.Name", "Trade_US"."TradeTypeID" AS "Trade_US.TradeTypeID", "Users"."ID" AS ...
0
votes
0answers
22 views

get mysql index size split by index [duplicate]

I have ran this query : SELECT Table_NAME "TableName", data_length /1024 /1024 "table data_length in GBytes", index_length /1024 /1024 "table index_length in GBytes", ...
1
vote
1answer
44 views

Whats wrong with my index?

I have a table, here is the structure: CREATE TABLE `table_data_cache` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `date` date DEFAULT NULL, `enterTollgateNumber` bigint(20) DEFAULT NULL, ...
1
vote
1answer
76 views

BitmapIndexScan vs IndexScan tuning

I have the following test case: CREATE SCHEMA IF NOT EXISTS long; SET search_path TO long,pg_temp,pg_catalog; CREATE TABLE long WITH (autovacuum_enabled=off) AS SELECT id, (random()*1000)::int AS ...
3
votes
1answer
59 views

Using an index for both asc and desc on a string column

I know a trick to make order by queries faster when we want to order by asc and desc. For integers store a negated value. So if I store -7,-11,-8,-5,-1,-2 the order by asc will give -11, -8, -7, 5, ...
2
votes
2answers
15 views

PostgreSQL query slows due to ORDER BY even with an index

I have the following query to find offices that service a given area. When I run this query without the "ORDER BY" clause, it takes a less than 0.1 seconds. When I add the ORDER BY clause, it takes ...