All Questions

Tagged with
Filter by
Sorted by
Tagged with
1
vote
1answer
23 views

Understanding composite BTREE + GIN_TRGM_OPS index prioritization & odd lower() behavior

hoping someone can try to help me decrypt some index behavior. I'm working on enabling some simple contains type lookups on various user-data columns (~varchar < 255) and trying to understand the ...
0
votes
1answer
52 views

Why is this datetime index not being used?

I have a table like this: CREATE TABLE TestTable ( [TestTableID] [int] IDENTITY(1,1) NOT NULL, [IntField1] [int] NOT NULL, [IntField2] [int] NOT NULL, [IntField3] [int] NOT NULL, [...
2
votes
1answer
22 views

Postgres not using multicolumn index for queries on leftmost columns?

I'm working in Postgres 9.5.15 and have a table called project: id | integer | | not null | nextval('project_id_seq'::regclass) user_id ...
1
vote
1answer
84 views

Do I need to update an index include columns every time I add a new column

I work on a team where we have millions of records of data, but not 100s of millions anything crazy. We have had recent DB issues where we have hung connections due to Page Latch IOs, where the DB is ...
-3
votes
1answer
39 views

Why we can't have more than one inequality condition in MySQL indexing? [closed]

I am recently studying about database indexing in MySQL. I read some articles and found a great web page: http://mysql.rjweb.org/doc.php/index_cookbook_mysql I read in this page and some others that ...
1
vote
1answer
38 views

How to make Postgres Multi-Column and Multi-Table Search More Efficient

I have a Shipment table with some basic data about a shipment and a ShipmentItem table which contains additional attributes about that shipment with a foreignKey on the Shipment table's primaryKey. ...
7
votes
2answers
381 views

Force SQL Server to use fragmented indexes?

I have a quite large table (>10M rows) with frequent crud operations. It has proper indexes, but they get fragmented quickly. Without a periodic index reorganize/rebuild maintenance plan, the index ...
4
votes
1answer
97 views

Add INCLUDE term to primary key index in Postgres 11

I work with Postgres 11 and would like to change the index that backs the primary key of a large table (~750 million rows). The primary key is the bigint column id and I would like to include an extra ...
0
votes
1answer
30 views

Why is DTA recommending non-clustered indexes that match the clustered Primary Key index?

I'm only vaguely familiar with SQL Server's Database Engine Tuning Advisor (DTA) but I'm using it to suggest recommendations for indexes on my database. Most of the tables in the database at present ...
0
votes
1answer
31 views

Optimization for table with text and time columns

I have a simple table with the following columns: id (int4) time (timestamp) name (varchar) service_name (varchar) For every service (there are not more than 500 different names), there are a lot of ...
0
votes
1answer
51 views

Postgres index not being used

Postgres 9.6.8, a table with 6GB including a jsonb field that is up to 15MB. The query planner is not using any indices btree or gin when querying that table. When I add the same indices to a local/...
0
votes
2answers
39 views

No index used warning in WHERE column IS NULL

I'm getting this warning: No index used in query/prepared statement SELECT * FROM mTable WHERE my_column IS NULL But the my_column is already indexed. MariaDB [mytest]> SHOW INDEX FROM mTable; ...
0
votes
1answer
33 views

Indexing every WHERE column?

I tried to turn on MYSQLI_REPORT_ALL | MYSQLI_REPORT_STRICT and now MySQL tells me: No index used in query/prepared statement... in some of my app's queries. Is it really best practice to index ...
3
votes
0answers
32 views

Will my database index be more performant with a smaller varchar size?

We're using Mysql 5.7 We have an index over a varchar(255) column in a table of ~ 500M rows. select distinct varchar_column from table yields seven results, with the max length 21. A DBA consultant ...
1
vote
0answers
60 views

How does mysql optimizer works?

I have two tables t_core_blueprints,t_ci. CREATE TABLE `t_core_blueprints` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `last_modified_on` ...
1
vote
0answers
33 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 ...
0
votes
2answers
108 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
0answers
54 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 ...
3
votes
1answer
30 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 ...
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
386 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
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 ...
7
votes
1answer
399 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 ...
0
votes
1answer
65 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 ...
1
vote
0answers
387 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 ...
-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 ...
1
vote
1answer
67 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 (...
1
vote
2answers
89 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
75 views

Composite index design where only a subset will be queried

I have a table with columns A (typedate), B1 ... B24 (all boolean). Generally, A will always be used in every query and only one of B1 ... B24. A large subset of the B columns (more than half) will ...
0
votes
1answer
31 views

Primary index is being used, not the one which is on the field present in the where condition

I have 2 tables, one is category table and has below coulmns and has a primary index on cat_id column. cat_id (primary key), acc_id, islive Another table is cat_brand table which has below fields ...
-1
votes
1answer
50 views

Adding Multiple Individual Indexes vs One Combined Index for Multiple Foreign Keys

Background I have a "join" table that is used to join one "transactional" table with a "category" table based on multiple columns (and through several joins): TxnTable(Entity1Name, Entity2Name, ...
3
votes
0answers
154 views

Best Way to Index Multiple Effective Date Columns

I've got a table that is primarily to represent relationships between entities (i.e. mostly composed of foreign keys). These relationships change over time, and so the table has a StartDate and an ...
0
votes
1answer
29 views

I need help creating an index for timestamp

I have a query taking too long. This is the table: mysql> describe parking_data; +----------------+--------------+------+-----+-------------------+----------------+ | Field | Type ...
1
vote
3answers
40 views

Correcting a database that has had indexes and statistics created on it willy-nilly

I've inherited a DB and I believe it was the victim of DBAs who encountered random slow operations, ran the tuning adviser for individual queries and blindly created the recommend indexes and ...
1
vote
0answers
28 views

Index to facilitate joining a table against itself to gather the last observation

I have a discrete step timeseries table where I insert about 1M rows per "month end" with the identifier being the last day of the month (2018/02/28 or 2018/08/31). I try to keep as many months as ...
3
votes
3answers
120 views

Is there any way to test which clustered index is better for specific queries?

We've been querying and processing a growing table of events in our system, which was fine without specific indexes for now. However we've noticed a decrease in performance and are wondering what we ...
0
votes
2answers
46 views

Define MYSQL index with condition

I have a table with these schemas: CREATE TABLE `transactions` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(20) NOT NULL, `description` varchar(255) NOT NULL, `is_deleted` ...
-2
votes
1answer
59 views

Can rebuilding an index create fragmentation?

Its my understanding that when you rebuild an index SQL will sort the pages into order at the end of the data file, growing the data file until all pages are ordered and then drop the old index ...
1
vote
1answer
678 views

Indexing strategy for VARCHAR2 LIKE search

Scenario Database Oracle Database 11g Table Users ID Number, PK CODE Number, Indexed, Unique, Not null FIRST_NAME VARCHAR(100), Not null LAST_NAME VARCHAR(100), Not null EMAIL VARCHAR(50), Not ...
1
vote
3answers
318 views

How to remove multiple redundant indexes?

I have a legacy app. I wanted to find out which tables are the largest ones using script from Percona blog. It turned out some of my tables have indices that are larger than actual data by factor of 3 ...
-2
votes
1answer
46 views

Index not hitting while querying [closed]

I have a query on a table tab and this table has a column a: select * from tab where a = :a; but the index is not hitting. Please let me know what could be possible reason (It was asked during an ...
0
votes
1answer
22 views

Mysql - optimal indexing for query using IN operator

I have this query: SELECT * FROM table1 WHERE foo IN (1,2,3,4) AND bar = 123; I have 3 indices: ALTER TABLE table1 ADD INDEX IDX_foo (foo); ALTER TABLE table1 ADD INDEX IDX_bar (bar); ALTER TABLE ...
0
votes
2answers
125 views

a filtered index to support a query - is it possible to consider nulls?

I have been dealing with this query: SELECT @amount = sum(accountAmount) FROM dbo.accountTransaction WHERE invoiceID = @invoiceID AND (notCountedInTotal = 0 OR notCountedInTotal IS NULL) ...
0
votes
1answer
686 views

postgres 9.6 - jsonb & indexing - poor performances

On a table (used by a django model) I'm using jsonb column data to store arbitrary data fetched from a webservice: abs=# \d data_importer_rawdata; Table "...
2
votes
2answers
131 views

Combine indexes to optimize “OR” queries

I know that since version 8.1 Postgres can use multiple indexes for queries with or at query time. I wonder if it is possible to combine multiple columns into one index column at index time. That is ...
1
vote
1answer
39 views

Change an index order from ascending to descending

I have a table index in my postgres 9.6 db that I think would be more efficient if the table was sorted descending rather than ascending (as it currently is). The data that I want to test is not in ...
4
votes
2answers
994 views

SQL deadlock on nonclustered key caused by two INSERTs and a CHECK CONSTRAINT on the same table

Been struggling with deadlocking on a table during INSERTs. It's a multi-tenant database and Read Committed Snapshot Isolation (RCSI) is enabled. There is a CHECK CONSTRAINT upon INSERT to ensure ...
1
vote
1answer
371 views

Index by subkey inside subkey inside array in jsonb

I have a jsonb field registration that contains an array of objects. I need to create two separates indexes to look for id, by example: d61802ff-3eec-4a72-97ca-832f51b96bf0 = [ .tour_id.id ] f492b26d-...
1
vote
1answer
422 views

How to create some GIN index concurrently in Postgresql

I have a large table: CREATE TABLE hh(h int8[] not null, file int8 not null), and GIN-index over h field (CREATE INDEX ON hh USING gin(h)). Index was created is about 8 minutes, so I decide to split ...
0
votes
1answer
45 views

Best index for a join table?

I have two tables that are joined using a join table. I tried to found the best index(es). DROP TABLE IF EXISTS jobs CASCADE; DROP TABLE IF EXISTS devices CASCADE; DROP TABLE IF EXISTS jobs_devices ...