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
1answer
117 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
126 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
150 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
78 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
24 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
114 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
58 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
200 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
83 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
191 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
58 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
125 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
89 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
283 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
75 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
59 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
73 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
82 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 ...
0
votes
3answers
67 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
69 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
190 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
51 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
149 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
152 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
79 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
63 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 ...
0
votes
1answer
43 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
44 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
50 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
79 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
140 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
104 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
39 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
58 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
47 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
63 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
74 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
156 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
62 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
510 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 ...
2
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 ...
1
vote
1answer
84 views

Get the DDL for all indexes - Oracle

I have about 5 schemas in my oracle 12c database for which I need the DDL for all the indexes for each of the schemas. DDL I have checked it on how to get the code from it, but for all the indexes and ...
11
votes
4answers
552 views

Unable to create a Filtered Index on a Computed Column

In a previous question of mine, Is it a good idea to disable lock escalation while adding new calculated columns to a table?, I am creating a computed column: ALTER TABLE dbo.tblBGiftVoucherItem ADD ...
1
vote
2answers
72 views

How do you achieve a physically sorted table file organization?

Do I just declare an index on a column in the schema like ? ALTER TABLE `table` ADD INDEX `index_name` (`colDate`) I want the table records to be physically sorted not just when doing an ORDER BY ...
6
votes
2answers
229 views

Index Fragmentation - Am I interpreting the results correctly?

I'm not a DBA, but I'm responsible for a database that currently has hundreds of tables and ~5TB of data. I recently ran the following query, in hopes of determining index fragmentation: Declare ...
1
vote
3answers
166 views

Get count estimates from pg_class.reltuples for given conditions

Is it possible to query the reltuples column for a given table with additional conditions like table.name LIKE 'hello%'? Currently on my bigger tables the SELECT count(*) query takes long and I ...
1
vote
1answer
147 views

Using index on datetime field not working really

Here is my query select msisdn, sum(score) as score from contest_player_update_history_ideacontest6_35 where request_date >='2015-01-14 00:00:00' and request_date <= '2015-02-03 23:59:59' ...
2
votes
1answer
70 views

Why is my query not using “Missing index tip”? ( SQL Server 2008 R2 )

I know there's a lot of questions like this one, but I think this is different, and none of them could help me. When I run a query with execution plan, I get this: But, even with the index ...