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
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

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.

10
votes
3answers
497 views

What is the appropriate index architecture when forced to implement IsDeleted (soft deletes)?

Currently, we have an existing database and application that is fully functional. I do not have the ability to change the architecture at this point. Today, each table in the database has an "...
0
votes
1answer
17 views

How to add a constraint to a table using a partial index?

I have a simple table: create table item ( lan text not null, disabled date ); My aim is to ensure uniqueness for the lan column over all rows, where disabled is null. I created a partial ...
4
votes
1answer
96 views

Okay to merge included columns on otherwise identical indexes?

I have two indexes that index the same field on the same table. The only difference between them is the included columns. There's no reason I shouldn't combine the included columns and just have one ...
1
vote
0answers
14 views

Spatial index unused on nested set

I'm using the nested set architecture to store URL hierarchies in the following InnoDB table on MySQL 5.7. From my research, I've learnt that using a SPATIAL index will significantly speed up any ...
-2
votes
0answers
17 views

Rebuilding Indexes [on hold]

I'm working with a db where I have tons of fragmentation issues. I'm rebuilding some of these indexes. Can anyone tell me how come the fragmentation of an index is not 0 after the rebuilding process. ...
1
vote
1answer
20 views

PostgreSQL Index Only Scan can NOT return ctid

I have this table in PostgreSQL 11: CREATE TABLE A (id bigint PRIMARY KEY, text text) Now I want to fetch the ctid of rows meeting some condition like id = 123. However, even using pg_hint_plan to ...
0
votes
2answers
31 views

Does adding a postgres index concurrently take up disk space gradually or all at once?

I'm adding a unique index (concurrently, over the course of hours or maybe even days, on a column of 5 billion bigints, all of them NULL) on a Postgres 9.6 server that's pretty low on disk space. I ...
0
votes
0answers
11 views

InnoDB locking: what does index record mean?

From MySQL documentation Record Locks A record lock is a lock on an index record. what does index record mean for cluster index? what does index record mean for secondary index? Is it a key-...
1
vote
1answer
22 views

Add an index on a new column

I'm adding a new column (bigints, initial values all null), to a large (~5 billion rows) Postgres (9.6) table. I want to add an index on the row (algorithm: concurrently), and I'm wondering if there'...
0
votes
2answers
28 views

How to index/optimize sum of two columns in PostgreSQL?

We have a database where one table contains serialized temporary data that needs to be kept for various times (usually between tens of minutes and two weeks). We also have a low priority background ...
1
vote
2answers
73 views

SQL Server Index and Statistics Maintenance performance question

I tried to use IndexOptimize stored procedure on a database with a very high number of indexes (> 250.000). The initial step where stored procedure collects data what needs to be processed takes hours,...
0
votes
1answer
8 views

gin_trgm_ops index creation fails

I'm trying to create a trigram index on a text filed containing names. I've added the pg_trgm extenion to the schema. Running \dx shows it is enabled: List of installed extensions Name | ...
-1
votes
0answers
23 views

indexable and none indexable compund predicates in query [closed]

I was reading about predicates being indexable or stageable then i found out that AND is indexable and OR is not indexable. why? I cant't find the reason why AND and OR behave like this. I tried to ...
0
votes
0answers
24 views

Indexing strategy for date range availability in SQL [duplicate]

Imagine the structure of a simple hotel booking app: hotel_chain(id, name) hotel(id, hotel_chain_id, name) room(id, hotel_id) booking(room_id, date_start, date_end) There are hotels, that belong to ...
0
votes
0answers
21 views

I need some help about indexing a JSONB column

I'm a newbie with PostgreSQL and databases in general. I have PostgreSQL version 9.6.8 and in it there's a schema with JSON data in it. Some of the fields are being indexed already, but there's a ...
3
votes
1answer
597 views

Aggressive Under-Indexing and no data for missing index

I know that there are lot of blocking on my database and have tried my best to get this sorted by vendor as this application is supported by them and hasn't produced any successful result yet. Every ...
0
votes
1answer
27 views

Help understanding mongodb explain results

I have an index on field f1. {f1:1} Running an explain on the following query db.coll.find() says that the winning plan is a CollScan (expected) However, for db.coll.find().sort({f1:1}) the ...
0
votes
1answer
21 views

