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)

6
votes
0answers
266 views

Fast hamming distance queries in postgres

I have a large database (16M rows) containing perceptual hashes of images. I'd like to be able to search for rows by hamming distance in a reasonable timeframe. Currently, as far as I properly ...
3
votes
0answers
54 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 ...
3
votes
0answers
114 views

Why would function based indices I’ve created lower the cost but now show up in the explain plan breakdown?

Sorry for the terrible column/table names but since this is for a work project I wanted to insure it was ok to ask. I was just hoping to at least learn why I'm not seeing my functional indices being ...
3
votes
0answers
204 views

SQL Server 2012 Error when creating Selective XML Index on XML Query Plan

I'm trying to create a database solution that will enable me to optimise high CPU/IO/Duration queries to improve overall performance of the database. Part of the optimisation requires persisting ...
3
votes
0answers
152 views

custom population of full text index

Is there any way to populate a full text search index without storing the backing information in the database? I don't mean using remote-blob-storage or FILESTREAM types. The backing information is a ...
3
votes
0answers
158 views

Key Lookup and Full-text index

I have the following query: SELECT T2.Title FROM TitleTable T1 INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1 WHERE T1.FKID2 = @ID_PARAM1 AND T2.FKID2 = @ID_PARAM2 AND ...
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 ...
2
votes
0answers
66 views

Can there be any case where it would require additional indexes while all columns compose primary key

Ok i don't know whether this question is asked before or not Here my table SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblProductPagesFeatures]( ...
2
votes
0answers
24 views

See indices implicitly created for foreign key constaints in MySQL

When I add a foreign key constraint to my table, SHOW INDEX doesn't show any new indices, but the overall space used by the indices in that table increases. Apparently, the index for the field I put a ...
2
votes
0answers
237 views

Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?

