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)

2
votes
1answer
48 views

SQL Server : Index Seek with very high Scan Count

I have a query that joins 2 tables: Documents table, that has DocID as Unique Clustered Index DocumentsRows table, that has DocID, RowID as Unique Clustered Index When I join those 2 tables, I ...
3
votes
0answers
45 views

Timestamp-based index

I have a big database (200GB+) that contains some log info. And I want to speed up SELECT queries and stored procedures. I have a table with a GeneratedOnUtc datetime column, and have a non-clustered ...
8
votes
2answers
244 views

Minimizing Indexed Reads with Complex Criteria

I'm optimizing a Firebird 2.5 database of work tickets. They're stored in a table declared as such: CREATE TABLE TICKETS ( TICKET_ID id PRIMARY KEY, JOB_ID id, ACTION_ID id, STATUS str256 ...
1
vote
1answer
48 views

Updating large table (~6.5 million rows) & indexes

I'm trying to update a table with about 6.5 million rows in a reasonable amount of time, but I'm having some issues. We're adding a new column to an existing table and then setting a value for all ...
0
votes
0answers
7 views

Will a covering index save a table hit if only the commoly-used length of a `text` field is indexed?

I would like to add a covering index for a table that contains a text field. The maximum size of the field that is in use is ~2000 ASCII characters, 95% is below 800 characters. I am considering ...
2
votes
1answer
47 views

Unused index in range of dates query (PostgreSQL, timestamp with time zone)

I think maybe I am making a dumb mistake here, so apologies in advance. But I have a query that is not using existing indices and I do not understand why. Am I reading the plan wrong? Are the indices ...
3
votes
2answers
147 views

Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

I have a poorly performing query: SELECT user_id, count(item_id) as count FROM table items WHERE category = 'magazine' AND created_at > 1384754400 GROUP BY user_id ORDER BY count(item_id) desc ...
0
votes
1answer
18 views

Reusing covering index for fields in FROM, from another query's index

For the following query I have a covering index as so: SELECT bar_id FROM foo WHERE user_id=:user_id AND date_sent=:date_sent; ALTER TABLE foo ADD INDEX (user_id, date_sent, bar_id); Now I have ...
1
vote
1answer
68 views
+50

Clustered index performance consideration in MySQL & PostgreSQL

In MySQL/InnoDB, the clustered index is synonymous with the primary key, so picking up a poor primary key affect your db performance, i.e. use of UUID as PK is a performance killer for database write. ...
25
votes
5answers
8k views

Working of indexes in PostgreSQL

I have a couple of questions regarding working of indexes in PostgreSQL. I have a Friends table with the following index: Friends ( user_id1 ,user_id2) user_id1 and user_id2 are foreign keys to ...
0
votes
1answer
174 views

MySQL is using wrong index

This is my table: - user_id INT (foreign key on `user` with the reference `id`) - question_id INT (foreign key on `question` with the reference `id`) - option_id INT (foreign key on `option` with the ...
2
votes
1answer
4k views

MySQL requires FORCE INDEX on huge table and simple SELECTs

We have an application which stores articles from different sources in a MySQL table and allows users to retrieve those articles ordered by date. Articles are always filtered by source, so for client ...
12
votes
1answer
20k views

How to use psql with no password prompt?

I wrote a script to REINDEX indexes in a database. Here is one of them: echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE} psql -U ${USERNAME} -h ${HOSTNAME} ...
19
votes
4answers
17k views

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

I had to write a simple query where I go looking for people's name that start with a B or a D : SELECT s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering ...
1
vote
1answer
132 views

Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - ...
-1
votes
0answers
44 views

Surrogate column as part of composite key [closed]

Let's say we have TABLE with columns (A, B, C, D, E, K). K is the key of (A, B, C). Both (A, B, C, D) and (K, D) are unique keys of TABLE. I need to join two TABLEs like this (we need to return A, ...
2
votes
2answers
72 views

How should I (re) index the table while keeping the transaction log small?

I have a 170 GB table in a database. It has 70% fragmentation on its index (index has multiple columns in it). The last time I tried DBCC REINDEX the transaction log blew up in the hundreds of GBs. ...
0
votes
2answers
178 views

mysql need help optimizing query

I have a table containing servers logs, and another table containing rules to match certain and drop them. The logs tables contains around 1 million rows, and there are about 20-30 rules. The ...
0
votes
1answer
192 views

Is searching an alternate index on a partitioned myISAM table concurrent or serial?

I have a myISAM table with 30 partitions. The primary key is a SHA1 hash value, which is also used in the partition selection function. I also have an alternate key - an autoincrement integer. Since ...
3
votes
1answer
174 views

Why do nonclustered Indexes use clustered indexes instead of the table data pages directly?

From a Microsoft Course (presented by a third party) I learned that when a table has a clustered index even non-clustered indexes hit the clustered index. When there is not a clustered index (Heap) ...
1
vote
0answers
47 views

Is >58 hours to create an index normal? Should I cancel and try again?

I have an InnoDB table with ~43,964,557 rows containing 20.5 GiB of data (not counting existing indexes). mysql> show table status like 'backpack_items'; ...
0
votes
2answers
33 views

Will reindexing use the empty space or just grow table?

I have a table which is 170 GB and it has 98 GB of free/empty space. The free space was created by a massive deletion. If I run DBCC REINDEX or ALTER INDEX REBUILD, will the index use that free space ...
2
votes
1answer
45 views

Retrieving latest record using DISTINCT ON is slow [duplicate]

Using Postgres 9.4. I have a table cartests with 5.5M rows. Each row is a car test: \d log.cartests; Table "log.cartests" Column | Type | Modifiers ...
0
votes
1answer
30 views

SQL Server Table Index To big

I have a table that has approximately 450k records here is the structure of it CREATE TABLE [dbo].[Reviews]( [ReviewID] [int] IDENTITY(1,1) NOT NULL, [AuthorName] [nvarchar](50) NULL, [AuthorURL] ...
6
votes
1answer
460 views

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is: 922475bb-ad93-43ee-9487-d2671b886479 There ...
1
vote
1answer
676 views

Slow insert with MySQL full-text index

I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right? ...
0
votes
2answers
35 views

What indexes should one use to optimize a PostgreSQL query with a JOIN depth of 2?

Disclaimer: I am relatively new to PostgreSQL. I'm wondering how to optimize a query that does 2 INNER JOINs. My scenario is fairly simple: Select Posts with a photo (Posts.photo IS NOT NULL) and a ...
0
votes
1answer
28 views

MySQL performance of applying unique constraint single column vs combination column

I have a large MySQL InnoDB table containing around 137 million rows. I need to add a unique constraint to the table. We have two options: 1) A combination index on three columns, one of which will ...
0
votes
3answers
376 views

Mysql - How to Optimize Retrieval Time in a Table

I have query like this! which has 200 million Records in a single table.. I am using BTree Indexes in my table... mysql> select COUNT(DISTINCT id) from [tablename] where [columname] >=3; ...
1
vote
1answer
227 views

How to repair all indexes in a MySQL database?

My database got corrupted, and now the php app is throwing this error: Invalid query: Can't find FULLTEXT index matching the column list The error is not very useful, since it doesn't say what ...
2
votes
0answers
29 views

ola hallengren Index Maintenance script - GUID Index

We been running ola hallengren Index Maintenance script and was ran into a problem with some indexes that have GUID as the Index Key, They are Frag and have a page count of 6,309, but they don’t get ...
0
votes
1answer
81 views

PostgreSQL SELECTs not returning correct result following recovery?

I took a copy of the current database from a live slave using pg_basebackup and recovered it to a brand new isolated computer following the instructions in the docs. Once the new database had started ...
3
votes
1answer
306 views

Index for finding combinations of attributes in JSON array

Based on this related question on SO. This works fine if you only want simple matches. Suppose tracks have both Artist and Title field in the JSON data. So we have something like: INSERT INTO tracks ...
1
vote
2answers
50 views

Creating index on large MySQL table taking forever

My table has 981 million rows and is about 30GB large. It is six columns: ID, Ticker, Date, Time, Price, Volume. The data was in text files and using LOAD DATA INFILE was actually quite painless and ...
1
vote
1answer
27 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. ...
2
votes
1answer
468 views

Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?

I've been archiving some MySQL (version 5.0.96) MyISAM tables locally and deleting from my server after checking the data. Every time the process has been pretty similar and comes off mostly without a ...
1
vote
1answer
61 views

Should database be denormalized if I need an index across multiple tables

I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like: SELECT * FROM song s JOIN album a ON a.id = s.album_id WHERE LOWER(CONCAT_WS(' ', ...
0
votes
1answer
85 views

When to use hashtable over binary search tree vice verse for Database Index

Database Index can be constructed using binary search trees (BST) or HashTables , From my understanding so far , HashTables are superior to BST in terms of time taken for searching and insertions ...
0
votes
0answers
28 views

create_index_cost used_bytes vs alloc_bytes

I'm reading a lot about create_index_cost, but I can't seem to find out why there is such a discrepancy between the size of used_bytes and alloc_bytes. I know what each parameter means, but why is ...
0
votes
1answer
817 views

Improve MySQL Query Performance - Too many records

I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during ...
3
votes
1answer
69 views

How to determine cost/benefit of adding an index?

According to Craig Ringer: While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations ...
5
votes
2answers
72 views

Finding the query causing the missing index DMVs

How can I find the query causing the missing index DMVs suggesting to create a particular index? Thank you.
0
votes
1answer
26 views

TokuDB + MariaDB 10: should I use a clustered index

I have a large table (200 Columns.. I know, but it wasn't my design) that will grow fast and I will have a lot of useless rows in it, identified by an IP (a varchar 32 column). The table will get ...
2
votes
1answer
29 views

Slow query on mid-size tables with good index coverage

I'm running a query that touches 2 moderately sized tables (1-5 million rows). EXPLAIN shows it touches only indexes and very few rows, however it takes very long to complete (~8 seconds). The only ...
0
votes
0answers
17 views

Missing index recommendation for an already existing index [duplicate]

SQL Server missing index DMVs suggest to create an index which already exists. The existing one and the suggested one exactly overlap each other; they are single (same) column index with no included ...
1
vote
1answer
68 views

MongoDB index strategy

I'm developing a system to back-up computers and store the files / data with retention for a while. The actual files are stored in an object store, while I'm planning to keep the metadata of the files ...
0
votes
1answer
98 views

Use index intersection feature in mongo 2.6 to compound shard key

I am having three indexes like name,age,salary. I can use them as compound index as per 2.6 (name and age together). But can i use the same indexes as compound shard key like name and age. i also have ...
10
votes
3answers
11k views

Optimizing queries on a range of timestamps (two columns)

I use postgresql-9.1 with ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
1
vote
1answer
49 views

Dropping multiple KEYS of a table

Dropping multiple KEYS in a table is a preferred option or one by one is preferred? “DROP KEY A,DROP KEY B,DROP KEY C" For more 900 million records in the table. How does it work internally?
1
vote
2answers
149 views

Index rebuild log growth

How much log index rebuild will generate.I remebered reading rebuild index should generate same amount of log file as table size.But my tests are showing otherwise.We need this estimate since we are ...