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)

3
votes
0answers
83 views
+50

How to setup complex multi-column index for massive table

I've searched what I could search on Google, SO, etc and haven't found an answer that would seem to fit what I'm looking for. I'm hesitant to make a post about this as I'm sure the answer is out there ...
3
votes
0answers
99 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
84 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
157 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
124 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
141 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
60 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
118 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
80 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
89 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
274 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
63 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
127 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
33 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
199 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
24 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
38 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
20 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
38 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
47 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
85 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
247 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
38 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
65 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
52 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
52 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
119 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
2k 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
30 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
159 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
60 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
52 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
215 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 ...
1
vote
0answers
36 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
15 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 ...
0
votes
0answers
23 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) ...
0
votes
0answers
42 views

Index intersection issue with Mongo 2.6.4

I upgraded my system with 2.6.4 binaries to use specifically index intersection. But when i tried this my DB got down. I created these indexes in my collection "a_1_b_1" : 1.2 GB, "c_-1" : 500 MB ...
0
votes
0answers
12 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 ...
0
votes
0answers
38 views

Index on fields with a function

I have a log table. It logs visitor data (time of visit, browser, window size ...). I want to create reports. I use a query like this (MySQL): SELECT COUNT(*) as raw_views ... FROM logs WHERE ...
0
votes
0answers
32 views

Mysql table with only two columns, nothing unique: Will an index increase search performance?

If my table only has two columns (username, groupname) and a million or so rows, will an index improve performance at all? Wouldn't it take just as long to search the index file as it would to search ...
0
votes
0answers
74 views

How to know the correct indexes for mysql?

I tried to find out with explain where I must set the correct index. A current select takes about 20 seconds. Can someone tell me which would be the correct index for my tables? SELECT trade.*, ...
0
votes
0answers
21 views

Creating a table for indexing another table

We have a table with 350m rows and 12 columns, and around 1m rows adding everyday. We have crappy hardware and we cannot add an index on one of the 12 columns that we really really need to. (severe ...
0
votes
0answers
50 views

Strange behavior with context index on matarialized view

We are experiencing a very strange behavior with a materialized view on oracle. Here is the statement that we've used to create the materialized view: CREATE MATERIALIZED VIEW MY_MATERIALIZED_VIEW ...
0
votes
0answers
15 views

Index table for horizontal shard

Lets assume we have a vary amount of clients, which could manage some stuff on our site. Each client has his own table, which is used to store data. For instance: client_1, client_2, ..., client_N. ...
0
votes
0answers
39 views

MySql - multi-column index using only null values from one column

I have a Innodb table with a index that looks at 3 columns. ColA is integer and always has a value. ColB is varchar and always has a value. ColC is datetime and sometimes has a value (NULL). My ...
0
votes
0answers
146 views

index usages on table with recursive relationship

we are working on a project that uses jpa and oracle. In this project we have a table with a recursive relation: Create table alarm ( id varchar2(200), ... alarmcode varchar2(50), parent_id ...
0
votes
0answers
27 views

SQL Server Rebuild and Fragmentation

After running a SQL rebuild the indexes are still fragmented - some over 50%. What else can we do to reduce the fragmentation? Do we need to run the rebuild multiple times? Thanks for the help
0
votes
0answers
20 views

Questions about linear hashing

I've a little confused about linear hashing index mechanism, does anyone can explain that in the algorithm format. And does the sequence of different insertions into that hash table matters?
0
votes
0answers
133 views

Based on these DBCC SHOWCONTIG stats, should I index this table?

I have a SQL 2008 Standard server on full mirror (safety). I reorganize all indexes weekly. I am looking into rebuilding a few indexes to improve performance. I ran DBCC SHOWCONTIG on my largest, most ...