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)

0
votes
1answer
62 views

Is index like key(timetime) usable when we query: SELECT * … ORDER BY timetime DESC

When we index a timestamp field like timetime. Can we use it for ORDER BY timetime DESC or is it just usable for ORDER BY timetime ASC OR ORDER BY timetime? Does mySQL use this index when doing vise ...
5
votes
1answer
1k views

How does SQL Server choose an index key for a foreign key reference?

I am working with a legacy database that was imported from MS Access. There are about twenty tables with non-clustered, unique primary keys that were created during the MS Access > SQL Server upgrade. ...
4
votes
2answers
8k views

SQL Server 2008R2 - Why is my index not used

I have a table defined in the following way: CREATE TABLE [dbo].[MyTable] ( [MyTable_ID] [int] IDENTITY(1,1) NOT NULL, [COLUMN_WITH_DATA] [varchar](128) NOT NULL, [COLUMN_A] [varchar](128) ...
0
votes
2answers
109 views

SQL and Multiple Indexes

Consider the following table: i | a | b | c -------------- 0 | 1 | 2 | 3 1 | 4 | 5 | 6 2 | 7 | 8 | 9 3 | x | y | z The table, in the end, will have several thousand rows, and even more columns then ...
4
votes
4answers
10k views

Benefits of BTREE in MySQL

What are the pros and cons of using a BTREE index in MySQL, regarding query speed, disk storage and memory usage? Does BTREE provide easier iteration in increasing order ? What kind of queries would ...
2
votes
1answer
4k views

How can I tell if an index is being used to sort in MySQL?

I have a query with an ORDER BY clause which uses a column which is the last column on an index which is being used in the WHERE clause, essentially of the form: SELECT cols FROM tables WHERE ...
5
votes
1answer
770 views

Index defrag takes forever to run

We have an Agent job that runs every morning at 2a.m. to defrag the top 5 biggest tables. Usually it takes around 20 mins to complete. It has stayed this way for several years. However, recently (last ...
6
votes
2answers
2k views

Should a table have a clustered index even if it doesn't have appropriate fields for it?

If you have a table without a good candidate field or fields for a clustered index (stable, sequential), is it better to have a clustered index on a bad field or is it better to make all table indices ...
3
votes
1answer
277 views

Adding new key(s) to enable partitioning, INSERT performance?

I have a table to storage GPS coordinates with multiple (~30) inserts per second (from 50 different devices) during 12 hours per day, so the table is growing fast, to improve the SELECT speed I was ...
3
votes
2answers
4k views

Postgres primary key sorted in the reverse order. Will it use the index efficiently?

My Rails database is backed by Postgres database. As you may know, each table in Rails gets assigned a primary key which is of Integer type and is indexed. My list views show records in the reverse ...
8
votes
2answers
2k views

Slow ORDER BY with LIMIT

I have this query: SELECT * FROM location WHERE to_tsvector('simple',unaccent2("city")) @@ to_tsquery('simple',unaccent2('wroclaw')) order by displaycount I'm happy with it: "Sort (cost=...
0
votes
2answers
1k views

How to move all non-unique and non-clustered indexes to a different filegroup?

I want to move all indexes to a separate hard drive. To that effect, I created a new filegroup, then added a new file to it (e.g. alter database foo add file(...) to FileGroup). Now I want to move ...
0
votes
2answers
5k views

How can primary keys in a Netezza table be indexed?

What is the best way to index a Netezza fact table with lots of primary keys. For now, let's suppose we have 10 primary keys. This table is mainly used to store meta data about our database. =====...
4
votes
1answer
2k views

creating an index for varchar column greater than 900 bytes

I have a var char column in SQL Server 2008 which is usually less than 100 bytes but in some cases can be huge up 5607 bytes. I need to index it, and I can't because the max index size in SQLServer is ...
1
vote
1answer
120 views

How can I reset the index of the rows?

I would like to select and insert data from an old table to the new table: CREATE TABLE test2 LIKE test1 INSERT INTO test2 SELECT timestamp, datenum FROM test1 WHERE datenum>=23 and datenum<=...
2
votes
1answer
2k views

postgres composite index design

In my rails application using the Postgres database, I have a table called tw_schedules which belongs to a scenarios table (one scenario to many tw_schedules). In addition to the scenario_id foreign ...
9
votes
2answers
2k views

How to prevent daily index fragmentation of 99%

I have a highscore table for 100.000 players that is being inserted into 2 times a day with one record per player. At the end of the day the index fragmentation for the indexes in that table is 99%. ...
7
votes
1answer
903 views

computed columns, index, clustered index and covering index?

We all know the we can only have one clustered view per table, cool. But apparently you don't have values of computed columns on leaf even when using a clustered index and they are computed each time. ...
4
votes
1answer
144 views

Are two indexes needed?

Our MySQL database will contain an encyclopedia. The encyclopedia will be shown in pages where every page contains entries starting from a letter. Which indexes should I use? Should I have two ...
1
vote
1answer
141 views

SQl code performance

What measurement do you know if this sql code is efficient based on execution plans? How do you know if this sql code is efficient based on execution plans? SELECT [CustomerID], [...
4
votes
1answer
5k views

Oracle 11: Change global index to local on huge production table

I have big partitioned table with global index. My case is drop old data from archived partitions. I can't simply drop partition because i have global index. So I have idea to use INVISIBLE local ...
3
votes
2answers
628 views

What exactly is “pinning” in relation to indexes?

When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions. It is part of a test question, where ...
3
votes
1answer
153 views

Should I use an index or a hash function on a primary key if I have 100000 rows of data?

I have a table named PRODUCT that has 100,000 rows. I was wondering if either indexing, or a hash function on the primary key would result in better performance. I was also hoping someone could ...
2
votes
1answer
4k views

SQL Server Rebuild/Reorganize Fragmented Indexes causes CRC error or consistency errors

In our test lab I've been experimenting with different jobs to keep our critical indexes from becoming too fragmented. I'm currently using the approach described here: sys.dm_db_index_physical_stats ...
14
votes
2answers
16k views

Why index REBUILD does not reduce index fragmentatation?

I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It ...
4
votes
2answers
245 views

Does a dual key or single key lookup perform better on a table with a lot of data?

We have table where rows unique between 3 columns. GroupA, GroupB, ProductID. GroupA & GroupB are used in combinations to Group sets of ProductIDs and will always be used to lookup sets of ...
15
votes
3answers
2k views

Can spatial index help a “range - order by - limit” query

Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes. We have the following table with a tree structure (Nested Set model) of words and their frequencies:...
6
votes
1answer
335 views

Performance of query with a range condition and order by

We have the following table (in SQLite on Android) which a tree structure (Nested Set model) of words and their frequencies: lexikon ------- _id integer PRIMARY KEY word text frequency integer ...
2
votes
1answer
890 views

How to store GPS coordinate and search places in a radius from a NoSQL DBMS (like DynamoDB)

My team need a DBMS like DynamoDB to store large amount of data, principally places and coordinates. I've considered to use some GIS-based DBMS (like PostGIS) with an index on the POINT, but DynamoDB ...
1
vote
5answers
3k views

How should I save apache logs into a mysql table?

This is a php script to view apache logs. I want to save apache logs into a mysql database. Then add some rules for tagging urls using mysql REGEXP search, like: SET tag='some tag' WHERE url REGEXP '...
7
votes
3answers
1k views

Creating an index in both asc and desc directions

Over the past few weeks I've been raging against an old Firebird database. This database is crappy for all sorts of reasons, but one thing I noticed was that every single field of every single table ...
2
votes
1answer
1k views

Creating Bitmap Indexes on a Materialized View

I've been reading this article, discussing the benefits, tradeoffs, and potential problems with Bitmapped Indexes. It is mentioned: Also, remember that bitmap indexes are only suitable for static ...
3
votes
1answer
945 views

Will an index that is a partial key of the begining of another index perform any better than the longer index?

If I create an index that is a partial key of the begining of a second index, will SQL Server get any significant gains retreiving the results from the simpler index, when the search criteria matches ...
2
votes
2answers
74 views

Are these extra Index fields needed?

I've designed a table as below for users who register a quiz: CREATE TABLE `registered_quiz` ( `rq_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `user_id` smallint(5) unsigned NOT NULL, `...
2
votes
0answers
37 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
1answer
833 views

Disabling Indexes and Auditing for large data import

Hopefully the title describes this sufficiently, but I have a process that runs and performs thousands of individual inserts and updates (nothing I can do to change that). My questions are: 1. Is it ...
2
votes
1answer
1k views

Is Index causing timeouts?

Recently, while fine tuning the db for better perf, I changed a couple of indexes on tables which were hit very often. I am using the following query to keep tab on index stats SELECT DISTINCT ...
1
vote
1answer
2k views

Index Strategies on Text or NVARCHAR(MAX) Fields

I have the following query (simplified for question) I'm trying to speed up for a read only DB ... SELECT [sysid] ,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101) ,[From]=[from_addr] ...
7
votes
5answers
7k views

Improve sort performance in PostgreSQL?

I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the ...
0
votes
1answer
172 views

Creating Indexes to optimize query

I have this query and I want to increase its performance by adding appropriate Indexes. DELETE FROM MYTAB1 WHERE MYID1 IN ( SELECT MYID2 FROM MYTAB2 ); I am not familiar with the ...
1
vote
1answer
2k views

Is it possible to add index to mysql with multiple column to optimize order by performance?

# I have a simple table create table t1 (c1 int, c2 int); # I want to do the following sorting select * from t1 order by c2 desc, c1 desc; select * from t1 order by c2 asc, c1 desc; # add ...
4
votes
2answers
4k views

Improving performance of nasty nested-view joins

I have a moderate-sized database spread out over a few tables, the rough architecture is: Input Data (Data ID, Session ID and a few fields of statistical importance) Input File (Data ID and a blob) ...
7
votes
2answers
193 views

In SQL Server, is it possible to have a PRIMARY KEY on a table without either CLUSTERED or NONCLUSTERED indexes on the same key?

In Sql Server (2008), is it possible to have a PRIMARY KEY on a set of columns without either CLUSTERED or NONCLUSTERED indexes on the same set of columns? I am aware of the fact that PRIMARY KEY and ...
2
votes
1answer
504 views

Greater than operator ignoring a nonclustered index?

I have a pretty simple query in SQL 2008 R2 and I have a nonclustered index on the "sDate" datetime column. When I look at the execution plan for the following query it seeks the nonclustered index. ...
13
votes
1answer
5k views

When should you specify PAD_INDEX?

So, you can use FILLFACTOR to leave space in leaf index pages. Specifying PAD_INDEX also leaves space in intermediate nodes. What scenarios should you specify PAD_INDEX, and what benefit does it ...
1
vote
1answer
423 views

Analyzing TCB Index Status for DB2

The Following is the TCB index that I could get using a command for my DB2 database. I don't know how to analyze it. Which columns are important? What do they mean? TCB Index Stats: Address ...
7
votes
2answers
4k views

Index on primary key not used in simple join

I have the following table and index definitions: CREATE TABLE munkalap ( munkalap_id serial PRIMARY KEY, ... ); CREATE TABLE munkalap_lepes ( munkalap_lepes_id serial PRIMARY KEY, ...
0
votes
1answer
167 views

The sort column must be the last column used in the index - MongoDB Indexing Advice

MongoDB docs advices that: The sort column must be the last column used in the index Here is the example: db.foo.ensureIndex({a: 1, b: 1, c: 1}) Good: find(a=1).sort(a) find(a=1).sort(b) ...
2
votes
2answers
137 views

Why isn't the following query using the `always` index?

This morning I started receiving load warnings on one of the MySQL servers I manage. SHOW FULL PROCESSLIST revealed that the culprit was the following (poorly written) query: SELECT * FROM movies, ...
1
vote
1answer
4k views

Repair with keycache in mysql runs EVERY time. Can't even log why it's happening

I have a MyISAM table w/ ~1million rows and two indexes - one's a three-column key, and the other is a spatial index for bounded SELECT statements on a separate point column. My insert statements ...