I have a query on Oracle 11g of the following type, which results in a series of highly inefficient full table scans: select t.Id, t.ObjectId, t...[other Tasks columns] , od1...[ObjectData1 ...
2
votes
0answers
94 views

Disabled index still incurs overhead when inserting

I have a 38 million row table that I periodically truncate and refill. I disable the indexes (non-clustered) when I do this. But when I included the Execution Plan I noticed SQL Server 2005 is still ...
2
votes
0answers
140 views

Postgres 9.3 - deletes terribly slow for 10 minutes after dropping indexes/constraints

If I run a script which drops indexes (17 of them, CONCURRENTLY makes no difference except that without it I get the occasional deadlock), then constraints (20 constraints on 18 tables) and then run a ...
2
votes
0answers
311 views

PostgreSQL index array of int4range using GIN / GIST - custom operator class

Here is my table: CREATE TABLE mytable ( id INT NOT NULL PRIMARY KEY, val int4range[] ); I want to index the val column: CREATE ...
2
votes
0answers
68 views

How can one improve the performance of a query that selects on a low cardinality column in MySQL?

I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...
2
votes
0answers
250 views

Best Practice to update columnstore indexed tables

I have a table with aprox. 22 Bn rows, for each month I have made one partition that and each partition has two data files. I created the columnstore index on this table and the performance ...
2
votes
0answers
132 views

SQL fulltext indexing ranking combined with ranking used by window's file indexing service

We have a list of records contained within a MS SQL database table, we have enabled "Full Text Indexing" on the table. Also, we have set up a window's file indexing service and added a catalog ...
2
votes
0answers
36 views

Can I read data from a 'hash field' in a legacy Ingres database?

I have been asked to read and present data from the 'hash fields' of an Ingres 2.0 database, which are apparently multiple pieces of data stored together in a single field in various combinations. I ...
2
votes
0answers
211 views

MS-SQL index not using all the available columns as seek predicates

I have a sql query on a large table (>17 million rows) that some times does not populate all the predicates before performing index seek, which results in not filtering out enough rows and slowing the ...
1
vote
0answers
34 views

How do I recover from MySQL InnoDB table/index corruption?

While trying to upgrade a database (in place) I got the following error. The server starts up and everything seems to be fine, except that the table mentioned in the message is no longer accessible. ...
1
vote
0answers
36 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 ...
1
vote
0answers
28 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 ...
1
vote
0answers
53 views

I am not able to create/reindex a unique index in a table

I have a table that contains user locations (called public.user_location) and is the following: Column | Type | Modifiers id | ...
1
vote
0answers
58 views

Best practice for storing image order number in a database

I am designing a social networking website(mysql with InnoDB engine + php) where I need to store image sequence in a database. The table structure is as follows:- PicId (BIGINT, AUTO INCREMENT, PK) ...
1
vote
0answers
51 views

Indexed query fails to return rows in large (15 billion rows) MyISAM (MySQL) table

I have a very large table (15 billion rows) with a structure like this: CREATE TABLE `textlines` ( `PGUID` binary(16) DEFAULT NULL, `StatID` int(11) DEFAULT NULL, `Time` datetime(3) DEFAULT ...
1
vote
0answers
23 views

Why do these two queries (which I think are equivalent) rely on different keys?

We have a query that we don't like, since it ends up doing a full table scan. EXPLAIN SELECT count(*) AS c, location.name FROM study LEFT JOIN location ON study.location_id = location.id ...
1
vote
0answers
46 views

Guidelines on best indexing strategy for varying searches on 20+ columns

I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation. Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search. The problem: ...
1
vote
0answers
48 views

Index Maintenance

I have a query thats performing badly and has a RID lookup. There are already quite a few indexes on the table and in the query stats I can see they are used although some less than others and all ...
1
vote
0answers
136 views

What is a loose index scan?

I was reading that MySQL can not use an index on 2 range conditions due to not having a "loose index scan" What exactly does this mean? Is it related to the underlying or the implementation of the ...
1
vote
0answers
399 views

Error with Solr DataImporter using Mongodb

I've configured my environment and I've downloading Solr 4.7 The idea is make an indexing my mongodb database. For this I've installed the mongo-connector like appear here: ...
1
vote
0answers
44 views

Database tuning and possibly unexpected results

(This is cross-posted from stackoverflow; I initially put it in the wrong place.) So I have a database that's working a bit slower than I would expect and I'd like to describe what I'm doing so ...
1
vote
0answers
81 views

How to take advantage of multiple spatial indices?

All, I'm building a view which uses a spatial intersect (STIntersect in MSSQL 2012) between points (student addresses with address information) and polygons (school boundary polygons with school ...
1
vote
0answers
57 views

SQL Server Full-Text Index with Stoplist

I've searched this site and the internet in general and can't find any information which answers my question, so I thought I'd post it here. I have Full Text search operating on a database in SQL ...
1
vote
0answers
54 views

What's Scalar Operator in the plan?

I have a doubt. What means this Scalar Operator in the print below? I've been investigating our database cause we have severals RESOURCE_SEMAPHORE_QUERY_COMPILE . I've seeing a lot of queries using ...
1
vote
0answers
126 views

MySQL join query indexes

After recently discovering the MySQL feature to log queries that aren't using indexes, I've been chasing these down in a large app, looking for places to improve performance. There is at least one ...
1
vote
0answers
3k views

How to speed up query on table with millions of rows

The Issue: I'm working on a big table that consists about 37mln rows. Data include measurements of many devices made in certain time e.g. '2013-09-24 10:45:50'. Each day all of those devices are ...
1
vote
0answers
34 views

table scheme definiation it this way that insert duplicate into another table

I have a table in which there is a column name with SP varchar(10) NOT NULL. I want that column always to be unique so i created unique index on that column . My table schema as follows : CREATE ...
1
vote
0answers
203 views

Fulltext search returning too many rows

Server: MySQL 5.1.66-0+squeeze1 My problem: I have two databases with the same tables. One is for the testing environment and one for production. When I perform a MATCH AGAINST on the prodution ...
1
vote
0answers
61 views

Oracle - Mysterious indexes

I have a table in Oracle 10.2g that I'm trying to import roughly 11 million rows into. The table contains an SDO_GEOMETRY column. It previously had NOT NULL constraints on all 3 columns, a primary key ...
1
vote
0answers
54 views

How to make continues cluster?

I have a report table with the following index: providerid, date The table is around 30M records and it grows about 100K rows per day. I want to use in the index above as a cluster, but as I ...
1
vote
0answers
36 views

parenthetic grouping of join clauses

What is the difference between these joins? a left join b left join c (a left join b) left join c a left join (b left join c) Does the grouping only affect the order of the joins? Will the query ...
1
vote
0answers
37 views

How does MySQL 5.1 use indexes?

I've read the docs for MySQL v5.1.x on how about the indexes are used, but I'm not sure I understood it properly. Suppose having the following MySQL table: CREATE TABLE IF NOT EXISTS ...
0
votes
0answers
22 views

PostgreSQL indexing table on multiple columns

I have a table that holds half hour metering values in a compact form on a daily basis. The table has a structure as described below: day: timestamp without time zone, // describes a day ...
0
votes
0answers
13 views

Optimal Table Index for hourly data

I have a table that contains ~40,000,000 records and consisting of the following fields: idName - foreign key relationship to another table Date Hour Type (two possible types) Value Currently I ...
0
votes
0answers
32 views

Unique Constraint exception after dropping constraint

We had dropped a unique constraint using the ALTER TABLE DROP CONSTRAINT script, but it is still throwing the Unique Constraint exception while trying to insert. We drilled this down to two possible ...
0
votes
0answers
21 views

Mongodb compound index on two fields where one field is sparse

I have a collection of documents that give stats relating to videos. I have a unique compound index on videoId, date (there is at most one document per video per day). I want to be able to; in a ...
0
votes
0answers
31 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
0answers
25 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
0answers
49 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
0answers
42 views

Measure fragmentation of a single index?

We are measuring the impact of different defrag approaches that we do one index at a time. How do we query to see fragmentation of a single index, by index name (or table + index). This is on SQL ...
0
votes
0answers
29 views

Does MySQL have a visual tool for suggesting indexes like SQL Server's “Include Actual Execution Plan”?

SQL Server has a remarkably useful feature called "Include Actual Execution Plan" which not only gives you a visual diagram of your query, but also automatically suggests indexes that can improve ...