Skip to main content
The 2024 Developer Survey results are live! See the results

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
0 answers
3 views

How to Create a Case-Insensitive Search Index for Multiple Fields in MongoDB Atlas Search

I'm working with a MongoDB collection named skus and need help creating a search index and query for case-insensitive search across multiple fields with priority. Here's a sample document from the ...
0 votes
1 answer
36 views

Trigram index is slow for common terms

I have a PostgreSQL database with bunch of string columns in a table with a trigram index on them using pg_trgm. They are queried like this SELECT [...] FROM mytable WHERE mycolumn ILIKE '%searchterm%'...
1 vote
1 answer
677 views

Simple query with a single join very slow

I have this very slow, simple query that joins a large table (~180M rows) with a smaller table (~60k rows) with a foreign key, filtering an indexed column on the smaller table, ordering by the primary ...
146 votes
8 answers
239k views

Pattern matching with LIKE, SIMILAR TO or regular expressions

I had to write a simple query where I go looking for people's name that start with a B or a D: SELECT s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering if ...
0 votes
2 answers
733 views

Creating a specific index tree structure from keys that have the tree-structure baked in? Indexing H3 Hexes

Preface: I am NOT a DBA, please be gentle on my terminology and explanations. I have data that already has a structure baked into it and need to create an index in my DB that represents the ...
14 votes
1 answer
14k views

Why *not* ERROR: index row size xxxx exceeds maximum 2712 for index "foo"?

We have repeatedly seen failing attempts to index columns with values exceeding a maximum size. Postgres 10 has this kind of error message for it: ERROR: index row size xxxx exceeds maximum 2712 for ...
4 votes
1 answer
1k views

AWS Aurora Mysql seemingly not picking PRIMARY or secondary index

Relevant System Info: Aurora Mysql 8.0.mysql_aurora.3.03 rg6.xl instances (1 writer, 2 read replicas) Total size:5.5TB (all databases combined, or just looking at the most recent Snapshot) I have been ...
0 votes
1 answer
397 views

Poor performance on MySQL 5.7 on joined tables ( scanning wrong table ?)

I have a joined table (News, Publishers) with indexing. The query is working fine on MySQL 5.5. After I upgrading one of the server to MySQL 5.7, I start noticing high load, high CPU, and slow query. ...
1 vote
2 answers
3k views

Find MySQL InnoDB index corruption

I have large MySQL InnoDB table on my localhost. In this table primary key is id, unique index is set on fields a, b, c. When I run query: SELECT * FROM mytable WHERE a = 1 AND b = 2 AND c = 3; the ...
0 votes
1 answer
28 views

Performance Impact of Partial Index on INSERTs Not Involving the Partial Match

Given: postgres=# create table testing(a int primary key, b int not null); CREATE TABLE postgres=# create index on testing (b) where b = 0; CREATE INDEX postgres=# \d testing Table "...
0 votes
1 answer
627 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: ...
2 votes
2 answers
93 views

Automatic index creation - too many

When we have the auto tuning set to ON for index creation, the Azure SQL Hyperscale db has created about 10 indexes for some of our large tables. In analyzing the indexes, some of them differ only in ...
0 votes
1 answer
48 views

How to create a table with only indexes and no actual data?

Question: Is is possible to create a table in PostgreSQL for the sole purpose of creating specialized indexes (used for advanced search use-cases), without storing duplicate data in such tables? ...
1 vote
1 answer
609 views

Table is full even with innodb_file_per_table

I am trying to create an index to my table using alter query. My my.cnf file innodb_data_home_dir = /usr/local/mysql5/data innodb_data_file_path = ibdata1:60021538816;ibdata2:300M;ibdata3:30000M;...
57 votes
3 answers
52k views

What is the optimal data type for an MD5 field?

We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute). There is a table names that serves as a sort of central registry. Each row has a ...
0 votes
1 answer
4k views

"Cannot continue the execution because the session is in the kill state" when querying system tables

This is a snippet from some code I run on a 2012 (SP3-CU9) (KB4016762) - 11.0.6598.0 (X64) system that I just inherited and have not yet had a chance to fully patch. AlwaysOn AG are not enabled, and ...
0 votes
1 answer
1k views

