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
20 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
32 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
74 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
35 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
32 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 ...
-1
votes
0answers
21 views

Why are some indexes not completely defragged by AdaptiveIndexDefrag utility? [duplicate]

In a previous question (now deleted), I mentioned that I used Microsoft Tiger Teams' AdaptiveIndexDefrag utility to defrag the indexes of several databases. The thing that puzzles me is that some ...
0
votes
0answers
30 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
50 views

MS SQL Server 2016 Standard Edition? [closed]

Is DB Online Index Rebuild supported by MS SQL Server 2016 Standard Edition?
1
vote
1answer
50 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 ...
5
votes
1answer
230 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
69 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
66 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
48 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
45 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
30 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
133 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
82 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
22 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
85 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
523 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
30 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
39 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
95 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
45 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
vote
1answer
79 views

Query a table of domain names to get matching top level domains using index

I need to have a table that will contain a single column with a domain+suffix that will looks something like this: domain_and_suffix google.com ynet.co.il 360.cn I want to be able to query this ...
2
votes
1answer
64 views

does the order of index columns matter when we have equality operators?

Given that I have the tables CREATE TABLE `replies` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `repliable_id` bigint unsigned NOT NULL, `repliable_type` varchar(255) COLLATE ...
0
votes
1answer
44 views

Why insert statements get slow when having too many indexes?

I am having a slowness in my insert statements when working with MongoDB, and when I did a quick investigation, I though this might happened because the table has 11 indexes. Plus, I know that when I ...
0
votes
1answer
25 views

Index not used in a partitioned Oracle table

I have a table partitioned by list create table ICT_ENCOURS_BRUT ( code_exercice VARCHAR2(4), CODE_PRODUIT VARCHAR2(2), ....... ) partition by list (CODE_EXERCICE) ( ...
0
votes
0answers
61 views

How to create an index on MongoDB for Multiple Fields

I am looking to create an index on MongoDB, am relatively new to this type of DBMS, have looked in the MongoDB log and noticed that its doing a COLLSCAN which from reading online can indicate that an ...
0
votes
1answer
33 views

What does this mean "index is also maintained in the transaction log"?

"Index is also maintained in the transaction log" Does this mean when a table having an index is queried, the entire index is pulled into the memory and subsequently logged into tlog?
1
vote
1answer
323 views

Why engine choose Index Scan instead of Index Seek for Group By query?

I have query: SELECT M_nvarchar_3 , MA_nvarchar_40 , W_nvarchar_4 , LB_nvarchar_2 , SUM(ST_decimal_13_3) , SUM(CW_decimal_13_3) ...
0
votes
0answers
45 views

Working with index-key sizes which are close to the DBMS's index-key size limit

I might need to work with relatively large (non-clustered) index keys (between 1KB to 4KB). I found that Spanner and DB2 support 8KB index-key size, MSSQL allows 1.7KB and Postgress around 2.6KB (1/3 ...
0
votes
1answer
27 views

Get inserted values / UNIQUE key over multiple tables / MySQL

Caused by some bad design decisions, I have to make sure that a random-character string (no sequence!) must not appear twice in three different MySQL tables (TableA, TableB, TableC). So if the value &...
0
votes
1answer
36 views

How can I get MySQL to use the right index

I have a query on a table with almost 1.8M rows. I created an index new_index which includes all columns in the WHERE and ORDER clauses in order to improve performance. Now I issue the query: select ...
1
vote
1answer
58 views

Is it the right scenario to use spatial index?

I have used spatial index recently on one table to improve query which filter records by given date range, reference identifier and whether they are inside polygon. Here's the example of the query ...
0
votes
1answer
48 views

MySQL not using index as expected with a WHERE IN clause

I have a table called mytable and I'm trying to optimize a query on it. I generated the index ind5 on it, and it has the nullable int column col_1 in first place. This query has a WHERE IN clause, ...
0
votes
0answers
37 views

PostgreSQL query on jsonb column is not using indexes

I have the following table and indexes where all those indexes were created to try and find a solution to my issue: create table persistent_events ( "notificationId" ...
1
vote
3answers
58 views

Is there a mechanism inside of database engines to avoid collisions of non-sequential keys? If so, how does it work?

Say that I create a table that that has a random uuid for a primary key. Is there typically a mechanism inside of the database to gracefully deal with collisions? How does it work? My mind is ...
0
votes
0answers
13 views

MongoDB Index Restore Failure Since I Upgraded from 4.2 to 4.4

Steps to reproduce After upgrading from 4.2 to 4.4, take backup using mongodump Now restore backup On some collections, I am getting following {"alternate":"non-ignorable", "...
1
vote
1answer
48 views

PostgreSQL Query Optimization - Joining with a lookup table does not use index

I am attempting to optimize indexes and querying for a postgres database, and have run into some issues around a lookup table I have used to support joins for a many:many relationship. Here is the ...
5
votes
1answer
169 views

Index Definition Order and ORDER BY Clause

So i was doing my morning blog reading and stumbled upon this fun exercise: https://www.erikdarlingdata.com/sql-server/lets-design-an-index-together-part-3/ Here is the query in question from the ...
0
votes
1answer
26 views

Get number of deleted pages in PostgreSQL table/index?

How can I get the number of deleted pages in a table or index? My purpose in asking is to modify an index bloat query that otherwise doesn't take into account which pages are deleted (and therefore ...
0
votes
1answer
18 views

Amazon RDS index creation on large table over 300 M records

I need to create a new index on large table in RDS MySQL. This index is needed to reduce the scan time on this table. Below is the configuration: Table size: 400 GB Records: 400 Million column - ...
0
votes
0answers
16 views

A strange index that is always marked pg_index.indisvalid=false . Why indexes with ON ONLY option are always rebuilt in invalid state?

I'm looking in database (postresql 11.10) and see an index on a partitioned table that marked as invalid: SELECT indrelid::regclass, indexrelid::regclass, indisvalid, ddlx_create_index(...

1
2 3 4 5
59