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
2answers
21 views

How to drop all indexes not used for primary keys or constraints

I have an old dataset with a lot of unused indexes. I would like to clean it up and bring it back to what it looked like before custom indexes were added. Is there a way to drop all indexes except the ...
1
vote
0answers
29 views

How to prove Unique index could be better than non Unique index by the execution plan

I know Unique index will be faster than non Unique index theoretically if data is unique. Because Unique index be able to provide more information let query optimizer choose more effective execution ...
0
votes
1answer
103 views

Ensure that SQL server "avoids" the clustered index when a non-clustered index can be used

I have a query, the short of which looks like the following. While this is not exact, it should show how the problematic behavior is introduced and what I am looking to prevent. And "prevent"...
0
votes
2answers
70 views

Why Is There a Sort on this Column

This query has a plan which includes a SORT and the sort is taking up the majority (77%) of the cost: SELECT LFTYLM_STATUS FROM LFTYLM WHERE LFTYLM_LIS_ID = 138998 AND LFTYLM_LFTY_ID = 78 AND ((...
-1
votes
0answers
12 views

Finding good index/constraint on Timescale Hypertable

So we got multiple Timescale Hypertables on our PSQL Cluster. I try to optimize the tables in the last time. Our Background: the Tables are around 300 - 500GB in size. Normally we will use queries ...
0
votes
2answers
49 views

Order of Key Columns in Index

The execution plan for this query appears to be making a very bad choice (of course I know it is not): SELECT TOP 1 E_PER_ID FROM E WHERE E_CUS_ID = 1912 AND E_TYPE = 'R' ORDER BY E_ID DESC The two ...
0
votes
0answers
15 views

Why JSON indexing take less storage than multiple columns in MySQL 8?

I am doing a quick performance comparison for 10 000 rows on MySQL 8.0.27 between a table multiple_cols with 2 columns num INT and str CHAR(200) having each an index, a table json_indexing with 1 ...
1
vote
2answers
60 views

Postgres slow query on nullable index

Postgres version: 12.7 Table definition: column_name, data_type, is_nullable "id", "integer", "NO" "data",...
2
votes
2answers
261 views

Why does a MySQL 'where' clause with Index make a SUM query slower?

I have a table with 5 million records. Table columns are id, customer_id, status, and amount. When I run a query without Index(status), SELECT SUM(amount) FROM `game_results` WHERE `status` = 1 the ...
5
votes
3answers
570 views

PostgreSQL ignores index, runs seq scan

My table contains an index for column total_balance: \d balances_snapshots Table "public.balances_snapshots" Column | Type ...
1
vote
0answers
54 views

Slave index corruption mariadb 10.5

We have a simple mariadb replication with a master and a bunch of slaves. without any prior alert or warning, randomly in our slaves some indexes ( some of them are not present in master) become ...
0
votes
0answers
15 views

Oracle Index Clustering Factor

My question is about Clustering Factor (CF) of Indexes in oracle I have created a table as follows : Create table T2 as select c1,c2,c3,c4,c5,c6 from T1 order by c1,c2,c3,c4,c5,c6; Create Index IDX ...
0
votes
1answer
29 views

Track down which resource limits index creation in PostgreSQL

This index creation is slow (2 million rows) : CREATE UNIQUE INDEX foo_index_id ON foo (id); I modified several parameters which did not significantly affect total time: ...
0
votes
1answer
21 views

Possible to create a constraint that is unique per foreign key?

MySql, Table Repos Fk on Users.Id, A User can have many Repos however each Repo must have a unique Id, per User not per table, what is the best way to ensure that Repos Id is unique for a given User ...
-5
votes
2answers
66 views

Rebuild Indexing

i have SQL Server 2016 SP2 Standard Edition and 24*7 critical environment and Database size 1.31TB and i have free space 186GB on drive. I check database in maintained plan Rebuild indexing is not ...
-2
votes
0answers
11 views

Determining mathematical fomula for single level and two level primary index

For a certain primary index, we have the choice to create a single-level index or two-level. With a single-level index, there are B blocks in the index file and each block has E entries. With a two-...
5
votes
4answers
500 views

Are 'Functional Indexes' Supported in MS SQL Server?

I've just come across expression based indexes (aka "functional indexes") when reading through the documentation for PostgreSQL (I wasn't aware of such indexes previously). I'm just ...
0
votes
0answers
23 views

Why does postgres/citus use the index when querying the table, but not trough the view?

I have a Citus 9.5 database (postgres 11) on Azure and executing a complicated query with CTEs. The schema has a simple view from another schema as: create view my_schema.table1 as select * from ...
0
votes
1answer
40 views

When should I use Index on PostgreSQL? [closed]

In my Java (Spring Boot app), I have the following entity definition which has a relationship with Company entity via companyUuid property. I will also let users to search by name field. We do not ...
5
votes
2answers
617 views

How can I improve the performance of where clause with LIKE %abc?

I had below select statement that turned out very slow (41s) SELECT DISTINCT a.Doc_Resource_ID ,a.Parent_ID ,a.Logical_Path ,a.lvl ,CAST(SUBSTRING(a.Security_Level, 21, ...
1
vote
1answer
18 views

Postgresql (11.9) LIKE query not using text_ops index

I've seen related questions but I think this should be working and isn't. I have a text_ops function based index on column to do LIKE searches, but it only picks up the index with an equals: CREATE ...
0
votes
1answer
31 views

Optimize MariaDB query large dataset

We have a web application that uses MariaDB relational mode using tables with "InnoDB" engine, because our application inserts and updates many data each second. Also, we have a table to ...
2
votes
0answers
38 views

When/how does removing an index cause a table to lock in MySQL?

Dropping an index in MySQL 8 is INPLACE, not INSTANT, per https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html. As I understand it, INPLACE means that there is potentially some ...
0
votes
1answer
150 views

Ola Hallengren maintenance solution

When I am executing the below query: EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @...
1
vote
1answer
40 views

Postgres v10 is not using the index in a subquery

I have a subquery that will return 3 records, to later add the result of some calculations (using some functions). When I am running the full query, Postgres is calculating a high number of expected ...
1
vote
1answer
33 views

Specific index issue with postgresql planner/optimizer

i have an issue where i have no idea anymore and can't seem to find a similar one already discussed, so... Am/have to be on pgsql 10.15 / RHEL x86_64. rel A has about 813 million records. format is ...
0
votes
0answers
31 views

Different EXPLAIN ANALYZE results for the same query on different servers

I have a table named review with about 33 million rows. I have this query: EXPLAIN ANALYZE (WITH clicker_reviews AS ( SELECT monologue_id FROM review WHERE type = 'CORE' AND ...
-4
votes
1answer
52 views

MS SQL Server 2016 Standard Edition? [closed]

Is DB Online Index Rebuild supported by MS SQL Server 2016 Standard Edition?
1
vote
1answer
60 views

Implementing fast and deterministic sort order in PostgreSQL with pg_trgm

We have implemented a similarity search with pg_trgm in a PostgreSQL 13 database using a gist index with searchable_column. The table and index have the following setup: CREATE TABLE things ( id ...
0
votes
2answers
111 views

I think SQL Docs are wrong for sys.dm_db_index_physical_stats

Go to this documentation page: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15 Then go to ...
0
votes
1answer
37 views

Does trailing wildcard use index? (where column like 'value%')

I have a query on a Varchar2 field in an Oracle table: select column from tbl where column like 'value%' That query uses a trailing wildcard. Question: Is Oracle capable of using an index on that ...
6
votes
1answer
242 views

Creating multicolumn index for WHERE and ORDER BY

I am trying to create an index to be used in WHERE and ORDER BY clauses at the same time. Reading Postgres 14 documentation (11.4. Indexes and ORDER BY - https://www.postgresql.org/docs/14/indexes-...
1
vote
2answers
81 views

Indexing a view with an averaged column

With Sql-Server, I create this view: IF OBJECT_ID ('dbo.v_table', 'view') IS NOT NULL DROP VIEW [dbo].[v_table]; GO CREATE VIEW [dbo].[v_table] WITH SCHEMABINDING AS select toplevelcom_nr, avg(...
2
votes
2answers
67 views

improve performance of large scan calculation in PostgreSQL table

I have a table flags that collects user contributions for each distinct polygon (flag_value, in this case a 'YES') during their usersession. The idea is to count how many users said YES out of the ...
0
votes
2answers
50 views

Efficient generation of quantiles for indexed column

I am trying to build a query partitioner for a distributed processing system. This system would work with databases that support JDBC. The input of this partitioner would be the name of a table, and a ...
-1
votes
1answer
55 views

Composite Primary Key and Secondary Covering Index: Does the Secondary Index Ever Need to Include a Primary-Key Column?

MySQL. InnoDB. Say there's a composite primary key 'a, b'. There is another column 'c.' I have a query hitting 'a, c.' Consider two claims. (A) "A secondary index on 'c' gets me a covering index.&...
0
votes
1answer
10 views

postgresql Modifying existing unique index

I have this table on PostgreSQL: example_dev=# \d products Table "public.products" Column | Type | Collation | Nullable | ...
1
vote
1answer
39 views

Postgresql indexes: enums and booleans first?

When writing indexes, should I put the fields that represent enums first? That is, these fields have limited possible values: [0,1,2,3], for instance. So, I might make an index "my_index" ...
0
votes
2answers
63 views

Why is this UPDATE query not using an index?

I have a query: EXPLAIN UPDATE myTable SET col2 = 'foo' WHERE col1 = 'bar'; +----+-------------+-------------+------------+-------+-----------------------+---------+---------+------+--------+---------...
0
votes
1answer
31 views

Different response time because it does not use the indexes created with the same query and same tables from the web server to the Oracle database

I have a database with Oracle 12c (12.1), where when making a query locally and even from TOAD or another remote tool, it returns the response in milliseconds, reviewing the execution plan I can see ...
1
vote
2answers
201 views

Index on VARCHAR(MAX) column

Table structure: ID textvalue longtextvalue textvalue has an index and a length of 900 longtextvalue has no index and a length of MAX Goal is to put together both columns with a length of MAX and an ...
0
votes
2answers
53 views

Optimal index for a nested query?

I have a nested query and I am not sure if the index I've chosen fits it's needs. Currently I am not happy with it's performance. The table contains > 2 Mio rows counting. I am working with Oracle. ...
0
votes
2answers
93 views

What is meaning and significance of inequality column in missing index stats?

What is meaning and significance of inequality column in missing index stats? I'm trying to understand how does it influence the way dba will create an index?
1
vote
1answer
24 views

How does page split result in index fragmentation?

I am trying to understand the reason behind index fragmentation. Assuming fill factor is 100% and the clustered index is on an integer column. The values on each page are as follows and assuming that ...
1
vote
1answer
97 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: '...
5
votes
2answers
532 views

With a multi column non clustered index, and the SELECT on middle column, why does SQL server query this index rather than scanning the table?

A single non clustered is set up on LastName, FirstName, MiddleName - in that order. SELECT * FROM PERSON WHERE FirstName='xyz' Why does the execution plan use the index and not scan the table ...
0
votes
1answer
36 views

postgres set null will improve performance in sparse data?

I have ~50 mil data entries, while most of them(maybe 80%) have null values in a column(currently I assigned a dummy value to those null entries). However, I'm doing frequent searching on non-null(...
0
votes
1answer
40 views

Is there any benefit to specifying a fill factor when the table is empty?

When the table has lots of data, and I create a new index with fill factor (say 30), then sql server will leave space for new entries to be added into the data page. This will keep fragmentation low ...
3
votes
1answer
106 views

Index usage Brent Ozar script

I'm using sp_blitzIndex to check the health of my database indexation, but I don't understand every column used in the output. I'm talking specifically about "Usage Stats" and "Op Stats&...
-1
votes
1answer
48 views

Clustered Index and Sorting [duplicate]

select * from [table_a]; A clustered index sorts the table based on whatever column(s) you choose. That being said, if I have a clustered index on [column_a], [column_b], and [column_c] and run the ...

1
2 3 4 5
60