Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

1
vote
1answer
41 views

nvarchar(max) in Included Columns of NC index

Would adding nvarchar(max) to the included columns of an NC index that has one nchar(16) column key column cause rebuild issues with 20+ million rows?
0
votes
1answer
23 views

Why are bulk multi-column key queries so slow in MySQL?

(For this question, I am using AWS/Aurora MySQL with a reasonably-spec'd RDS instance) Consider the following schema: Table T: col0: the usual autoincrement primary key col1: varchar ...
2
votes
2answers
97 views

Containment check tstzrange @> timestamptz not using btree or gist index

Schema: Column | Type ----------------------+-------------------------- id | integer event_id | integer ...
0
votes
1answer
39 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 ...
1
vote
1answer
44 views

MongoDB Indexing - How many fields should I index?

I have a Collection in my db, called Post. Here's how it looks: Post { user: <EmbeddedObject>, school: <EmbeddedObject>, hashtag: <EmbeddedObject>, numberOfReports: ...
11
votes
1answer
624 views

Do SQL Server compressed indexes remain compressed on rebuild without specifying data compression?

After one rebuilds their SQL Server indexes using page compression (ALTER INDEX IX1 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)), do subsequent rebuilds (as done by some maintenance scripts ...
1
vote
0answers
39 views

Creating index on a big text column in postgres

We are talking about postgresql 9.5 on windows with a database which has UTF8 encoding and "English_United States.1252" (default) LC_COLLATE and LC_CTYPE. The column is of type text and it is used ...
0
votes
2answers
90 views

Sql Query Performance

I have one query for the report. Query Use RIS select CİHAZ,sum(sayi) as [Çekim Sayısı], count(*) as [Hasta Sayısı] from (select CİHAZ,[KABUL NO],[TC NO], count(*) as Sayi from (SELECT a....
-1
votes
1answer
47 views

Composite index and Normal index,redundant/duplicate index - Incurred Performance penalty

Need suggestion on below indexes, whether our composite index is enough and can the normal index be removed. Scenario - We have ix4 and ix6 indexes as below, please review and advise if ix4 is ...
0
votes
1answer
48 views

Database Size Increase After Index Rebuild.How to get back down?

I executed an Index Rebuild for a large table in database because it was 85% fragmented. After the Index Rebuild finished the database file size has grown from 19 GB to 27 GB. Rebuild option: (...
3
votes
1answer
24 views

Query column A, order by B - is index needed if A and B together are the PK?

I have a table (in Postgresql) which has a primary key on columns a and b. I want to do a query: select * from table where a='XXX' order by b; Does the existing primary key serve to help with the ...
1
vote
1answer
26 views

A low cardinality column index and SELECT

Suppose that a long (1000000 entries for example) MySQL table t has a column c with less than 10 distinct TINYINT values. Suppose also that most (say 99% or even 99.9%) of these values are equal to ...
1
vote
1answer
74 views

Does it make sense to create an index on foreign key (uuid) column in Postgres?

Assuming 2 sample tables in Postgres 10.X: CREATE TABLE public.order ( id VARCHAR(36) NOT NULL, ... ) CREATE TABLE public.suborder ( id VARCHAR(36) NOT NULL, order_id VARCHAR(36) NOT ...
5
votes
1answer
46 views

Do BRIN indexes support ENUM types?

BRIN indexes seem useful, but I'm not sure how to use one on an ENUM type. I thought this code would work: CREATE TYPE test_enum AS ENUM ('a', 'b'); CREATE TEMPORARY TABLE my_table ( x test_enum ...
2
votes
1answer
38 views

Simple indexed select query takes 350 seconds (?!) even though Filtered 100.00 “Using index condition”

My logs show that the query being explained here took 352.19 seconds (and similar times for other similar queries where the contact_id and execute_at were different). EXPLAIN SELECT * FROM `...
1
vote
1answer
51 views

Is there any benefit to having both an ordered and unordered index on the same column in postgres?

I have inherited a postgres database where there are two identical indexes except for their order: CREATE INDEX avail_time_ix ON table (availability_time); CREATE INDEX avail_time_dc_ix ON table (...
0
votes
1answer
49 views

Stored Procedure infinite looping after index updates

I have inherited a very old and poorly written stored procedure with nested fetch statements. This code processes a large amount of data by copying from one database into another. This process was ...
1
vote
1answer
35 views

Indexing for a table with many large subsets

In SqlServer 2017 we have a table with millions of rows. The table has a few dozen columns. One of the columns is a varchar(50) with a well defined range of allowed values, the value of this column is ...
1
vote
1answer
49 views

Oracle Optimizer refuses to use my index

I have a relatively simple query that SELECT data in a table. SELECT l.columnRecovered FROM schema.TABLE l WHERE (column1 = :a or (:a is null AND column1 is null)) AND (column2 = :b or (:b is null ...
1
vote
2answers
46 views

Live update index in production database [closed]

I have read Perform Index Operations Online provided by MSFT but I couldn't find any detail information for my questions. My questions are: What is the impact when I update (create/drop) non-...
0
votes
1answer
23 views

What index strategy to use to improve multi-condition SELECT?

As far as I understand, indexes should be selected on fields with high cardinality of data - e.g. sex only has two values ( male and female ) so low cardinality, whereas something like sha256sum field ...
0
votes
1answer
38 views

Is there a situation where MySQL creates a nonclustered index automatically?

MySQL creates a clustered index automatically when I create a primary key. But is there a situation where MySQL creates a nonclustered index automatically?
1
vote
1answer
45 views

MySQL not using indexes

My query does not seem to bother for using indexes. Collations are the same on each table. EXPLAIN SELECT SUM(it.revenue) FROM invoice_items AS it INNER JOIN invoice AS i ON i.id = it.invoice_id ...
2
votes
1answer
635 views

Do primary keys change when indexes are rebuilt or reorganized?

I have a database containing tables that each have auto-increment int columns serving as the primary key column. When I do a rebuild or reorganize operation on my indexes, does either operation ...
1
vote
1answer
152 views

postgresql rename index

I want to understand how safe is the rename command: ALTER INDEX old_name RENAME TO old_name; The index was created by command(for the foreign key column): CREATE INDEX old_name ON table_t1 (...
1
vote
1answer
43 views

Does an index improve the querying performance in just two ways? [closed]

As far as I know, an index improves the querying performance in the following two ways: The index only contains the values of the column that it was created for and pointers to the rows in the ...
1
vote
2answers
167 views

Clustered Sequential GUID Primary Key vs Non-Clustered GUID and Clustered Sequential ID Primary Keys

I'm trying to figure out what the optimal solution is in regards to size and performance when using GUIDs as a primary key. The main reason for wanting GUIDs is that it's more appropriate in our micro-...
8
votes
1answer
2k views

TOP(1) BY GROUP of very huge (100,000,000+) table

Setup I have a huge table of ~115,382,254 rows. The table is relatively simple and logs application process operations. CREATE TABLE [data].[OperationData]( [SourceDeciveID] [bigint] NOT NULL, ...
1
vote
0answers
35 views

DeadLock - Two Process Locked Same Index

We have met a scenario that two process acquired exclusive lock on same index ans the same time two are requesting for shared lock to other process which eventually leads to dead lock. I am still not ...
7
votes
1answer
90 views

PostgreSQL ANALYZE execution time over 24h (still running)

I upgraded a Postgres DB 9.3.2-->10.5 using pg_upgrade (in place). I did everything according to the documentation and the instructions given by pg_upgrade. Everything went fine but then I realized ...
1
vote
0answers
16 views

How long does it take for Cassandra to create an index? [closed]

I couldn't find anything on this. Is there a progress bar or whatever to check how long a secondary index creation will take?
0
votes
1answer
30 views

Composite index design where only a subset will be queried

I have a table with columns A (typedate), B1 ... B24 (all boolean). Generally, A will always be used in every query and only one of B1 ... B24. A large subset of the B columns (more than half) will ...
1
vote
1answer
36 views

MariaDB uses “wrong”/irrelevant Keys on large table

We have migrated our database from a local hosting company to AWS. Now when we run a particular query on AWS it finishes in about a minute (processlist says "sending data") where on the old server the ...
0
votes
1answer
65 views

REINDEX or REBUILD the indexes of a table after truncating it and then repopulating it?

Is it necessary to REINDEX or REBUILD the indexes of a table after truncating it and then repopulating it? I have a table with approximately 3.5 million rows in Oracle. Every week new data is ...
0
votes
1answer
33 views

Postgres jsonb complex query

I have a table on PostgreSQL 10 that looks like: CREATE TABLE forms (id serial, form jsonb); INSERT INTO forms (id, form) VALUES ( 1, '"permissions": [ {"name": "perm-name-1", "who":["group1", "...
2
votes
1answer
90 views

Query processor could not produce a query plan because of the hints defined in this query

There is a similar question, but it is not the same: The query processor could not produce a query plan I have the following query, and the following filtered index, and I cannot see any reason why ...
0
votes
1answer
26 views

Primary index is being used, not the one which is on the field present in the where condition

I have 2 tables, one is category table and has below coulmns and has a primary index on cat_id column. cat_id (primary key), acc_id, islive Another table is cat_brand table which has below fields ...
1
vote
1answer
384 views

“The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.” after adding indexes to temp tables

Recently, I added some indexes on temp tables to procedures which prepare data for data warehouse. On a testing db, tests of these procedures worked fine but when running on client's server it failed ...
2
votes
1answer
32 views

Secondary composite-key pages access

we have this relation Store: [SID, City, EID] Employee: [EID, Name, Salary, SID] Article: [AID, Name, Producer, Price] Inventory: [AID, SID, Count] Invoice: [IID, SID, Customer] Item: [IID, SID, AID, ...
0
votes
1answer
48 views

What and where to index, rapid increasing table postgres

I work as a software engineer with python and django. Currently I am struggling with a design choice made before my time. We have a transaction table that logs all customer activity. Due to the ...
0
votes
2answers
32 views

mysql v5.5 index performance

On a 5.5.23 server (that needs to go to 5.7 at least, I know) I have an InnoDB table with 3 columns (there are a few more that are not relevant here): TimeC (bigint, unix timestamp) id (integer) ...
1
vote
0answers
41 views

POSTGRESQL REINDEX INDEX Time estimation

to explain the context, I test postgres with a huge table (I try to have 1TB data) in a docker container for isolation, and I ran my container with a -v parameter in order to mount my dedicated HDD (...
-4
votes
1answer
26 views

About the Index in MYSQL [closed]

I have a question about indexes in MYSQL. I have a table, DIM_BANK. The primary key is the column ID_BANK. MySQL automatically created the an index for this with the following attributes - BTREE (...
-1
votes
1answer
33 views

Mysql - index - optimization (two separate or a multikey)

I have a question for the experts I couldn't find an easy answer in the docs: I'm using pseudo code as the real table doesn't matter so much on the theoretical question itself. Given I have a table ...
2
votes
2answers
47 views

Duplicate index - Same key, one with INCLUDE columns, which one to drop?

I have 2 indexes that have the same key, but one also has INCLUDE columns. Which one can I drop? They are the same, except IndexInclude has 6 INCLUDE columns. Both have some reads (IndexNoInclude ...
1
vote
0answers
25 views

optimise computation of analytics from mongodb collections specific to user [closed]

I am looking for some ways to optimise computation of analytics from mongo v3.6.7 collection s specific to user. For eg lets say i have 3 mongo collections and I want to generate a report 1 for a user ...
0
votes
2answers
80 views

mySQL query optimizer not using indexes

In our production environment (mySQL 5.6.40 on Amazon RDS), for certain queries the query optimizer ignores table indexes and takes several minutes to complete, whereas in our non-RDS test environment ...
3
votes
2answers
61 views

Optimization of a query on a table that represents a weak entity type

I've been trying to figure this problem out for a while now, so I decided I'll try my luck here. I have a very complex online shop DB structure (multiple shops in one database, hundreds of thousands ...
-1
votes
0answers
14 views

Query performance issue in Mysql

Table is completely normalized its in innodb. Size of data is more than 600 million records. Even after indexing and optimization queries are too slow. Size of records increases rapidly and it would ...
-1
votes
0answers
21 views

How to reset id?

id is the primary key in table student. select id from student; +-----+ | id | +-----+ | 171 | | 175 | | 183 | | 184 | | 190 | | 191 | | 200 | +-----+ 10 rows in set (0.00 sec) How to reset the id ...