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
0answers
229 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
1answer
2k views

Find fulltext indexes in SQL Server 2008

How do I find a list of tables that have a FULLTEXT INDEX applied to them on a given database? For example, you can enable and disable a FULLTEXT INDEX like this: ALTER FULLTEXT INDEX ON ...
3
votes
1answer
1k 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    - ...
6
votes
1answer
137 views

Ok to drop indexes on FK's if they have no stats in the DMVS

I have used the well known query below from Kevin Kline to check for unused indexes. Several indexes created on Foreign keys returns no read stats, only writes. Are you 100% safe to drop these ...
4
votes
3answers
611 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 ...
2
votes
1answer
63 views

How to efficiently hit an index in a query that uses IN (mysql)

I have a table like this where people can subscribe to certain feeds and the query fetches from the subscription. Here is a sample table: ...
3
votes
1answer
820 views

Query not using index when it should

I have this query (below) which keeps hitting the table when it should be using the created index. Is there something simple I'm missing here? How can I get it to use the index instead of the table? ...
3
votes
2answers
154 views

Proper Index for WHERE X <> 1 AND Y <> 1 ORDER BY Z

I have a relatively large table that is accessed in exactly one way, by exactly one query (on the read side). The query filters the table by two columns (both bit), and sorts by a third column (an ...
2
votes
2answers
292 views

MySQL index on column comparison

How do you go about setting an index to be used for a column comparison like WHERE col1 > col2? col1 and col2 are of DATETIME type.
2
votes
2answers
109 views

Optimizing index creation

One thing that has bothered me for awhile is creating many many indexes for the one table where the query only differs slightly or contains many of the same columns. An example case would be: table1 ...
0
votes
1answer
124 views

clustered index - data warehouse

My source data comes from multiple OLTP databases where the primary key is an identitiy column. We anticipate adding data centers and/or splitting the databases by product. Currently i have one system ...
-1
votes
1answer
157 views

Covering Index isn't being used and I don't know why

We have this complicated query that I'm trying to make "better" until we move it to pull from a data warehouse. I need a solution that's "good enough" for now and I think I'm about 2-3 indexes away ...
2
votes
1answer
406 views

Non-clustered Index Update not displayed in execution plan [duplicate]

Possible Duplicate: Nonclustered Index Insert I'm trying to work out the cost of having a nonclustered index on a particular table, but I don't see any maintenance cost in the execution ...
1
vote
1answer
247 views

Simplify and optimize a complex query

Our site has a chronological event feed that uses MySQL. We have noticed that some of the queries tend to run for quite a bit longer than others. For example, some queries for our heaviest users take ...
4
votes
1answer
3k views

Should I rebuild index after truncate/large insert?

I have a stored procedure that truncates some tables with around 1.75M rows in each, before inserting new data (based on data in other tables, calculations etc.) Basic outline is very simple: ...
2
votes
3answers
287 views

mariadb ignoring force index

I'm trying to tune a relatively simple query: select bt.col1,bt.col2 from bigtable bt join smalltable st on bt.smalltable_id = st.smalltable_id where st.name = 'some name occuring only once in st' ...
2
votes
2answers
2k views

How do I force a JOIN to use a specific index in MySQL?

I have a query that JOINs 2 tables; lineitem and part, select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem force index for join (l_pk), part where ( ...
3
votes
2answers
140 views

Why is this key lookup required?

I have an admittedly-complex query that I'm optimizing and I'm stuck on the next step. I have pinpointed the bulk of my problem to one part of my query that's doing the following Key Lookup but I'm ...
2
votes
1answer
5k views

After truncating a single partition its Primary key's index becomes unusable and all inserts/updates into that partition fail

I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001...201212.. and so on). This table has several local ...
0
votes
1answer
186 views

What structure is used for storing CONTEXT, CTXCAT and CTXRULE Oracle text indexes?

AFAIK there are three major structure types used for storing Oracle index data: B-tree, R-tree and Bitmap. All indexes use one of these structures. However, not sure about CONTEXT, CTXCAT and CTXRULE ...
1
vote
2answers
178 views

What are the alternatives for prefixed indexes in Oracle?

You can create an index and define prefix length, so the index will store only first starting symbols from each of column value. It looks like this in MySQL: CREATE INDEX table_idx ON ...
0
votes
1answer
1k views

When hash indexes should be used in Oracle?

Hash indexes are usefull when you have a large table with URLs and you need to query by them. So, a solution would be to have an additional column "url_crc32" that will be filled with hashed URL value ...
1
vote
3answers
384 views

Usefulness of a multi-attribute index

If an index has more than one attribute in it, is there any speed gained in a select statement whose where clause uses one of the attributes in the index? For example, take a table T with an index on ...
3
votes
1answer
99 views

Large inserts breaking indexed queries

Very similar to Large insert/update in table causes timeout while SQL Server re-arrange indexes only I am not using SSIS with only occassional large (predominently annual) inserts from another staging ...
1
vote
2answers
304 views

Mongodb multikey index and $all in queries

I have such structure of documents in my products collection: { title: 'Samsung Galaxy S III', category_id: ObjectId("50bcc2f0b910a6c1936a4424"), properties: [ { title: 'OS', ...
1
vote
1answer
1k views

SQL Server Full-Text Indexer with stoplists/stopwords

As I'm working on a graduate project (Textmining with SQL Server 2012 Semantic Search) I run into a situation where I need to post a question on this website, hoping someone can help me. This ...
8
votes
1answer
1k views

SQL Server Index vs Statistic

What are the differences between CREATE INDEX and CREATE STATISTICS and when should I use each?
6
votes
1answer
4k views

MySQL INNODB corruption after server crash during concurrent truncate command

My server crashed today I think due to a concurrent truncate table command on one of our INNODB tables. The server could be restarted, but after it starts up, everytime I try to issue an SQL command, ...
5
votes
1answer
217 views

Walking a BTREE index as far as possible in MySQL

Suppose one has a column of words on which one builds a BTREE index: CREATE TABLE myTable ( words VARCHAR(25), INDEX USING BTREE (words) ); LOAD DATA LOCAL INFILE '/usr/share/dict/words' INTO ...
4
votes
2answers
148 views

We are running database maintenace with indexing the system, indexing fails due to locking?

We have been trying to do re-indexing in our system, there are users still using the database during the indexing. While doing index for certain tables there is failure of indexing this seems to be ...
2
votes
1answer
2k views

MySQL Explain: Using index, using temporary, using filesort. Can this query be improved?

I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table. In a report I'm writing, an object can be selected to view statistics against. The ...
1
vote
1answer
644 views

InnoDB, What would cause: “db1/tableABC contains 6 indexes inside InnoDB, which is different from the number of indexes 5 defined in MySQL”

MySQL 5.5.24 . We have InnoDB tables that grew almost by double (from 50GB to 100GB - file-per-table enabled) in a few weeks due to a project we are working on. The tables were freshly dumped and ...
3
votes
4answers
3k views

Slow query on large table with GROUP BY and ORDER BY

I have a table with 7.2 million tuples which looks like this: table public.methods column | type | attributes ...
2
votes
2answers
282 views

Can I use a foreign key index as a shortcut to getting a row count in an INNODB table?

I have a table that has a large number of rows in it. The primary key (an auto-incrementing integer) is, by default, indexed. While waiting for a row count to be returned I did an EXPLAIN in another ...
3
votes
1answer
191 views

How to update views outside of update/insert transaction in SQL Server

Looking at SQL Server indexed views I see that these views are updated within a transaction that updates dependent tables. I wonder if there is a way around it? There will be about 10 views with 20 ...
0
votes
1answer
4k views

fundamental difference between Primary/Clustered and Secondary/Non-Clustered index

Are Primary Index and Clustered Index similar in concept Secondary Index and Non-Clustered Index similar in concept
4
votes
1answer
1k views

Is a clustered index locked while a (clustered index) scan is in progress?

The question is pretty simple, as the title says. However, I cannot find any references to confirm or contradict this. The problem leading me to this question is that I have a view joining several ...
1
vote
1answer
1k views

Use of Shrink after rebuilding Indexes in sql server 2005

I need to create a weekly database plan for my company's databases (5.8Gb), now I want to rebuild the Indexes, and then want to trigger a Shrink operation. Now I know Shrink creates Index ...
2
votes
2answers
216 views

Defrag a HEAP by creating clustered index and immediately dropping it

I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
2
votes
1answer
254 views

Why 2 allocation units during online index rebuild to different file group?

I'm in the process of rebuilding the clustered index of a big table to a different file group. I'm monitoring the rebuild process using sys.system_internals_allocation_units. I'm 3h in but not done ...
3
votes
1answer
1k views

Move Primary Key to Filegroup (SQL Server 2012)

How can I move a clustered primary key to a new filegroup? I already have found a possible "algorithm" but it is horribly inefficient: Drop non-clustered indexed (requires them to be resorted and ...
2
votes
1answer
192 views

clustered and covering index ignored on delete statement. Table scan occurs

Why would SQL Server 2005 find it more efficient to perform a table scan instead of using the available clustered index on the primary key (and only the primary key)? DISCLAIMER: There is also a ...
4
votes
2answers
361 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
2answers
2k views

Postgresql 9 speeding up indexed inserts (JPA)

I have an application which generates a lot of data which needs to be inserted quickly (something around 13million records). I use JPA 2.0/Hibernate with Postgres 9.1, and I managed to achieve quite a ...
5
votes
3answers
1k views

Query performance degrades with time and use

I have been fighting a problem for several weeks now where the performance of my SQL Server queries degrade over a few days of use. In addition every few days, a query will simply not return from my ...
2
votes
1answer
3k views

How does postgres use the schema search path when making indexes?

Using Postgres 9.1.4 with PostGIS 1.5, as part of a Rails 3.2.x app. I have a DB containing schemas A and B, with schema A empty and schema B containing tables foo and bar, with indexes on each table ...
9
votes
2answers
1k views

Must an index cover all selected columns for it to be used for ORDER BY?

Over at SO, someone recently asked Why isn't ORDER BY using the index? The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, ...
0
votes
1answer
63 views

Is it any potential problem with having 2 constraints suppored by one index in Oracle?

I have the following table : CREATE TABLE action (action_id INT NOT NULL, action_type_id INT NOT NULL, action_date DATE NOT NULL); CREATE INDEX IDX_ACTION_ACT_TYPE_ACT ON action(action_id, ...
3
votes
2answers
73 views

Looking for a possible multi-column index

I have a table highscore containing the columns: game (text) date (timestamp) score (integer) more irrelevant ones... The query most often run on it is: SELECT * FROM highscore WHERE game = ...
2
votes
1answer
847 views

Is a query with UNIX_TIMESTAMP using indexes in mysql?

If I use .. WHERE UNIX_TIMESTAMP(`some_timestamp`)>NOW() in a SELECT, does this query still make use on an index, set on some_timestamp ?