Stack Exchange Network

Stack Exchange network consists of 175 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.

2
votes
1answer
13 views

Is it possible to add a spatial index to a partition with Postgres 10?

I am creating declarative partitions like: CREATE TABLE mydata.my_features ( id bigint, my_feature1 character(1), my_feature2 integer, ... my_geom geometry ) PARTITION BY ...
-1
votes
0answers
26 views

If everyone has index, none has it?

Ok, So in my case, I'll be frequently querying a table based on all of the columns and logically I am inclined to have all the columns indexed. But then if all the columns are going to be indexed ...
0
votes
1answer
26 views

Questions on database table and index partitioning in SQL Server

I have developed Transact-SQL script to partition the table. To apply the partition, I am creating one non-clustered index for my partition key column and associating it to partition scheme. I have ...
3
votes
0answers
33 views

Best index for column where values are mostly the same

We have an integer column that currently consists only of 0 or 1 values. This column has now been used by a developer to store a unique 32-bit identifier on some occasions, and we need to be able to ...
2
votes
1answer
32 views

SELECT very slow when JOIN and LIMIT are specified but no/few results found

I am running into an issue where the query planner does not take into account how a JOIN effects the frequency of particular values. For some context: I am storing sessions in an append only log ...
2
votes
1answer
74 views
+100

Postgres partial index on IS NULL not working

Postgres version Using PostgreSQL 10.3. Table definition CREATE TABLE tickets ( id bigserial primary key, state character varying, closed timestamp ); CREATE INDEX "state_index" ON "tickets"...
0
votes
0answers
37 views

How to utilize an INDEX when filtering a VIEW? [on hold]

edit: Double-posted this to Stack Overflow where I received some great input. Worth a look. I have a very simple query that executed surprisingly slow and the reasons turned out that it was doing a ...
0
votes
0answers
27 views

add index and change int data type to bigint in mysql 5.7 with 1.3 billion records the fastest way

how do i do this without downtime? or at least minimal downtime (3hrs max) Is there any way to tweak my.cnf config to at least make this process of indexing and change data type faster? Below is the ...
3
votes
1answer
35 views

Definition of indexing, datatype configuration, and DBMS for SHA3-256 hashes

I am designing a system within which I will need to be able to search a database using a file or string SHA3-256 hash. For example the hash of test is ...
0
votes
0answers
24 views

SQL Server - Cannot reorganize index on “extended_index” table

I have a maintenance plan that includes an index reorganization step. I am getting the following error in the job history: Executing the query "ALTER INDEX [SpatialIndex-20180705-165942] ON [dbo......
4
votes
2answers
44 views

Performance issues with groupwise MAX in JOIN clause

Problem I have some assets in my application which are updated from time to time in an asynchronous fashion. The example I'm gonna use here is Vehicles. There are two tables: Vehicles: holds ...
19
votes
4answers
3k views

Is it bad to have index space larger than data space?

Often I need to run queries against large tables that don't have the right index. So I ask the DBA to create such index. The first thing he does is look at the table statistics and see the index space ...
0
votes
0answers
20 views

Running REINDEX in a live environment (POSTGRESQL 9.6.10)

I have a live app where I have some big tables (~100+M rows), which I recently truncated to ~20M rows. One behavior I've noticed after making the said change is that DB response time has escalated in ...
4
votes
3answers
89 views

postgres bloom index

i read the doc about postgres bloom, but can't reproduce the same results, please help me to understand what i missed. my server is: SHOW server_version; server_version -------------...
1
vote
0answers
26 views

Why does indexing take forever on an Aria table?

I have a table using the Aria engine on MariaDB 10.3, it stands at 123.5 GiB in size on an SSD server, with plenty of space available (the disk could fit another copy of the unindexed database + more)....
5
votes
4answers
445 views

Which columns should be indexed when all may be used in different search queries?

Background I'm working on a website for a movie theather chain currently located in four different cities (might expand in the future). They use the same, single-database website for all cities, ...
0
votes
0answers
42 views

Slow query with order by join table

I have a SQL with multiple join left, because some data is empty. However, I need to sort the data using a string column from a join table. I thought about creating an index for this table, but this ...
0
votes
0answers
13 views

Pagination using seek-method with nullable columns, sharing a composite index with ORDER BY

I have the following table: CREATE TABLE users( id text PRIMARY KEY, -- actually a uuid organization_id text NOT NULL, lname text NOT NULL, fname text NOT NULL -- other irrelevant fields ); ...
6
votes
1answer
378 views

Index help for bad Query on fairly big table (2m)

I am looking for some assistance please. I have a query that is running against a fairly big table (2 million records). I have been trying to get the indexes working efficiently. There are a few ...
-3
votes
1answer
80 views

What is the difference between the date queries and which is more efficient? [closed]

Where tt.transactiondate >= getdate () -1 Where tt.transaciondate >= dateadd (day, -1, getdate()) Where dateadd (day, 1, tt.transactiondate) >= get date() Where datediff (day, tt....
1
vote
1answer
87 views

JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)

I have a database which stores receiver to indicate which account the data relates to. This has led to tons of duplication of data, as one set of data may create 3 separate rows, where all column data ...
1
vote
0answers
20 views

MSSQL - Unable to find index entry in index ID 1

Currently keep getting this severity 21 error through for a database that is hosted on SQL 2014 Enterprise with SP3 CU1 applied: DESCRIPTION: Unable to find index entry in index ID 1, of table ...
-3
votes
1answer
38 views

Can we get any performance gain from creating index from date column?

I have seen lot of indexes created on the date columns in my project. Normally the index is a combination of a date column and another column (like state) which are used to create an index. Since ...
4
votes
1answer
210 views

Covering Index Changes Execution Plan but is not used

I have the following occasionally slow running query: SELECT C.CustomerID FROM dbo.Customers C WITH (NOLOCK) WHERE C.Forename = @Forename AND C.Surname = @Surname OPTION (RECOMPILE) CustomerID ...
-1
votes
1answer
78 views

Index vs Statistics [duplicate]

In SQL Server, when to create statistics only on a table column instead of index and when create to index (which creates statistics as well automatically)? I mean both improve query performance then ...
0
votes
2answers
47 views

How to speed up query with anti-joins

I have a query with 2 anti-joins (UserEmails = 1M+ rows and Subscriptions = <100k rows), 2 conditions, and a sort. I've created an index for the 2 conditions + sort, which sped up the query by 50%. ...
0
votes
1answer
21 views

Why Postgres uses a custom defined index instead of the default one on the primary key

I have a table called alpha with many columns, the primary key is just alpha_id. I have also an index (super_index) on that table that uses alpha_id and status, which is just another column. I run a ...
0
votes
0answers
22 views

What can cause MySQL to accidentally execute the same operation twice in the same second?

For the past couple months, I've been seeing a lot of these errors: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry The proximate cause is that I have a unique ...
9
votes
1answer
330 views

Missing Non Clustered Index already part of Clustered Index

I'm debugging a slow running query and in the execution plan a non-clustered index is suggested, with 51.6648 Impact. However, the non-clustered index only includes columns that are already in the ...
0
votes
1answer
27 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
114 views

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

Schema: Column | Type ----------------------+-------------------------- id | integer event_id | integer ...
0
votes
1answer
40 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
50 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
689 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
57 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
92 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
52 views

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

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
50 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
27 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
30 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
115 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
57 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 ...
1
vote
2answers
55 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
54 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
52 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
36 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
50 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
49 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
24 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
40 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?