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.
2
votes
2answers
92 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
98 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
85 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
154 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
138 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 ...
3
votes
1answer
719 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
102 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'
...
1
vote
2answers
177 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
101 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
924 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
78 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
122 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
398 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
172 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
58 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
171 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
348 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 ...
7
votes
1answer
203 views
SQL Server Index vs Statistic
What are the differences between CREATE INDEX and CREATE STATISTICS and when should I use each?
5
votes
1answer
720 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
140 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 ...
-2
votes
1answer
62 views
Best indexing for users table [closed]
This scenario occurs so many times (for almost every website) that I thought I would ask here:
What would be best indexing for a users table:
Edit: I think index on email-addresses and username may ...
4
votes
2answers
90 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
708 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
131 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 ...
2
votes
3answers
472 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
118 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
103 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
1k 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
3
votes
1answer
379 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
291 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
160 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
167 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
238 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
132 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
145 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
525 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
305 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
582 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 ...
7
votes
2answers
233 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
54 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
62 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 = ...
1
vote
1answer
166 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 ?
2
votes
1answer
174 views
Copy Postgres databases with indexes between instances
How do you copy a single Postgres database between installations without using pg_dump and without regenerating indexes?
We have an application we deploy which uses Postgres 8.4.7 database in Linux. ...
5
votes
3answers
167 views
Database Indexing - Maintenance Jobs
I have created a script that runs every night to rebuild & re-organize indexes based on the fragmentation, Indexes with Fragmentation > 30% are rebuilt, Indexes with Fragmentation 10% - 30% are ...
1
vote
1answer
86 views
Simple Slow Query Not Using Indexes
I have a slow query running on a large forum. The EXPLAIN synopsis:
mysql> EXPLAIN
SELECT
COUNT(post_thanks.id) as thanked,
post_thanks.postid,
post.title,
post.dateline
FROM ...
3
votes
2answers
347 views
Do I risk losing the benefits of indexing if I have an index on every column?
I use PostgreSQL 9.2. I have a table with ~5 million rows and 150 columns. The table does not change at all (I replace it once a year). Users query this table with all kinds of filters on any some of ...
1
vote
1answer
66 views
Partitions and Indexes, in which cases should be used
I know some knowledges:
Partitions used to achieve better performance (like described in this article)
And indexes used to increase performance in selection operations.
And as result my question: ...
2
votes
1answer
225 views
Index with multiple leaf levels
I have a three-column (int, smallint, smallint) composite clustered index with three leaf levels. My question is how and when does SQL Server create multiple leaf levels (index_level 0) for the same ...
7
votes
1answer
884 views
PostgreSQL Index optimization with dates
I have a large table of objects (15M rows +), for which I want to query for outdated field. PostgreSQL (9.0.8)
I want to divide the query by millions, for scalability & concurrency purposes, and ...
5
votes
1answer
228 views
Unexpected Seq Scan when doing query against boolean with value NULL
I have a database column called auto_review where column type is boolean.
There is an index for that field, created using the ActiveRecord ORM.
CREATE INDEX index_table_on_auto_renew ON table USING ...
