Stack Exchange Network

Stack Exchange network consists of 174 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

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.

0
votes
0answers
18 views

Stored Procedure infinite looping after index updates

I have inherited a very old and poorly written stored procedure with nested fetch statements. This code processes a large amount of data by copying from one database into another. This process was ...
1
vote
1answer
33 views

Indexing for a table with many large subsets

In SqlServer 2017 we have a table with millions of rows. The table has a few dozen columns. One of the columns is a varchar(50) with a well defined range of allowed values, the value of this column is ...
1
vote
1answer
43 views

Oracle Optimizer refuses to use my index

I have a relatively simple query that SELECT data in a table. SELECT l.columnRecovered FROM schema.TABLE l WHERE (column1 = :a or (:a is null AND column1 is null)) AND (column2 = :b or (:b is null ...
1
vote
2answers
43 views

Live update index in production database [on hold]

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

What index strategy to use to improve multi-condition SELECT?

As far as I understand, indexes should be selected on fields with high cardinality of data - e.g. sex only has two values ( male and female ) so low cardinality, whereas something like sha256sum field ...
0
votes
0answers
22 views

Do database restart affect create Index suggestion/statistic?

Do database restart affect create Index suggestion? If I restart database then right after running the sp_BlitzIndex @GetAllDatabases = 1 is it going to give an accurate index statistic? If no, how ...
0
votes
1answer
40 views

Index Seek Plan turns to Index Scan after adding another Join condition [closed]

I had a query passing from Entity Framework which is a very expensive query with a terrible performance. while optimizing this query I found the problem in this part: SELECT TOP 20 * FROM [...
0
votes
1answer
34 views

Is there a situation where MySQL creates a nonclustered index automatically?

MySQL creates a clustered index automatically when I create a primary key. But is there a situation where MySQL creates a nonclustered index automatically?
1
vote
1answer
42 views

MySQL not using indexes

My query does not seem to bother for using indexes. Collations are the same on each table. EXPLAIN SELECT SUM(it.revenue) FROM invoice_items AS it INNER JOIN invoice AS i ON i.id = it.invoice_id ...
2
votes
1answer
623 views

Do primary keys change when indexes are rebuilt or reorganized?

I have a database containing tables that each have auto-increment int columns serving as the primary key column. When I do a rebuild or reorganize operation on my indexes, does either operation ...
1
vote
1answer
109 views

postgresql rename index

I want to understand how safe is the rename command: ALTER INDEX old_name RENAME TO old_name; The index was created by command(for the foreign key column): CREATE INDEX old_name ON table_t1 (...
-3
votes
0answers
15 views

How to determine the index build time

I'm got a large table with many indices. I built them last night. I need to know how long it took to build them. Is there some way to determine the creation time for an index?
1
vote
1answer
39 views

Does an index improve the querying performance in just two ways? [closed]

As far as I know, an index improves the querying performance in the following two ways: The index only contains the values of the column that it was created for and pointers to the rows in the ...
0
votes
0answers
13 views

MySQL - Indexing of a database is very slow

I have a MySQL database. Starting from this month, the indexing lasts up to 1h30m. Previously it lasted only 8 minutes. How can I speed up this process? I am new in MySQL, and I need help diagnosing ...
2
votes
2answers
114 views

Clustered Sequential GUID Primary Key vs Non-Clustered GUID and Clustered Sequential ID Primary Keys

I'm trying to figure out what the optimal solution is in regards to size and performance when using GUIDs as a primary key. The main reason for wanting GUIDs is that it's more appropriate in our micro-...
8
votes
1answer
1k views

TOP(1) BY GROUP of very huge (100,000,000+) table

Setup I have a huge table of ~115,382,254 rows. The table is relatively simple and logs application process operations. CREATE TABLE [data].[OperationData]( [SourceDeciveID] [bigint] NOT NULL, ...
1
vote
0answers
33 views

DeadLock - Two Process Locked Same Index

We have met a scenario that two process acquired exclusive lock on same index ans the same time two are requesting for shared lock to other process which eventually leads to dead lock. I am still not ...
7
votes
1answer
79 views

PostgreSQL ANALYZE execution time over 24h (still running)

I upgraded a Postgres DB 9.3.2-->10.5 using pg_upgrade (in place). I did everything according to the documentation and the instructions given by pg_upgrade. Everything went fine but then I realized ...
1
vote
0answers
16 views

How long does it take for Cassandra to create an index? [closed]

I couldn't find anything on this. Is there a progress bar or whatever to check how long a secondary index creation will take?
0
votes
1answer
26 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 ...
1
vote
1answer
32 views

MariaDB uses “wrong”/irrelevant Keys on large table

We have migrated our database from a local hosting company to AWS. Now when we run a particular query on AWS it finishes in about a minute (processlist says "sending data") where on the old server the ...
0
votes
1answer
42 views

REINDEX or REBUILD the indexes of a table after truncating it and then repopulating it?

Is it necessary to REINDEX or REBUILD the indexes of a table after truncating it and then repopulating it? I have a table with approximately 3.5 million rows in Oracle. Every week new data is ...
0
votes
1answer
24 views

Postgres jsonb complex query

I have a table on PostgreSQL 10 that looks like: CREATE TABLE forms (id serial, form jsonb); INSERT INTO forms (id, form) VALUES ( 1, '"permissions": [ {"name": "perm-name-1", "who":["group1", "...
2
votes
1answer
42 views

Query processor could not produce a query plan because of the hints defined in this query

There is a similar question, but it is not the same: The query processor could not produce a query plan I have the following query, and the following filtered index, and I cannot see any reason why ...
0
votes
1answer
25 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
vote
1answer
245 views

“The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.” after adding indexes to temp tables

Recently, I added some indexes on temp tables to procedures which prepare data for data warehouse. On a testing db, tests of these procedures worked fine but when running on client's server it failed ...
2
votes
1answer
31 views

Secondary composite-key pages access

we have this relation Store: [SID, City, EID] Employee: [EID, Name, Salary, SID] Article: [AID, Name, Producer, Price] Inventory: [AID, SID, Count] Invoice: [IID, SID, Customer] Item: [IID, SID, AID, ...
-1
votes
0answers
26 views

Should I add `date` to my multicolumn primary key (PSQL data warehouse)?

date isn't needed to uniquely identify rows, but it is used in almost every query. I could add a separate index on date but wondered if maybe I should just add it as the first term in the primary key ...
0
votes
1answer
47 views

What and where to index, rapid increasing table postgres

I work as a software engineer with python and django. Currently I am struggling with a design choice made before my time. We have a transaction table that logs all customer activity. Due to the ...
0
votes
0answers
30 views

Index creation / Query Optimization : for a group by + SUM query on mysql

I have a partitioned table with the following schema: CREATE TABLE sales_report ( sales_report_id bigint(22) NOT NULL AUTO_INCREMENT, report_date date DEFAULT NULL, cust_id int(11) DEFAULT ...
0
votes
2answers
31 views

mysql v5.5 index performance

On a 5.5.23 server (that needs to go to 5.7 at least, I know) I have an InnoDB table with 3 columns (there are a few more that are not relevant here): TimeC (bigint, unix timestamp) id (integer) ...
1
vote
0answers
34 views

POSTGRESQL REINDEX INDEX Time estimation

to explain the context, I test postgres with a huge table (I try to have 1TB data) in a docker container for isolation, and I ran my container with a -v parameter in order to mount my dedicated HDD (...
-4
votes
1answer
26 views

About the Index in MYSQL [closed]

I have a question about indexes in MYSQL. I have a table, DIM_BANK. The primary key is the column ID_BANK. MySQL automatically created the an index for this with the following attributes - BTREE (...
-1
votes
1answer
27 views

Mysql - index - optimization (two separate or a multikey)

I have a question for the experts I couldn't find an easy answer in the docs: I'm using pseudo code as the real table doesn't matter so much on the theoretical question itself. Given I have a table ...
2
votes
2answers
43 views

Duplicate index - Same key, one with INCLUDE columns, which one to drop?

I have 2 indexes that have the same key, but one also has INCLUDE columns. Which one can I drop? They are the same, except IndexInclude has 6 INCLUDE columns. Both have some reads (IndexNoInclude ...
1
vote
0answers
23 views

optimise computation of analytics from mongodb collections specific to user [closed]

I am looking for some ways to optimise computation of analytics from mongo v3.6.7 collection s specific to user. For eg lets say i have 3 mongo collections and I want to generate a report 1 for a user ...
0
votes
2answers
63 views

mySQL query optimizer not using indexes

In our production environment (mySQL 5.6.40 on Amazon RDS), for certain queries the query optimizer ignores table indexes and takes several minutes to complete, whereas in our non-RDS test environment ...
3
votes
2answers
60 views

Optimization of a query on a table that represents a weak entity type

I've been trying to figure this problem out for a while now, so I decided I'll try my luck here. I have a very complex online shop DB structure (multiple shops in one database, hundreds of thousands ...
0
votes
0answers
13 views

Query performance issue in Mysql

Table is completely normalized its in innodb. Size of data is more than 600 million records. Even after indexing and optimization queries are too slow. Size of records increases rapidly and it would ...
0
votes
0answers
19 views

How to reset id?

id is the primary key in table student. select id from student; +-----+ | id | +-----+ | 171 | | 175 | | 183 | | 184 | | 190 | | 191 | | 200 | +-----+ 10 rows in set (0.00 sec) How to reset the id ...
0
votes
0answers
17 views

Predicate that spans 2 columns over 2 tables is very slow

I am having some performance issues on a query that does a concat between two fields in 2 different tables. If I remove the concat, the query takes a few ms. The offending predicate is this: AND ...
-1
votes
1answer
38 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, ...
1
vote
1answer
33 views

How to test whether unique index can be created in MongoDB?

I want to add a new unique compound index to our production system. Before doing the change, I would like to check whether the field combination is unique, i.e. will the index creation succeed or fail....
0
votes
1answer
44 views

How to Replace Hash Match (Partial Aggregate) with Stream Aggregate

My execution plan shows a hash match (partial aggregate) instead of a stream aggregate even though I have a non-clustered index (which basically means sorting is already in place). It gives me a ...
1
vote
1answer
60 views

Why full text search on table with GIN index is still very slow

With what I've been able to gather thus far, if you need to run a full text search on a table in a PostgreSQL (psql 9.6.2, server 9.6.5) database with lots of entries (say on the order 1.2M+), the ...
0
votes
0answers
51 views

Newbie Clustered Index question - when to NUSE?

I have a bit of a newbie question, which I feel I should really know having dealt with databases for so long, but... I know the general advice for indexes is NUSE, but a lot of my tables have a non-...
2
votes
0answers
50 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 ...
1
vote
3answers
67 views

How can I speed up this query?

Here is the definition of the relevant table: CREATE TABLE IF NOT EXISTS pricehistory ( id INTEGER(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, price INTEGER NOT NULL, region ...
1
vote
3answers
48 views

How correct Postgres plan in case of a double JOIN, ORDER BY and a LIMIT?

I got a SQL request that takes more than 4s: SELECT "Content"."contentId" FROM "UserFeed" JOIN "FeedRole" ON "FeedRole"."feedId" = "UserFeed"."feedId" JOIN "Permission" ON "Permission"."...
0
votes
1answer
58 views

How can I get SQL to do the sensible thing without resorting to a query hint?

I have two tables, Items and Grouping. Items has about 7 million rows whereas Grouping only has 300,000. Of the two conditions on my query, the 'human obvious' thing is to evaluate G.StatusID IN (1,2)...