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
77 views

Hash index use case in MongoDB

Recently MongoDB added hash index to its features but in the documentation doesn't explain much about hash index use case and its privileges to normal MongoDB index. So I'll be grateful if you guide ...
1
vote
0answers
24 views

Index comparison

On my Production server (SQL2008) I have a query that returns suggestions of indexes. SELECT * FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats gs on ...
0
votes
1answer
36 views

MySQL: Overlay modifications on original data while indexing on a field that itself has modifications

I have a MySQL table of original_data that is important for historical/auditing purposes and should not ever be changed. I want to be able to mark up these data with modifications, e.g. to correct ...
2
votes
3answers
193 views

Performance impact of NOT using a foreign key (many dedicated 1:many key-key tables vs non-fk generic key-key table)

We're using a lot of association tables to manage 1:many relationships among a variety of different objects in our system. To illustrate the question, two examples would be: users, events, ...
0
votes
0answers
30 views

Help with fulll text index of large 100gb DB (yago) in MySQL

I'm trying to import the a full dump of Yago into a mysql (actually MariaDB -- I'm running an up-to-date arch system) database. The data is available here: ...
1
vote
2answers
71 views

How to determine the best index on a table with 3 nullable columns?

I am examining a users table similar to this: CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `password` ...
1
vote
0answers
79 views

How can I rebuild an index on table with 100 million rows without a wait time out?

