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
1
vote
1answer
93 views

How to increase index row maximum size in Postgresql?

I want to perform KNN on 512-dimensional vector using the solution suggested here. I couldn't create table with 512-d cube array. Solved by changing the CUBE_MAX_DIM in the source code. Couldn't ...
1
vote
2answers
42 views

SQL Server Update Statement joining on index column

Using SQL Server 2012, I have a stored procedure that updates data by doing a delete and insert similar to this: DELETE FROM MyTable WHERE EXISTS ( SELECT 1 FROM ...
0
votes
1answer
37 views

MongoDB is not using the index I created for this specific query

MongoDB 4.0. I have a simple query: db.product.find({ modifiedOn: { $gte: ISODate("2019-08-02T03:00:00.000Z") } }) .limit(10) And a simple index on modifiedOn....
1
vote
1answer
76 views

Performance Recommendations for Large Volume of Data

Oracle Table: Fields: 55 Roughly 10 are Used for Querying Single Record Length: 512 bytes Records: 3-5 million records per day Period of Time: 13 months to be available, > 13 months to be purged ...
0
votes
1answer
26 views

Optimize MySQL FULLTEXT searches to use another index

I'm running a price comparison service, with a database of products that aims to become quite large (in the millions or more). The Product table looks like this: CREATE TABLE Product ( id INT(10)...
1
vote
2answers
54 views

MySQL 5.6 users index instead of range type select on a big table and simple query

We have recently migrated from MySQL 5.5 to 5.6 (5.6.44-86.0-log Percona Server to be precise) and we have a huge problem. Seemingly simple queries run for minutes instead of milliseconds on a table ...
3
votes
3answers
287 views

Getting Index Scan instead of a possible Index Seek?

Currently learning a few things on query optimization, and I've been trying out different queries and stumbled upon this "issue". I am using the AdventureWorks2014 database, I ran this simple query: ...
5
votes
3answers
508 views

How likely is fragmentation on a table with 40000 products likely to affect performance

So database administration is not a strong point of mine, so I have a few questions. This all relates to the performance of a website I'm looking at, and have spent a fair bit of time researching/...
0
votes
1answer
31 views

pg_trgm index not used with commutator

PostgreSQL 9.6, on a table with ~100000 rows of text up to 3200 characters long. I have the trigram indexes: CREATE INDEX trgm_idx_gin ON test_trgm USING gin (t gin_trgm_ops); CREATE INDEX ...
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 ...
4
votes
2answers
113 views

How to optimize IN query on indexed column

I have a table with over 50M records. One of the fields is COLOR_CODE. I've set an index on the column COLOR_CODE like this: "mytable_colorcode_idx" btree (color_code) I'm noticing that when I run ...
2
votes
1answer
112 views

What does SQL Server use to do a count(*) on a table?

Internally, how does SQL Server determine the result for a select(*)? Is it different between a heap and a clustered index? Is it able to do smart things with the clustered index to not have to load ...
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
0answers
47 views

Do i need to add index for apply filtering for this query?

Below is my query: with cte as (SELECT a.postId from (SELECT p.postId FROM join_area ja INNER JOIN post p USING (areaId) WHERE ja.belongAreaId = 29 AND p....
0
votes
1answer
47 views

slow count queries - what are we doing wrong?

Using Postgresql 9.6.14, running the following query: SELECT COUNT(*) AS "__count" FROM "crm_client" INNER JOIN "crm_role" ON ("crm_client"."role_ptr_id" = "crm_role"."id") INNER ...
2
votes
4answers
108 views

Is it better a composite index (item, item_type) or single-column index on item_type_1, item_type_2, …?

We have items of three types to be stored and queried according to some business logic. create table a_table ( item_a varchar2(30), item_b varchar2(16), item_c varchar2(2), -- other ...
15
votes
5answers
454 views

Attempts to reclaim unused space causes the used space to increase significantly in SQL Server

I have a table in a production database that has a size of 525 GB, of which 383 GB is unused: I'd like to reclaim some of this space, but, before messing with the production DB, I'm testing some ...
2
votes
2answers
152 views

Usage of NON Clustered Index in SQL Server

I have 2 Index: CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA] ( [Column1] ASC, [Column2] ASC ) CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[TableA] ( [Column3] asc ) Now I have 2 ...
1
vote
1answer
19 views

How to skip prefix column check in multi column index in MySQL?

Below is my demo table structure: CREATE TABLE `table1` ( `postType` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-for rent, 1-to rent', `postLocation` smallint(6) NOT NULL, `postArea` smallint(6) ...
0
votes
1answer
32 views

Not lowering avg fragmentation

I have a script which iterates through all database and underlying tables. With this script i want to rebuild/reorganize the indexes and i am testing this on AdventureWorks2017 database. So after ...
1
vote
1answer
80 views

Inserting few millions of rows: Does indexing happens along with the insert operation

I am inserting 50 million records to an existing table with a primary key , and a non clustered key. My question is whether indexing of those will existing indexes will happen along with the insert ...
0
votes
0answers
27 views

How to index on joined tables

Total table_parent rows: 1318 Total table_child rows: 12 mil++ EXPLAIN select SQL_CALC_FOUND_ROWS table_child.* , table_parent.name AS parent_name from `table_child` inner join `table_parent` ...
0
votes
1answer
40 views

Why last table don't using index for joining?

By the help of others members i get an Query idea for my previous problem. But after writing this, the EXPLAIN showing that my last table do't using index for join but the joining ROW is PRIMARY KEY, ...
0
votes
0answers
30 views

Query Optimization - Potential ill effects of adding an full text index to a frequently updated table

We have a query of the following type which we are trying to optimize. SELECT table_one.* FROM table_one INNER JOIN table_two ON table_two.fk_id = table_one.id WHERE (table_one.h_id = 20626 AND ...
0
votes
0answers
22 views

optimize query with date type field in mysql

I currently have the following query prepared: select sum(amount) as total from incomes where (YEAR(date) = '2019' and MONTH(date) = '07') and incomes.deleted_at is null When ...
-1
votes
1answer
33 views

How to update a numeric type column to avoid many decimal points

lets assume I have a table with this entries: thing: position: ball 1001.25 pensil 1001.0625 car 1002.5 watch ...
0
votes
0answers
20 views

Global nonpartitioned index vs local partitioned on for a partitioned table

We have a select query which is really slow. We are using views in the from clause. Global nonpartitioned index is used for accessing the rows of a partitioned table. My doubt is that if Global index ...
1
vote
2answers
87 views

primary key constraint Sorting

Is having a field in primary key constraint optimizes sorting of the table ? Here's the schema of the table: CREATE TABLE Persons ( Field1 long NOT NULL, Field2 long NOT NULL, Field3 long NOT NULL, ...
-1
votes
1answer
101 views

MariaDB: Duplicate entry for key on OPTIMIZE with no duplications

There is a table named contracts with following structure: MariaDB [db_name]> DESCRIBE `contracts`; +-----------------------------+--------------+------+-----+---------+----------------+ | Field ...
1
vote
1answer
217 views

Slow SQL query from app, fast from SQL Studio

I have a query that takes 30+ seconds when called from app, but takes <1sec when called directly from SQL SMS. Here is the query : SELECT T0.pk FROM products T0 WHERE (( ( T0.pk IN (...
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/...
2
votes
1answer
37 views

Do I need a Primary Key if it's already an Index

I have a bunch of tables with no Primary Key. Some do already have a non clustered index comprised of what would be the Primary Key (ClaimID, LineNBR). The columns used are currently set to accept ...
0
votes
0answers
30 views

What is the proper way to index a column in MySQL that has a lot of json data in it?

We have a long row that includes a json column that holds variable columns specific to each row. We currently do not index that column as we don't query against it - rather it is supporting data for ...
0
votes
1answer
60 views

PostgreSQL - how to list all UNIQUE indexes for specific database?

I would like to get a list of all UNIQUE indexes (for unique column combinations) for specific database in PostgreSQL. I searched but could not find the query for this. I found these two queries. ...
1
vote
1answer
68 views

Is it necessary to ANALYZE a table after an index has been created?

In my transaction, I am creating a temporary table: create temporary table x on commit drop as select ... I also add an index on that table: create index on x(some_column); Is it now necessary to ...
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 ...
-4
votes
1answer
69 views

what does 'Create Unique Index' do?

Let's say we have a emptyCustomers table (CustID, Age, FirstName), and we create a unique non-clustered index on Age column. Obviously, there could be a lot of duplicates on Age. So what does 'Create ...
-1
votes
1answer
24 views

Will an indexed View gets executed every time when a new row is inserted? [closed]

Let's say we have two tables: Customers(CustID, FirstName, OrderID) and Orders(ID, Date, Amount) and there is an indexed view join on CustID and ID and have a clustered index on these two columns. so ...
-3
votes
2answers
61 views

why you can't enable a disable index? [closed]

My textbook says: "The opposite of an ALTER INDEX … DISABLE isn’t ENABLE; that doesn’t even exist. You must perform an ALTER INDEX … REBUILD." But when you rebuild a index, you make the index ...
2
votes
2answers
42 views

Postgres query very slow when using index

So I've run into this issue with a slow Postgres query. I've added an index but it doesn't seem to help. It takes 8 minutes to get 100 records from a table with 2822968 records. Here's the query ...
1
vote
1answer
78 views

Constraint on multiple columns?

I have a table edges, which describes relations in a graph: CREATE TABLE IF NOT EXISTS edges ( src INT NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE ,tgt INT NOT NULL ...
1
vote
4answers
193 views

Indexing a SHA hash in place of a VARCHAR

In Is it a good idea/approach to index a VARCHAR column? the concept was using VARCHAR as the column. I was wondering, if we store the SHA-1 hash or SHA-256 hash (if I am paranoid) as a BINARY(20) ...
4
votes
3answers
84 views

How does FILLFACTOR reduce page splits?

The default FILLFACTOR is 100 and I have been told at times to reduce it to 80 or lower so that page splits will be reduced due to the empty space on each page. I don't understand this--if a new ...
0
votes
0answers
28 views

How to create an Index on a raster column?

In a PostGIS enabled DB, I have a 1.7GB table with a raster column rast. I need to create an index on that column in order to speed up my raster queries. I have tried the following: CREATE INDEX ...
8
votes
2answers
328 views

Covering index used despite missing column

I have the following query, using MariaDB 10 / InnoDB: SELECT id, sender_id, receiver_id, thread_id, date_created, content FROM user_message WHERE thread_id = 12345 AND placeholder = FALSE ORDER ...
1
vote
0answers
88 views

Querying foreign table with SP-GiST index results in “ERROR: cache lookup failed for type 0”

I'm using PostgreSQL 11.3 (compiled by Visual C++ build 1914, 64-bit) and I want to access a table in another Postgres database using postgres_fdw but since the original table has an SP-GiST index on ...
0
votes
0answers
5 views

InnoDB clustered index performance when using random values as primary key

By default, my primary keys of InnoDB storage engines are auto increment integers. In effort, to hide the number of rows in the database, application code implements some random generator for the ...
1
vote
1answer
19 views

Multi-dimensional range queries with single column indexes

I am reading a book that covers many database topics on a very high level, but doesn't dive deeper. It introduces multi-column indexes as a better way to query for 2 dimensional ranges. For example, ...
0
votes
0answers
67 views

How to calculate count of rows of Memory-Optimized Table?

Performance is important for me so I don't use select count(*) from MyTable. I use the following query to calculate rows count: SELECT SCHEMA_NAME(schema_id) AS [SchemaName], [Tables].name AS [...