We’re rewarding the question askers & reputations are being recalculated! Read more.

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

Why does a query on a sparse index perform much faster than a query on a partial index in MongoDB?

I created a sparse index in my MongoDB Atlas like so: db.detection_events.createIndex({"emp_id": 1}, {sparse: true}) When I performed this operation: db.detection_events.count({"emp_id": {$exists: ...
3
votes
1answer
134 views

Did adding JSONB indexes bloat the database?

We are running: user@host:~$ psql -d database -c "SELECT version();" version --------------------------------------------------------...
0
votes
0answers
66 views

sys.dm_db_index_usage_stats showing incorrect figures for last_user_update

As the title suggests, I seem to be getting unexpected readings from the last_user_update column in the index_usage_stats DMV. It seems to update the datetime value everytime I run a script inserting ...
0
votes
0answers
20 views

After upgrading from PG 9.3 to 10 indices became corrupted

So recently I was upgrading the database in production from Postgres 9.3 to 10. In addition to upgrade, we wanted to move the database to another machine. I know, doing it "in place" would be easier ...
1
vote
1answer
60 views

Are idx_scan statistics reset automatically (default)?

I was looking at the tables (pg_stat_user_indexes and pg_stat_user_tables) and discovered many indices that are not being used. But before I think about doing any operations to remove these indices, ...
0
votes
0answers
44 views

Slow insert (trigger/indexes) processing

Whenever an insert is done in a table these tasks are executed: One Trigger (see down). Three indexes: one checks that a primary key is unique and the rest makes sure that two columns don't have ...
0
votes
1answer
37 views

mysql 5.6 is Not referring Indexes for deterministic function DATEDIFF

I do not understand why MySQL is not using the index for the below query. I have a table named users and its create table is this table contains data. Now I am running SQL select statements and it'...
2
votes
2answers
60 views

Define JSON Index for Schemaless using MySQL 8.0

We are using MySQL 8.0 and need to implement Schemaless Feature where we allow Vendors to create a Module(ObjectName) and add columns(int, string, currency) to it. Vendor can add objects after ...
0
votes
0answers
63 views

Is there a way to make postgres “COUNT WHERE” query log(N)?

Afaik, now this request SELECT COUNT(*) FROM user WHERE rating > 50 looks like this: find an item with the value of rating 50(or greater) in our b-tree index, pretty fast. Iterate though all items ...
0
votes
1answer
74 views

How to optimize a SQL Server query to fetch the results faster

Currently I am having a situation where I have to import around 0.5 Million rows into a SQL Server table named BARCODE_MANAGEMENT, which has the following columns: ID int (identity column), SETUP_ID ...
2
votes
0answers
40 views

the cost decreased but the time was practically the same, why?

Table definition: CREATE TABLE schema.mylogoperation ( id_mylogoperation serial, data DATE, myschema VARCHAR(255), column_var_2 VARCHAR(255), user VARCHAR(255), action TEXT, ...
1
vote
1answer
461 views

Creating index on TIMESTAMP column to use with range operators

TLDR: Can I create an index that's used by the following WHERE clause: WHERE foo_date <@ tsrange('2018-01-01', '2018-02-01') Let's say I have a table like this: CREATE TABLE foo ( foo_id ...
0
votes
1answer
63 views

Simple insert statements are taking so long

I have a scenario where one main stored procedure calls a nested procedure and that nested procedure contains simple insert statements for multiple tables and almost every insert is taking so long I ...
0
votes
0answers
40 views

Does having unused columns effect the indexing of the table

I have a table where there are some columns which are empty and they are not used. That table has to be indexed multiple times based on the application. Does having these empty columns in my table ...
0
votes
0answers
63 views

How is data accessed with unique index in MariaDB? (NULL r_rows on ANALYZE)

I'm using MariaDB 13.3.14 I have a very simple test table with an email column. And I am using ANALYZE to see the behavior of the indexes. If I understand correctly the output of ANALYZE, rows ...
0
votes
2answers
32 views

[Solved]MySQL index work in one table and not work in another table when using IN

I was asked if the index is working or not when we use IN keyword in MySQL like below: //suppose col has index and the value is almost unique SELECT * FROM yourtable WHERE col IN('xxx','yyy','zzz');...
0
votes
1answer
29 views

Should I use Indexes on mySql table?

I have a table in mysql, in which searching, insertion, deletion and updation is done quite frequently and I have read that indexes affection insertion and updation performance. so should I use ...
1
vote
1answer
123 views

passing the parameters of sp_executesql to sp_ineachdb or sp_foreachdb

I am often in need to run the sp_executesql in the context of different databases I would like to add the same parameters as the sp_executesql into the sp_ineachdb But when I look at this: -- Syntax ...
2
votes
1answer
235 views

Indexes with the same object_id but different names

When I run SELECT count(object_id) AS count, object_id, min(name) as name1, max(name) AS name2 FROM sys.indexes GROUP BY object_id HAVING COUNT(*) > 1 ORDER BY count I will get a list ...
16
votes
5answers
1k 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 "...
1
vote
1answer
49 views

Is there any way to find information of index fragmentation, Statistics information with the help of system tables in SQL Server

I am working on a report which will give information for different segments like blocking process slow running queries disk space etc. I also want to add one more segment where I can get ...
0
votes
1answer
26 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 ...
5
votes
1answer
100 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
32 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 ...
1
vote
1answer
65 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
58 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
67 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
26 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
97 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 ...
3
votes
2answers
153 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
426 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 | ...
0
votes
0answers
30 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
25 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
768 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
51 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
24 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 ...
2
votes
1answer
346 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
99 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 ...
1
vote
1answer
362 views

“CREATE INDEX” Background process in SQL Server [duplicate]

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
35 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
338 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
53 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
132 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
1answer
29 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
27 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
52 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
29 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
920 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
51 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
27 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 ...