mysql creating sort index making queries slow

We have a view table the query for the view is pasted below. The problem is that whenever we try to query items view our database start making sorting index which make the over all time of our ...
0 votes
0 answers
47 views

Not able to drop non-clustered index

I was executing a query to create a non clustered index in one of the table and that has around 43M of rows. It runs more than 1 hour, then I felt something is wrong and have cancelled the query ...
1 vote
1 answer
54 views

How a B-Tree index on a columnar table would look like?

Greenplum database supports B-tree index on append-optimized columnar tables which allows UPDATE operations as well . Even though it's not a recommended practice to have index on such tables(probably ...
1 vote
1 answer
356 views

EAV or JSONB column for dynamic data that needs good indexing

I have some data in a Postgres database that does not fit into a static schema. There is a table "content" and each piece of content has a user-defined type. Each type can have different ...
1 vote
3 answers
574 views

Is there a mechanism inside of database engines to avoid collisions of non-sequential keys? If so, how does it work?

Say that I create a table that that has a random uuid for a primary key. Is there typically a mechanism inside of the database to gracefully deal with collisions? How does it work? My mind is ...
0 votes
1 answer
1k views

MySQL How to Script to create indexes on all date columns in database

I have a lot of tables in the database that are using a datetime stamp - I need to create a script that will create indexes on all columns in each table that does not already have an index . So far I ...
0 votes
2 answers
335 views

Understanding of the %%lockres%% and %%physloc%% pseudo columns with index hint

I am experimenting with clustered and non-clustered indexes in SQL Server and I have noticed something interesting. Here is a simplified version of my T-SQL code: IF OBJECT_ID('dbo.dept') IS NOT NULL ...
1 vote
2 answers
833 views

mysql - large table - index performance and query issue