Reduce page accesses of query

Assume that we have a secondary index for an attribute in a table and we want to execute a range query on that attribute. Recommend a possible approach to reduce the number of page accesses for ...
1
vote
1answer
24 views

Index partitioned table to prevent Postgres from doing a sequential scan

I have a Postgres database with a partitioned table that will contain ~2,000,000,000 entries. I have defined a partitioned database based on the first letter of the "identifier" - this is split into ...
2
votes
1answer
92 views

Indexing strategy for a query involving three views, multiple columns and several JOINs

The scenario I am having issues with a SELECT query on a view named V3, which takes ages (I had to cancel in SSMS, it timeouts in the application). V3 combines two other views, V1 and V2, to get a ...
2
votes
1answer
53 views

“CREATE INDEX” Background process in SQL Server

While analyzing our environment, I've noticed a recurrent blocking that usually happens around the same time, our monitoring tool says the blocked process is a "CREATE INDEX", however, the index ...
0
votes
1answer
33 views

SQL Server Threshold of Write per X when considering Indexes? [closed]

I am not entirely sure if this is even a question worth asking. However, I was curious as if there was some sort of standard, or specifically a metric, that some more seasoned DBA's use to gauge ...
1
vote
3answers
241 views

Suggestion on Missing Index Creation

I have been using sp_blitzindex and it is very very helpful(Thanks to Brent Ozar and Team). I executed this procedure against my database and below is the finding for one table falling in Indexaphobia ...
1
vote
0answers
29 views

Background index creation resource provisioning

I have a long running background index creation running in MongoDB. However, the CPU and RAM usage is below 30%. Is there a way to allocate more resources to the index creation process or is it ...
1
vote
0answers
22 views

Why MySQL not using index with where clause

I know many people have asked similar questions but i've gone through many of them and cannot find a similar one or at least not one that could help me understand the issue here. Here's the query - ...
3
votes
0answers
21 views

Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?

I have a big table (30 millions rows) defined like this: CREATE TABLE `drawing` ( `id` int unsigned NOT NULL, `artist` int unsigned NOT NULL, `imageUrl` text NOT NULL, `uploadDate` timestamp NOT ...
0
votes
0answers
26 views

How can I optimize this MySql Select query?

I have the following query executed every 1 minute on a cron job SELECT COUNT(*) FROM `Orders` AS `o` WHERE (`o`.`OrderStatus` = 0) AND (`o`.`CreatedAt` > DATE_ADD(UTC_TIMESTAMP(), INTERVAL -1....
1
vote
2answers
36 views

Index options and applying partitioning to an index

I have following two questions: I have a SQL Server index with the following options: PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF SORT_IN_TEMPDB = OFF DROP_EXISTING = OFF ONLINE = OFF ...
0
votes
0answers
25 views

Combine different index types in postgres

we have query for fulltext where we have also another condition. Can you tell me what is correct usage of multiple indexes of different type? for instance: select * from foo_tbl foo where foo....
0
votes
1answer
25 views

Subtract values in same column from 2 different dates in same Mysql table

I have a MySql table like: Item Date Likes Book 2012-05-20 402 Book 2012-05-21 432 Book 2012-05-22 450 Pen 2012-05-20 20 Pen 2012-05-...
0
votes
2answers
33 views

Poor performance on a 'leaderboard' query

Consider the following 2 tables: players: | rooms: id | id name | type room_id | embed_code_id points | My query needs to fetch the 100 players with the most points from a given ...
0
votes
1answer
24 views

What's the most appropriate type of index to use for this function-based query?

I'm using Postgres 9.5. I'm going to be running a lot of this kind of query SELECT * FROM mydb_article where regexp_replace(url, '\?.*$', '') = :url_wo_query_string so I figured I would need to ...
3
votes
1answer
271 views

How to determine the cost of index creation?

I have a table that I want to create an index on to improve the performance of a couple of SELECT queries that use the table. How can I test whether the index has had any significant detrimental ...
0
votes
1answer
32 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
44 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
42 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
38 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
118 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 ...
2
votes
1answer
338 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
34 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
67 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
33 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
219 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
53 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
1answer
28 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 ...
1
vote
1answer
33 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 ...
0
votes
1answer
105 views

Postgresql: 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
20 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
29 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
80 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 ...