Questions tagged [index]

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 sorted but structures the data differently to allow faster access.

Filter by
Sorted by
Tagged with
0
votes
1answer
31 views

msdb system table fragmentation

I have a Index Maintenance job for msdb database to rebuild those fragmented index in msdb database. But I still found some backup related table in msdb database (e.g. backupmediafamily, backupset, ...
5
votes
2answers
14k views

Add UNIQUE index fails with duplicate entry error, but no duplicates found

Given table: CREATE TABLE mytable ( field_a CHAR(15) NOT NULL DEFAULT '', field_b MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, field_c SMALLINT UNSIGNED NOT NULL DEFAULT 0, field_d SMALLINT ...
1
vote
1answer
2k views

How to optimize a Full Text Search

So we have this VARCHAR(255) column called code, which is a string of varying size we use in a WHERE clause. Doing this search slows the query down by about 300ms and since the query is run a few ...
33
votes
1answer
8k 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 ...
6
votes
4answers
15k 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 ...
10
votes
2answers
5k views

Is there a way to force an index to stay in memory with SQL Server 2008?

I've got a table with several millions rows, from which I need to run some queries from time to time. First query will usually be quite slow (around 10s), and subsequent queries are usually way faster ...
1
vote
4answers
1k views

Find and Remove Duplicate Indexes

I have more then 1000 tables in my database. On few tables I found duplicate Indexes. Now I want to find duplicate indexes in the database and remove them. Means, if a table have 2 or more indexes on ...
3
votes
1answer
78 views

Do I need to rebuild heap table after rebuilding a non-clustered index?

Assume that I have a heap table with a higher fragmentation percentage and that table also has a non-clustered index with higher fragmentation too. I can't reproduce this case right now but maybe ...
1
vote
1answer
23 views

MySQL wrong use of index when a date interval is big

I have a query that have this statement: SELECT DISTINCT C.id_produto FROM movdiario C INNER JOIN PRODUTO P ON C.ID_PRODUTO = P.CODITEM INNER JOIN estoques B ON B.id_produto=C.id_produto ...
0
votes
1answer
197 views

index_merge not working in MariaDB

There is MariaDB 10.1.20 (also checked in MySQL CE 5.7 and Percona Server 5.7) 1) Create table: CREATE TABLE test1 ( id INTEGER NOT NULL DEFAULT 0, flag enum('y','n') NOT NULL DEFAULT 'n', ...
1
vote
1answer
853 views

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

In MongoDB, 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 and ...
2
votes
1answer
126 views

Mysql GROUP BY is really slow on a simple view

I have these tables. CREATE TABLE `movements` ( `movementId` mediumint(8) UNSIGNED NOT NULL, `movementType` tinyint(3) UNSIGNED NOT NULL, `deleted` tinyint(1) UNSIGNED NOT NULL ) ENGINE=InnoDB ...
1
vote
1answer
114 views

How to use index for simple time range join?

I have a large (~ 100 million rows) timeseries table t_16 in Postgres 11.5 where the primary key is a field abs_date_time of type timestamp. This is a follow up of this question: Why does a CTE ...
0
votes
1answer
217 views

Best option to index an application log table for quick insertion and retrieval in date order?

Suppose I have an application trace log table with three columns: (id uniqueidentifier, message nvarchar(max), and started datetime2). The primary key (clustered) is on [id]. I need to be able to ...
0
votes
1answer
189 views

Is mongodb upsert the right option for write intensive use case?

I'm designing a data model for a transactional use case which is write intensive. The write load would be 900 - 1,600 Transactions Per Second (TPS) which would account for 30 million Inserts/Updates ...
2
votes
1answer
9k views

How to speed up query on table with millions of rows

The Issue: I'm working on a big table that consists about 37mln rows. Data include measurements of many devices made in certain time e.g. '2013-09-24 10:45:50'. Each day all of those devices are ...
0
votes
2answers
55 views

Sql Server Standard 2014: Reindex job locked by other transactions

On a customer of ours we have Microsoft SQL Server 2014 - 12.0.4100.1 (X64) installed. It's a heavy transactions schema (especially during nights) and we have a scheduled REINDEX maintenance job that ...
1
vote
1answer
3k views

Index is not used when joining two tables in a stored procedure

In MySQL 5.7, I have two tables: CREATE TABLE `periodholdings ` ( `stkcode` varchar(255) NOT NULL, `participantid` varchar(255) DEFAULT NULL, `participantname` varchar(255) DEFAULT NULL, `...
16
votes
1answer
4k views

Composite Primary Key in multi-tenant SQL Server database

I'm building a multi-tenant app (single database, single schema) using ASP Web API, Entity Framework, and SQL Server/Azure database. This app will be used by 1000-5000 customers. All the tables will ...
1
vote
1answer
31 views

How to troubleshoot poor trigram (gin) index performance?

I have a compound LIKE in a query that would have seemed to have benefited from a gin index however on two practically identical servers I see vastly different performance, with the worst case being ...
1
vote
2answers
28 views

Postgres doesn't use expression index for group by

I'm doing a simple group by on a single column with a matching index and it works fine: create index on contacts (home_city); select home_city, count(*) from contacts group by home_city limit 50; ...
3
votes
1answer
87 views

Save CPU cycles on SQL Server Query Plans (Better without Stats than with on wide tables)

Assume that a query in question has a very wide set of columns. The query looks like the following: set statistics io,time on; Select a.Col1, a.Col2, b.Col3, b.Col4, c.Col5 From FirstWideTable a ...
1
vote
1answer
24 views

Impact of partition scan on large and small data

I have a table SampleTable which has a clustered index on Date column, and is also partitioned on Date column. However the Date column is of type DateTime. While querying from this table i.e. ...
0
votes
2answers
27 views

slow index creation with gist and gist__int_ops

I am seeing performance issues with frequently updated / inserts table, and one of my hypothesis is the gin index I have on int array. The gin index was added since we have query of "X exists in ...
0
votes
1answer
107 views

MongoDB Indexing - What happens if I don't index all necessary fields?

Let's say I have a collection with 20 fields and a query that uses 5 of them, to find the documents I need. 3/5 of those fields are always used in the query, 1 for equality comparison, 1 for range ...
3
votes
1answer
94 views

Finding optimal indices, different query competing indices

My schema: create TABLE "logs" ( "id" serial not null default nextval('logs_id_seq'::regclass), "request" varchar(1024), "token" varchar(512) default NULL, "was_batch_request" bool ...
4
votes
1answer
241 views

SQL Server chooses unselective index

I was testing SQL Server indexes and found very strange behavior. Here is my code: DROP TABLE IF EXISTS dbo._Test DROP TABLE IF EXISTS dbo._Newtest GO CREATE TABLE _Test( ID INT NOT NULL, ...
1
vote
1answer
134 views

Changing stored procedure parameter results slow stored procedure performance

I have a stored procedure to fetch all users in the database. To do this it queries a view which is composed on two tables: Users (holds basic info like username) and UserPortals (maps users to ...
3
votes
1answer
58 views

Why does ORDER BY NULLS LAST affect the query plan on a primary key?

Using PostgreSQL 11, I have the following table with around 450 million rows: postgres=> \d+ sales Table "public.sales" ...
0
votes
0answers
24 views

SARGABLE predicate must be in DNF. Why?

This famous paper about finding Access Path in Relational Database Management System by Selinger et al. says the following about criteria tree and predicates (at the end of 3rd section). A sargable ...
0
votes
2answers
65 views

Can indexes be deactivated for particular queries in DB2?

I'm trying to evaluate the impact of a new index on various queries that run against a DB2 11.1 database. It would be convenient to be able to force the optimizer to ignore the new index on a query-...
1
vote
2answers
177 views

MySQL CREATE INDEX slowing down

My analytics table has 190M rows, about 150GB ins size. Storage engine is MyISAM with key-buffer-size=16G myisam_sort_buffer_size=2G myisam-max-sort-file-size=200G. Machine has 32GB memory. While ...
5
votes
1answer
1k views

single query using OR operator vs multiple queries using equals operator

Case 1 - Single query using OR operator in where clause: select * from users where name='smith' or nick='smith' (using index_merge ) Case 2 - multiple queries using equals operator in where clause: ...
1
vote
2answers
47 views

Index on a partitioned key

we have a list partitioned table. Let partitioned column be c1. We have an index on that column(c1). We are planning to drop the index. Our doubt is that if we drop the index, does it affect the ...
0
votes
1answer
33 views

PostgreSQL: Table and index on different tablespace

In PostgreSQL i have the freedom of choosing to put index (of a table or materialized view) on tablespace other than the one i chose for the table (or materialized view) itself. Is there any ...
0
votes
2answers
239 views

Transaction Log file size for Index reorg

I have a DB that is 100GB. It's fixed this way on purpose. It won't exceed this. I have two indices. One with a single value. One with three values. I'm not a DBA. I'm a guy who's been tasked with ...
6
votes
1answer
3k views

Use GIN to index bit strings

I'm trying to extend PostgreSQL to index bit strings up to 1000 bits. (These bit strings are created by quantization of high-dimensional vectors, so for each dimension up to 4 bits are assigned). ...
1
vote
1answer
43 views

Understanding composite BTREE + GIN_TRGM_OPS index prioritization & odd lower() behavior

hoping someone can try to help me decrypt some index behavior. I'm working on enabling some simple contains type lookups on various user-data columns (~varchar < 255) and trying to understand the ...
5
votes
1answer
53 views

What accounts for a large single index size (other than bloat?)

I've been doing some digging into the size of our production PostgreSQL 9.6 database and found some results I thought were surprising. We have a table (let's call it foos) with about 10 million ...
2
votes
2answers
165 views

Can indices save a giant `WHERE foo IN (…)` query with thousands of entries in the query list?

I have some client code hitting my postgres instance with a giant SELECT statement that is matching against a list of IDs. The query ends up looking like this... SELECT "id","name","status","...
1
vote
2answers
225 views

Oracle: Physically reorganizing rows of a huge table by a different key

I have a huge table RECORD -- a few billion rows -- which contains data loaded continuously in a lot of batches. I can assign a primary key RECORD_ID from sequence, or use natural composite key ...
10
votes
3answers
2k views

Filtered index only used when the filtered part is in the JOIN not the WHERE

I've created the filtered index below however when I run the 2 queries further down this index only gets used for a seek in the first example that has the END_DTTM in the JOIN rather than the where ...
9
votes
2answers
2k views

Should I mark a composite index as unique if it contains the primary key?

Given some table with a primary key, e.g.: CREATE TABLE Customers ( CustomerID int NOT NULL PRIMARY KEY, FirstName nvarchar(50), LastName nvarchar(50), Address nvarchar(200), Email ...
0
votes
0answers
47 views

Problem with calculating an approximate space required for online index rebuild

I have a database called TestDB which is in simple recovery model. I performed index rebuild on my Test Server which has no other user transactions except me. Before an online index rebuild the sizes ...
3
votes
1answer
116 views

How to find dropped or ghosted columns in Azure SQL DB? [duplicate]

In SQL Server, dropping a column is a metadata only change; the data remains in the index until the index is rebuilt. On prem, I use system_internals_partition_columns to determine what indexes have ...
1
vote
1answer
130 views

Is database record access sequential after retrieving the indexed page from disk? [closed]

This question was posted on Stack Overflow but left unanswered, I was hoping maybe here I might find an answer. What I understand about databases that if a queried or requested record is found in the ...
0
votes
1answer
124 views

What index(es) and queries for multiple column ranges search?

Based on a simple innodb mysql 5.6 database schema like this: What is the optimal indexing strategy for querying the "info" table where most of the search criteria will be IN and BETWEEN conditions? ...
3
votes
2answers
156 views

Create index for multiple WHERE EXISTS subqueries

I have to find an efficient way to write a very specific query for an application that I'm developing. The idea is the following: Find all the genetic markers for which there exists at least one ...
0
votes
2answers
168 views

Best Indexing for Performance - MySQL

I have a question related performance DB MySQL. I have querying like this with big data inside : SELECT * FROM tableA a INNER JOIN tableB b ON a.id = b.id AND a.code = b.code ...
4
votes
4answers
216 views

Leveraging an index on another column with the same ordering guarantee

I have a massive table that is only indexed by the auto-increment column, which is also populated with the current timestamp (which is not indexed). CREATE TABLE MyTable ( ID BIGINT NOT NULL ...

1 2 3 4 5 52