I have simplified a much larger table below: CREATE TABLE `core` ( `id` int NOT NULL, `loc_country` enum('United States','Colombia','United Kingdom', 'Australia','India','Germany','...
1 vote
1 answer
86 views

What is the impact on an index when I am widening a key varchar column

One of the central tables in my database has a varchar(25) column named EmployeeID. This column is used within a compound index and is likely the most used index in our system. We got an enhancement ...
3 votes
1 answer
3k 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 ...
0 votes
1 answer
50 views

When generating combinations using a CTE, is there a way to use an index to group the elements of each combination?

I have a project that involves combining a variety of things into their possible combinations. This includes permutations, combinations, with and without repetition, and some more exotic ways of ...
1 vote
1 answer
65 views

Creating indexes before bulk-loading data is weirdly much faster than vice versa

EDIT: An important thing that I missed is the fact that I parallelize COPY operations by N number of cores, by splitting the data file into N equal parts, and issuing N requests through separate ...
0 votes
1 answer
38 views

Postgres - Improving multi-column GIN text search performance

I've got a schema for customer records a bit like: account_id - UUID name - text I currently have a GIN index that looks like: ... USING GIN (account_id, name gin_trgm_ops) We have a mix of accounts....
29 votes
3 answers
25k views

How to know when/if I have too many indexes?

Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement..."). From my understanding every added ...
1 vote
1 answer
675 views

Optimize join without denormalization

I have two tables: orders(order_id, scan_timestamp) and order_products(order_product_id, order_id, pick_number, quantity) and I want to know how many of a particular product (identified by ...
1 vote
2 answers
488 views

The UPDATE queries is 50x slower than its SELECT query

The UPDATE queries is 50x slower than its SELECT query. I have a table named sync_read and the have the following columns id club_id created_at user_id queue_id The table have the following Indexes: ...
0 votes
2 answers
2k views

How to speed up counting and filtering on table with a lot of columns and filter conditions?

I have TABLE_A in my database that has a lot of columns. Let's say there are 100 columns: COLUMN_1, COLUMN_2, COLUMN_3, .. COLUMN_100 There are so many columns not because of denormalized design, ...
0 votes
1 answer
604 views

Mysql/Mariadb - Speed up query with varchar columns already indexed but index its first 2 characters (USxxxxxxxx, CAxxxxxxxx, etc)

On my project (MariaDB 10.3), I use a table for storing data per ISIN code (a financial asset identifier). For instance, "US3696041033" for General Electric. I have 4 Millions rows in my ...
9 votes
1 answer
588 views

Does Ordering of Columns in INCLUDE Statement of Index Matter?

I was reading through the comments of this answer on another question, and it got me wondering if the ordering of columns in the INCLUDE clause of an index (on SQL Server specifically but also any ...
1 vote
2 answers
792 views

MongoDB - Slow aggregate query

It takes around 6min to get the result from MongoDB, when I use the following aggregate query. db.barcodes.aggregate([ { $lookup: { from: 'company', localField: '...
12 votes
2 answers
42k views

Find rows containing a key in a JSONB array of records

I'm trying to query for a key present in an array of objects. This structure: column jdata {"name": "Somedata", "array": [ {"name":"bla1", "attr&...
0 votes
1 answer
21 views

MongoDB createIndex compound unique index on array field

I'm trying to create a unique index in MongoDB that enforces uniqueness based on the existence and non-undefined nature of a field (barcodes) in my skus collection. Here's the index creation query I'm ...
0 votes
1 answer
671 views

How to conditionally recreate indexes created by `CREATE INDEX ON <table_name> (<column_name>)`?

I am squashing migrations and rewriting all constructs like: CREATE TABLE entities ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL, description text ); CREATE INDEX ON ...
1 vote
1 answer
708 views

Does innodb guarantee unique data in unique indexes even with UNIQUE_CHECKS=0?

We have a suspicion that sometimes there could be non-unique data even with a UNIQUE index. Question is, can we rely on InnoDB guaranteeing that there won't ever be non-unique data, even in the ...
0 votes
1 answer
26 views

When is re-indexing triggered after multiple updates

If I have a table in a PostgreSQL database and I want to perform a few thousand of the following statements: UPDATE table_name SET indexed_col='some', other_col='values' WHERE pk='row-slug'; UPDATE ...
0 votes
1 answer
59 views

How to Make Queries on a DATETIME Column Efficient If My Primary Query Pattern is an Hour?

Context Here is the DDL that I am intending to use to define the table for a logistics/delivery company. CREATE TABLE scraping_details ( id INT IDENTITY(1,1) PRIMARY KEY, -- Identity insert and ...
0 votes
2 answers
492 views

Sort index doesn't work for MySQL

I have a table with more than a million records. I get the data by using LIMIT, but also ORDER BY. The latter causes a huge performance problem. My query ends with this: ... ORDER BY `record`.`...
0 votes
2 answers
421 views

Oracle over-indexed tables

I am trying to improve performance of a view in Oracle 11g, the view joins 11 tables, the 3 biggest tables in the query are 40, 27, 19 milions of rows. I found that each of these big tables has more ...
1 vote
1 answer
809 views

MariaDB: Duplicate entry for key on OPTIMIZE with no duplications

There is a table named contracts with following structure: MariaDB [db_name]> DESCRIBE `contracts`; +-----------------------------+--------------+------+-----+---------+----------------+ | Field ...
4 votes
1 answer
2k views

Why does postgres choose to sort instead of scanning the index?

I have the following table: # \d service Table "public.service" Column | Type | Modifiers -------------+----------+----------- customer_id | integer | not null date | date ...
0 votes
2 answers
500 views

Trying to find a way fit an extremely large index in to memory

Description: I am having trouble with the on-growing data size in my MySQL database. I am using Ejabberd and MAM function which will make use of an archive table to store messages sent between ...
0 votes
1 answer
1k views

pt-online-schema-change error creating triggers

I am trying to add an index with pt-online-schema-change. The dry-run succeeds, but I am stuck at creating triggers. Any help ? pt-online-schema-change --alter "ADD INDEX domain_index (domain) USING ...
0 votes
2 answers
35 views

Postgres query planner suddenly changing his mind

My Postgres table has a range column containing timestamps with time zone. I have created an index on the lower bound of the range, like so: CREATE INDEX bdg_sys_period_start_idx ON building USING ...
0 votes
0 answers
49 views

Indexing strategy for a very read-heavy application with most business logic managed by the database

We're working on migrating from a NoSQL database to Postgres. Our entire business logic (complex network calculations) is handled by the database – we have approx. 150 tables, 300+ dynamic views (...

1
2 3 4 5
67