Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

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

Why is Primary Key not present at the end of Unique Key in MySQL?

I have a table with following definition. CREATE TABLE `test` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, `e` int(11) DEFAULT NULL, ...
3
votes
1answer
90 views

Why is a Postgres 11 hash index so large?

Postgres 11.4 on RDS and 11.5 at home. I'm looking at hash indexes more closely today because I'm having problems with a citext index being ignored. And I find that I don't understand why a hash ...
4
votes
3answers
85 views

Expression index on a citext column ignored, why?

Running on RDS with about 32M rows. PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit Also testing locally on macOS with about 8M rows. ...
0
votes
1answer
10 views

Changing POSTGRES column datatype - should I drop indexes and then re-create?

I have a number of postgres tables with a column of type INT which I need to change to BIGINT. Each partition of the table contains about 100 million rows, my intention is to detach the individual ...
0
votes
2answers
74 views

Proper way to add new column in database

What is the proper approach when we add a new column into an existing table? For example, I already have columns like, Foo1, Foo2, Bar1, Bar2. Now I want to add a new column called Foo3. What is ...
2
votes
0answers
39 views

Oracle DISTINCT with NULLable columns

There is a longstanding(?) problem(?) with Oracle that if you want to use a DISTINCT select on a column which has a B*-Tree Index but is nullable, that the index is not used. (As other answers suggest,...
2
votes
1answer
71 views

Range Extraction Algorithm for Muti-Part aka Composite Indexes in MySQL

I went through Range Extraction Documentation for MySQL Indexes. They have explained for single column indices. What is the procedure to reduce complex multi column (at least two) criteria? What I am ...
0
votes
1answer
66 views

SQL Server Non Clustered Index Compression

I'm struggling to find out the answer, we currently use compression on all our data warehouse tables, which has improved performance and also greatly reduced the storage space used. I've only ...
1
vote
1answer
36 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. ...
2
votes
1answer
47 views

Adding indexes to production MySQL database without downtime?

I am looking to improve the performance of queries on a large MySQL database that is running in production by adding proper indexes. We have tried to add the indexes on the live database, but of ...
2
votes
1answer
27 views

Creating a view (or table) from JSON data with two different shapes

I'm pulling in JSON data from two different sources that have roughly the same information, but in different shapes. Let's say I'm pulling in data about music albums, and one of the API responses ...
0
votes
1answer
76 views

sqlite - very slow performance of WHERE EXISTS without index on EXISTS

I've got a strange case of (very) slow performance of an update query when I don't have an index on the table in the EXISTS clause. For example: UPDATE tbl1 SET col1 = "Y" WHERE EXISTS (SELECT ...
0
votes
0answers
21 views

Matching Information in specific example

I am new to database building and have been fighting with a certain query problem for a couple of days now. All I need really is how this kind of Problem is called, but I would appreciate a longer ...
2
votes
1answer
106 views

indexes are not created on localdb

When using localdb and executing this, when browsing the database in sql server object explorer (in visual studio), the index is not created. I have to manually create it to make it appears. Why ? ...
1
vote
1answer
51 views

Why Index-Only-Scan's COST can be higher than Seq-Scan

sql> create table index_test (col1 integer, col2 integer default 0); sql> do $$ begin for i in 1..100000 loop insert into index_test (col1) values(i); end loop; end; $$; sql> ...
2
votes
3answers
36 views

Postgresql Operator Class for index support of custom operator

I'm trying to create an index that will support queries that use my custom operator. This is on Postgresql 10.4 The custom operator I followed the tips in this SO answer to create an operator that ...
1
vote
1answer
33 views

How to get same value in a unique column but differentiated by other column values?

I have a table like this ID | PRODUCT_NAME | MANUFACTURER_ID | PACKAGE_ID --------------------------------------------------- 1 | VITAMIN A | 1 | 1 2 | PARACETAMOL | ...
4
votes
2answers
239 views

GIN index ignored when query has many (more than 18) OR statements

I have an issue where the execution is not using a GIN index when going over 18 OR statements. This is my index: CREATE INDEX emailjobs_externalreferences_gin ON vsko_mailer_api_prod.emailjobs USING ...
2
votes
0answers
30 views

Querying a High Cardinality Field

I am designing a data model for our orders for our upcoming Cassandra migration. An order has an orderId (arcane UUID field) and an orderNumber (user-friendly number). A getOrder query can be done by ...
0
votes
1answer
26 views

Does innodb guarantee unique data in unique indexes even with UNIQUE_CHECKS=0?

We have a suspicion that sometimes there could be non-unique data even with a UNIQUE index. Question is, can we rely on InnoDB guaranteeing that there won't ever be non-unique data, even in the ...
5
votes
1answer
50 views

Bad execution plan when having partial index and big In-clause in Postgres

Postgres seems to always use sequential scan where it could have used a partial index to get index scan only. It only happens when an in-clause exceeds more than 100 elements. Given the following ...
4
votes
1answer
68 views

How to recreate an Index with the same settings?

I'm in a situation where I want to add another included column to an existing index, but keep the rest of the settings (eg. FILLFACTOR, ONLINE, SORT_IN_TEMPDB, etc.) the same. I do not have the ...
7
votes
2answers
375 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 ...
2
votes
1answer
40 views

Is searching faster if indexed values are the same?

Currently I have a collection events with an attribute createdAt of type date (for example 2019/09/09 10:32:12.123). Because my users search a lot by date, I added an index on this attribute. There ...
0
votes
0answers
38 views

index corrupt error while trying to index a large table

I am trying to index a large table, approximately 12 million rows and 33GB in size. There is currently no indexing on the table. When I am trying to index one of the columns in a table, I get the ...
0
votes
1answer
15 views

Mongodb Index not used in case of $or query

I have added created a collection first and created index db.first.createIndex({a:1, b:1, c:1, d:1, e:1, f:1}); and inserted data db.first.insert({a:1, b:2, c:3, d:4, e:5, f:6}); db.first.insert({a:...
-1
votes
2answers
77 views

Mysql 8 picking wrong index for group by date function [closed]

I have a table as below. CREATE TABLE `alarm` ( `alarmTypeID` tinyint(4) NOT NULL, `vehicleID` mediumint(9) NOT NULL, `alarmDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `...
0
votes
0answers
31 views

Why won't postgres use my index?

I have a table comments with many fields and one of them is rating integer not null and I have added index: +--------------+-----------------+-----------------------------------------------------+----...
0
votes
1answer
35 views

Oracle function based index with empty function name

I'm trying to understand this syntax in the creation of an index in a database I'm using: CREATE INDEX IDX ON SQ.MYTABLE (('ID_COLUMN1'), ('ID_COLUMN2'), ('ID_COLUMN3')); After searching for a ...
9
votes
4answers
1k views

How do I intentionally fragment a SQL Server Index?

I would like to create bad indexes conditions intentionally on a SQL Server 2017 test database I have, just to better understand these maintenance scripts? SQL Server Index and Statistics Maintenance ...
4
votes
1answer
84 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 ...
3
votes
3answers
120 views

Any drawbacks of using GIN PostgreSQL index for an integer foreign key?

I have a large table (600 millions rows) with a foreign key other_id of type integer. A single value of the foreign key is repeated about 100 times on average. I need to have an index on that FK ...
1
vote
1answer
35 views

When are statistics created?

I know that when we create an index then statistics are automatically created. My question is: Is it the only case when statistics are created? I remember that once I found on the internet that we ...
1
vote
2answers
65 views

Is it bad practice to automate the updating of statistics and re-making of query plans on a relatively small SQL Azure database?

I have a SQL Azure database that's about 500MB in size. Every 2-3 weeks the database will suddenly start to run slow and some queries (generated by LINQ) will timeout. There is no gradual increase in ...
0
votes
1answer
20 views

Postresql dynamic index creation for many tables

How can I create an index in Postgres dynamically over many tables but always with different names. Lets assume table x has a column y with all table names of hundreds tables which need to get an ...
1
vote
2answers
62 views

DB2 Load Utility Primary Key vs. Unique Index

In short, does the Primary Key double as a Unique Index? Some background: I am trying to load a bunch of data into two tables using the DB2 Load utility loading in IXF file types. The data is ...
0
votes
1answer
38 views

how to find tables with either sparse or a column set column?

from the error message below: A compressed index is not supported on table that contains sparse columns or a column set column as I was rebuilding an index and changing the data_compression to ...
0
votes
0answers
6 views

how can I translate a “include” column in a index from sql server to mongodb?

Does MongoDB have INCLUDE columns? in SQL Server let's say we have this query SELECT NAME FROM USERS WHERE ADDRESS ='LIGHT STREET' then I can create an index on ADDRESS and INCLUDE NAME. So , does ...
0
votes
1answer
72 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
36 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
52 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
254 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
507 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
29 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
112 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 ...