I have the following table with ~100 million rows: CREATE TABLE Result ( MetricId int NOT NULL, PeriodId int NOT NULL, Value decimal (18, 2) NOT NULL, CONSTRAINT PK_Result PRIMARY KEY ...
1
vote
1answer
81 views

How to speed up queries in MySQL on a table with ~50 million rows

The table has 4 columns of type datetime, smallint(6), tinyint(4) and decimal(15,5). The first 3 columns are PK. There are four indexes on the table: one with all the PK columns, and one with each ...
2
votes
2answers
149 views

Create index with a commentary

I would like to add some comments to my indices. So I added some additional information to the index creation script, expecting to see this comment again as soon as I open the script again. But it was ...
1
vote
1answer
55 views

Low cardinality fields: integer or string

A Ticket has the statuses: new in_progress on_hold closed I could make ticket.status a string (on_hold) or an unique int(2). It is indexed. Int pros: smallest size in index Int con: low clarity ...
2
votes
0answers
22 views

Combine several missing index suggestions with one index?

I investigate on missing indexes recommendations of my sql server engine. Looking at the most useful ones (for a specific table) that are candidates based on their potential usage and potential ...
1
vote
0answers
68 views

Does “CREATE INDEX WITH DROP_EXISTING” use the bufferpool for the recreated index?

We have recreate our 4TB clustered index in ouor SQL Server. Even thought we have 1.5TB of memory we still run into the following error message: "Msg 802, Level 17, State 2, Line 30 There is ...
1
vote
1answer
120 views

MYSQL - FORCE INDEX ON A DELETE

UPDATE backup.records FORCE INDEX(IDX_records_deviceId) SET readyToBeDeleted = 1 WHERE deviceId = 226219 AND timestamp <= 1445255546 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space ...
1
vote
1answer
54 views

Understanding index fillfactor

I'm trying to understand the index FILLFACTOR parameter on a simple example. I have a table that is generally updated only once in ten days, but the update is quite heavy (requires 5-6 hours). Now, ...
3
votes
3answers
176 views

How is combined index implemented in SQL Server

I understand how single indexed column works in SQL Server and how it is implemented using balanced trees. There are plenty of interesting videos on YouTube on this topic. However I don't understand ...
0
votes
0answers
68 views

dropping index in a table that has 8 million entries

I am trying to execute the following: DROP INDEX idx_deletedAt_isLocked_testi ON app_instagram_shop_picture; DROP INDEX idx_id_deletedAt_isLocked ON app_instagram_shop; currently I am using mysql ...
3
votes
2answers
122 views

MongoDB uses only one index on simple query with sorting

So the problem is that I'm having a huge collection and I'm trying to run a query that filters and sorts on only fields that have indexes on them. I have these two indexes: ...
1
vote
1answer
52 views

Does InnoDB store row counts in secondary index B-Tree non-leaf nodes?

Does InnoDB store row counts of child nodes in secondary index B-Tree non-leaf nodes? If so, does MySQL use this information to speed up COUNT(*) queries with range constraint (assuming appropriate ...
1
vote
3answers
79 views

MySQL Indexing and performance when size of index is greater than size of data in table

I wrote this query for seeing if the size of the indexes in certain tables were larger than the actual amount of data in the table, using MySQL 5.6 and InnoDB as the engine. SELECT TABLE_NAME, ...
0
votes
1answer
39 views

How do I create a table best suited for storing address information

I have to create an address table in Oracle. Table is expected to hold millions of row. I created a table looking like this Table Address ID | Address Line1 | Address Line2 | Address Line3| City ...
1
vote
1answer
64 views

Is my postgres index being used?

I have a large PostgeSQL 9.1 database in production use. I want to free up some disk space. There is a large index, and I'm unsure if it's being used. select * from pg_stat_all_indexes where ...
2
votes
2answers
177 views

Very slow query in PostgreSQL on 10kk records table

I'm having very slow requests to my queries. I created an index on the filter columns and made partitioning but requests is still too slow. How can I make it faster? Here's query and explain: SELECT ...
0
votes
1answer
68 views

Improve execution time of query in POSTGRES with multiple joins

I'm having the trouble with the following query due to the growing size of the database ( websites is around a million rows, misspelled goes up to a couple millions ) It can reach up to 165 seconds ...
0
votes
1answer
39 views

dm_db_index_physical_stats results inconsistent with actual object properties - what may be the reasons?

SQL Server 2008 RTM (10.0.1600.22) Running a typical index fragmentation report on a table: SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName, s.index_type_desc, ...
1
vote
1answer
59 views

how to check whether or not queries are doing partition elimination when accessing my partitioned table?

I am considering partitioning a table. I am trying to think in terms of how we query that table so that I achieve partition elimination. Suppose I have a table that has a partition by month, what ...
1
vote
2answers
68 views

Partitioned Tables and Indexes - what are the downsides?

When talking about Partitioned Tables and Indexes for tables with less than 100 partitions, no nonaligned indexes: with that I mean: Nonaligned index An index partitioned ...
1
vote
3answers
66 views

How to optimize SQL with WHERE and ORDER BY

I have the SQL statement: SELECT erp_orders_id FROM erp_orders o WHERE o.orders_export_time >= '2015-09-20' ORDER BY o.erp_orders_id ASC LIMIT 1 where erp_orders_id is primary key of type int. ...
1
vote
1answer
59 views

Search efficiency of datetime vs date + indexed smallint (PostgreSQL)

Context We are storing weather forecasts as multiband rasters (using Postgres with PostGIS). Each forecast is identified by its date and time (either 0:00, 6:00, or 12:00). We are looking at about 20 ...
2
votes
3answers
158 views

Excluding clustered key columns from non-clustered indexes definitions

As I've read the bookmark or the reference of the non-clustered index to the table clustered index is the clustered index key itself. On the SQL Saturday event I've attended, one of the lecturers said ...
0
votes
1answer
49 views

MySQL Finding index size for compound unique indexes

I'm using following query to get index and data sizes for tables in database. But unfortunately it doesn't show index size for compound unique indexes. How can i get index sizes for compound unique ...
2
votes
1answer
113 views

indexing strategies

I am confused by indexing strategies advocated by one of the database products. Database is "DB2 for i" (iSeries/AS400). Please see following SQL and index advised by the product for the SQL. There ...
3
votes
1answer
122 views

Faster query with pattern-matching on multiple text fields

I have one Postgres table with more than 20M tuples: first_name | last_name | email ------------------------------------------- bat | man | batman@wayne.com arya | vidal | ...
1
vote
1answer
66 views

Aligned index column order

Situation: There is one SQL Server (2014) database that has multiple partitioned tables, that all share the same partitioning strategy. Tables have a partition per unique [PartitionKey]. Every [Id] is ...
2
votes
0answers
57 views

Postgres: CREATE INDEX, but very slowly, to reduce performance hit on rest of database?

I have a large production PostgreSQL (9.1) database running on Ubuntu Linux. It is currently being queried and used. However there is another use I need later, and I will need to create a large ...
1
vote
1answer
38 views

How indexing on a date field that is constantly updated and or omitted affect performance?

In mongo, I have a collection with a date field called last_updated I would like to index. Reason being, I am often querying on a date range of that field, in addition to sorting by that field. I ...
1
vote
2answers
40 views

Performing efficient searches over two columns

Disclaimer: I am extremely inexperienced with databases, so forgive me if this is an ignorant question. I have a table which contains several columns, but the ones that are important are the columns ...
1
vote
1answer
43 views

SQL Server execution plan is using PK instead of index

The following query is taking 5 seconds to return 9692 rows: SELECT Project.CalculateSequencialProject, Project.CreateDate, Project.OpeningDate, Project.ForecastClosingDate, ...
5
votes
2answers
60 views

looking for missing indexes in the cached execution plans

I have the following query that shows me what is cached in sql server: SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount, st.TEXT AS ...
5
votes
2answers
137 views

Confused about why a particular index is being used

I have a table (with 170M rows) that looks as follows: CREATE TABLE [dbo].[Panel] ( [SubId] [varchar](15) NOT NULL, [LineageId] [int] NULL, [Buck] [varchar](20) NULL, [Lot] ...
2
votes
1answer
80 views

Can we create index for key/value of JSONB data type?

Can we create an index for a key/value of a JSONB data type? For example, for these schema: CREATE TABLE x ( id BIGSERIAL, data JSONB ); CREATE TABLE y ( id BIGSERIAL, data JSONB ); The ...
1
vote
0answers
28 views

How does PostgreSQL compute the cost for index scan?

Consider the following plan: Index Scan using foo_c1_idx1 on foo (cost=0.00..8.38 rows=1 width=37) (actual time=0.012..0.013 rows=1 loops=1) Index Cond: (c1 = 500) Buffers: shared hit=4 Total ...
4
votes
1answer
54 views

Adding a second Group By column slows query down

I have the following query: SELECT 'TEXT', 'TYPE', 'DESTINATION', 'HEADLINE', 'COUNTRY_ID', 'COUNTRY_CODE' UNION ALL SELECT Data_1.TEXT, Data_1.TYPE, Data_2.Destination, Data_2.HEADLINE, ...
0
votes
0answers
43 views

Can we combine Bitmap index with B+ tree index?

If I have a database of employees and expected queries on (salary,gender). Would it be a good idea to have a B+ tree index on salary and Bitmap index on gender at every leaf node of a tree. What are ...
0
votes
1answer
53 views

How does lock works when delete rows through secondary index in InnoDB?

step1: InnoDB Transaction Isolation Level Repeated Read. step2: Table schema CREATE TABLE `follows` ( `id` int(11) NOT NULL AUTO_INCREMENT, `followable_type` varchar(255) DEFAULT NULL, ...
1
vote
1answer
68 views

How to measure I/O contention after adding calculated fields on indexed views to a table in SQL Server?

I have successfully added several calculated fields to different tables, for example: ALTER TABLE dbo.tblBGiftVoucherItem ADD isUsGift AS CAST( ( ISNULL( CASE WHEN ...
1
vote
0answers
44 views

Can an index become inconsistent for “no reason”?

Of course, when i say no reason i mean "apparently" no reason because nothing happened on the server (forced shutdowns, data corruption, deadlocks, etc...). I ask this because, there was an ...
9
votes
1answer
119 views

Huge mismatch between reported index size and number of buffers in execution plan

The issue We have a query like SELECT COUNT(1) FROM article JOIN reservation ON a_id = r_article_id WHERE r_last_modified < now() - '8 weeks'::interval AND r_group_id = 1 AND ...
0
votes
2answers
54 views

Is there a way to show the creation statement for an index in PostgreSQL

I need to recreate an index in PostgreSQL which has suffered index bloat. Since I need the index to be usable while it's being created, I can't use REINDEX. I am going to recreate the index with a new ...
5
votes
2answers
498 views

How to make this query use my multicolumn index?

Currently, I have a view which is defined like this: View "public.customer_list" Column | Type | Modifiers | Storage | Description ...
3
votes
1answer
126 views

what would be a good choice of clustered index (and other indexes) for this situation involving a indexed view?

To help me improve the performance of some queries I have created the following indexed view: CREATE VIEW DBO.vwOrders WITH SCHEMABINDING AS ...