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

Planning and Optimizing the table structure InnoDB MySql

I am planning to have an InnoDB MySql table with tens of millions of rows. Each row represents a record that can belong to AT LEAST one of 200+ categories. Most of the records will belong to 2 or ...
0
votes
2answers
77 views

Composite index of three columns in MySQL and WHERE by 1st and 3rd column. Index usage

Let's consider a table with 3 varchar columns and a composite index on all of them in the order of (a,b,c): CREATE TABLE `test` ( `a` VARCHAR(255) NOT NULL, `b` VARCHAR(255) NOT NULL, ...
1
vote
1answer
163 views

How can I make “phraseto_tsquery” as fast as “plainto_tsquery” in PostgreSQL

Some background: I have a table, documents with over 200,000 rows that has a column body, which can be very long. I put an index on this table: CREATE INDEX documents_body_tsvector_index ON ...
0
votes
3answers
40 views

How DB index works internally at high level?

I have around 500 GB of data in one table of MySQL which has around 5 billion records. It has around 15 columns. It has index on all the required columns. When I do select * from big_table where ...
5
votes
2answers
135 views

Why are partial PostgreSQL HASH indices not smaller than full indices?

I want to create the most efficient index for a sparsely populated column. I only need equality operations, so a HASH index should be beneficial. Now I'm wondering why a partial HASH index isn't ...
3
votes
1answer
377 views

Redundant indexes in SQL Server

I am currently going through all indexes in our data warehouse as a performance tuning exercise. Several of these indexes are not used in the system stats, and I am considering removing them to reduce ...
0
votes
1answer
46 views

Why does MariaDB choose different index use for the same data?

I have this table (this is the output from show create table on both servers, ie. using diff to compare the output shows only the auto increment value, because one has moved on slightly from the ...
3
votes
1answer
111 views

Non-clustered indexes for columns defined within a composite Primary Key

I have a multi-tenant database where I am utilizing an AccountId column within all tables as a part of the composite Primary Key for tenant isolation. Is it beneficial to create additional non-...
-1
votes
1answer
38 views

Is the index created as soon as data is inserted into a table? [closed]

For example I have a table and I created an index on ID column which generates numbers every time I insert a row. If I only insert 5 rows does index tree start to be created or is there a threshold of ...
1
vote
1answer
3k views

MariaDB 10.1.38 - Specified key was too long; max key length is 767 bytes

I have a table that has 3 columns: id(int 11) | user_id(int 4) | title(varchar 512) ____________|________________|___________________ 1 | 3 | Thing X 2 | 3 ...
0
votes
1answer
98 views

Indexing VARCHAR column?

Should I add INDEX to my VARCHAR column? I need to select rows with a string, not with ID, will it speed my queries? Example: SELECT * FROM `cities` WHERE `slug` = 'new-york';
1
vote
2answers
533 views

MongoDB does not allow to create unique partial index in field that contains null

I'm using MongoDB 3.6.9 (with Ubuntu) to save a set of documents that contain one field named rss_id and for some documents, this field has the value null. I want to create a unique index in that ...
0
votes
1answer
88 views

MariaDB 10.2.22 Galera Cluster Using Bad Indexes After Analyze

I'm using Drupal on a MariaDB Galera 10.2.22 3 node cluster and I'm having some problems with one query. I have a table (InnoDB) called field_data_field_department which has a bunch of indexes. If I ...
1
vote
1answer
415 views

How to store high-dimensional (N > 100) vectors and index for fast lookup by cosine similarity?

I am trying to store vectors for word/doc embeddings in a PostgreSQL table, and want to be able to quickly pull the N rows with highest cosine similarity to a given query vector. The vectors I'm ...
2
votes
1answer
56 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 ...
0
votes
1answer
42 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 ...
6
votes
1answer
287 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 ...
3
votes
1answer
42 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 ...
3
votes
1answer
573 views

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

add index and change int data type to bigint the fastest way

How do I do this without downtime, or at least minimal downtime (less than 3 hours)? Is there any way to tweak my.cnf to at least make this process of indexing and change data type faster? Below is ...
3
votes
1answer
60 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
62 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
71 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 ...
22
votes
4answers
5k 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
52 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 ...
5
votes
3answers
186 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
48 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
548 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
46 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
37 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 ); ...
7
votes
1answer
396 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
87 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
288 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
883 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
44 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
274 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
314 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 ...
2
votes
2answers
1k 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
171 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
34 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
487 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
60 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
438 views

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

Schema: Column | Type ----------------------+-------------------------- id | integer event_id | integer ...
0
votes
1answer
73 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
75 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: ...
13
votes
1answer
2k 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
354 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
114 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
63 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
283 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: (...