592 questions
0
votes
0
answers
56
views
Indexing best practice in a large column table [closed]
I have a question about SQL Server indexing strategy.
I have many tables with millions of rows, structured like this:
Field0 nvarchar(5) primary key
Field1 nvarchar(xx)
..
Field300 nvarchar(xx)
...
-1
votes
1
answer
57
views
Why is postgres not using my index on a json column?
I am trying to figure out why postgres stops using my index on a json field when i have a lot of data. Up to 1 million rows, it works fine and uses the index. But when I try the query on a table with ...
- 1,532
0
votes
1
answer
185
views
Create primary index for primary key in postgres with desc ordering
I have a table with time-series data and with a primary key - actually several large tables but this will do as an example:
postgres=# create table foo (x varchar, y varchar, z text, updated ...
- 1,086
0
votes
0
answers
165
views
How to add two index for the same column in DB with db_schema.xml
I have a column name in my db_schema.xml, I want it to have the unique property and also fulltext, when I add these two fields it returns an error that said Index Name already exists, this is because ...
- 41
0
votes
0
answers
75
views
Why do I get a Clustered Index Scan instead of a Table Scan? [duplicate]
I have these simple tables, one without indexes and the other with only a primary key and clustered index on id:
CREATE TABLE [dbo].[MyTableWithoutPk]([id] [int] NOT NULL, [category_id] int NULL)
...
- 36.5k
0
votes
1
answer
49
views
Why is PostgreSQL sorting a seemingly already sorted result set?
I'm trying to optimize the following query for a school assignment:
SELECT
DATE(b.book_date),
SUM(b.total_amount) revenue,
COUNT(DISTINCT(t.passenger_id)) count_passengers
FROM bookings b
...
- 45
1
vote
1
answer
123
views
querying sys.dm_db_index_physical_stats not working
I am trying to check the fragmentation of my table indexes in Azure synapse studio
with query
SELECT
DB_NAME(ips.database_id) AS DatabaseName,
OBJECT_NAME(ips.object_id) AS TableName,
si....
- 59
0
votes
0
answers
56
views
AWS Amplify GraphQl multiple indexes on schema for separate queries
I have a AWS Amplify GraphQl project. A react application. In schema I have a model named "UserEntry". I want to write couple of queries. Let me share the model/schema first.
type UserEntry @...
- 71
0
votes
2
answers
131
views
postgresql index not being used in query
I have AWS RDS PostgreSQL as a database. I have a table for storing childbirth_data data with currently 5M records and another for storing message to child stats (not the actual messages) with 30M ...
- 15
0
votes
0
answers
24
views
Does the PolarDB engine selection of X-Engine not support columnar indexes?
Does the PolarDB engine selection of X-Engine not support columnar indexes?
After selecting the X-Engine for my PolarDB instance and attempting to manage indexes, the following error is displayed:[...
- 11
0
votes
1
answer
85
views
Indexing in MongoDB with Embedded Objects
I'm working on a Spring Boot Project using MongoDB as Database. I'm applying indexing on an embedded object which is slowing down one query but speeding up a similar query.
I have a MongoDB collection ...
1
vote
1
answer
132
views
This query is slow because I think I'm using a lot of id values in the ANY() clause, isn't it?
I have this table in Postgres:
CREATE TABLE public.products
(
id character varying NOT NULL,
created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
name character varying ...
- 4,048
0
votes
2
answers
132
views
How to properly utilize a composite index in JOIN queries?
I am using MySQL Server 8 and have a MyISAM table containing daily statistics for 'cell' objects for a period of 2 years. In the test table described here there are between 51 and 57 cells (rows) for ...
- 459
0
votes
0
answers
28
views
is it even possible to use multiple indices to query a design document view in CouchDB?
I am struggling in creating a design document key to enable flexible queries (involving multiple conditions in AND or OR) of a complex database stored with CouchDB 3.3.2.
After reading related ...
- 1,594
0
votes
0
answers
36
views
How to properly index by (UNIX) day (epochDay) a denornmalized database
It is impossible to keep a UNIX "universal anchor" for days (LocalDate.now().toEpochDay()) like the way UNIX-time does, since the days of ALL the different regions heavily overlap on top of ...
- 1,293
1
vote
3
answers
3k
views
AttributeError: module 'pinecone' has no attribute 'init'
I am following the Pinecone API and I received this error message:
Traceback (most recent call last):
File "/Users/leonardjin/Dev/lodge/openAI/collegeAssistant/academicAssisstants/apush/...
- 11
0
votes
2
answers
441
views
Postgres ignores my gin index for a similarity query
In my table (around 12 million rows), I have a text field called full_name and the id.
I also have the following indexes:
create index entities_full_name_gin_trgm_ops_index
on temp.entities
using ...
- 1,993
1
vote
0
answers
64
views
Why isn't no order (as opposed to ASC vs DESC) allowed for Mongo indexes?
I assume other DBs behave the same way -
{
Collection: db.Collection(VibeChatUser),
Index: &mongo.IndexModel{
Keys: bson.M{
"Email": 1, // specify the field ...
- 99.4k
1
vote
1
answer
58
views
Perfomance impact when using lexicographically ordered string for primary key
How much performance hit is when using string primary key in PostgreSQL (or generally in any whell-known DBMS)?
String is lexicographically ordered on its first half (as string date) and random in ...
- 403
0
votes
1
answer
207
views
MariaDB upgrade 10.3 to 10.9, indexes, eq_range_index_dive_limit and EXPLAIN
I'm currently in the process of upgrading and MariadDB server from 10.3 (10.3.38-MariaDB-0ubuntu0.20.04.1) to 10.9 (10.9.3-MariaDB-1:10.9.3+maria~ubu2004-log)
I've been running the 10.9 version ...
1
vote
1
answer
45
views
MySQL: Is there any way to optimize multiple table ORDER BY with following DB structure?
I have two tables with following structure and indexes (real names are changed for purpose):
mysql> describe table1;
+---------+---------------------+------+-----+-------------------+---------------...
- 205
0
votes
1
answer
194
views
How to maintain data integrity in Laravel Morph relationship on database layer?
In the context of a SaaS-based system, I am designing a role management system using Laravel's Morph relationship technique. This system involves assigning roles to both company entities and admin ...
2
votes
2
answers
155
views
Using index better than sequential scan when every hundredth row is needed, but only with explicit list of values
I have a table (under RDS Postgres v. 15.4 instance db.m7g.large):
CREATE TABLE MyTable (
content_id integer,
part integer,
vector "char"[]
);
There is a B-Tree index on ...
- 7,931
1
vote
1
answer
371
views
What constitutes a concerning large value for the seq_scan column in PostgreSQL's pg_stat_user_tables
I'm trying to identify which tables in my Postgres DB are missing indexes, for which I'm looking at the pg_stat_user_tables. I read that "large" values in the seq_scan column are an ...
- 11
1
vote
1
answer
39
views
Do I need an index on N+1 columns, to efficiently do a SELECT on the N columns, and sort by the other?
Suppose I have a table like this in MySQL:
CREATE TABLE mytable (
column_A [...],
column_B [...],
column_C [...],
column_X INT(11) UNSIGNED DEFAULT 0, -- type not really relevant
...
- 3,068
0
votes
1
answer
272
views
Index SELECT MAX with WHERE
I know that this question was asked many times, but I could not find an explicit answer when there is a WHERE clause as well, and this is very important for me since I will have table with Millions of ...
- 15
0
votes
1
answer
57
views
Optimizing a table's index in MySQL
I have the following table (tracking searches):
CREATE TABLE `searches` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`deleted` tinyint(1) unsigned NOT NULL,
`query` varchar(1024) COLLATE ...
- 3,548
0
votes
1
answer
2k
views
Reindex multiple indexes in postgresql in one query
I can reindex indexes one by one like this:
database=# REINDEX INDEX CONCURRENTLY indexname1;
...
database=# REINDEX INDEX CONCURRENTLY indexname2;
...
How could I run reindex of multiple indexes in ...
- 695
1
vote
1
answer
345
views
Migration to Azure SQL Database created larger indexes than on-prem
Recently migrated an on-prem SQL Server database to Azure SQL database, using Azure Data Studio migration assistant. The process was successful.
However, the size of the final database is much larger ...
- 13
5
votes
1
answer
3k
views
How to fix PostgreSQL errors telling tid from new index tuple overlaps with invalid duplicate tuple?
When inserting new rows to one of my tables (having multiple indexes) from a Spring-Boot application, I sometimes get:
ERROR: table tid from new index tuple (952367,9) overlaps with invalid duplicate ...
- 10.9k
0
votes
0
answers
92
views
Can querying an non-unique index return multiple values?
I have a table
UserId - PK
SubscriptionId
Date
Multiple users can have the same subscriptionId, and I want to query all the user who have this subscription. So I was thinking of adding an non-...
- 5
1
vote
0
answers
27
views
Index mechanism used in GRIDDB
Griddb uses a hybrid indexing system that combines hash indexes and tree-based indexes. What advantages does it offer over a pure hash or tree-based approach? Is it similar to Adaptive Hybrid Index ?
0
votes
0
answers
65
views
How to increase BTREE index creation speed
I have an RDS snapshot of a database that has a 1 TB table. I am trying to create an index on one of the columns. Since this is a clone table (for testing purposes) I decided to use an m6g.large which ...
- 499
3
votes
1
answer
201
views
Does a combined index also index the individual columns?
If in Entity Framework I have the following:
builder.HasIndex(e => new[] { "CampaignId", "CountyId", "StateId" }).IsUnique();
Are the three individual FKs indexed? In ...
- 32.6k
-1
votes
2
answers
49
views
Indexes needed for user advanced filter?
ASP.NET
MS SQL table: tblPets
ID (123456789)
AnimalType (Dog, Cat, Goldfish...)
AnimalName (Rudolf, Ben, Harold...)
CountryCode (US, AU..)
StateCode (CA, NY...)
CityCode (AK, LA...)
IsMammal (True, ...
- 588
0
votes
0
answers
197
views
Postgres gin index on jsonb column not working
I have two jsonb columns in my table: etl_value and user_value. I have jsonb_path_ops gin index on both columns.
"ix_variable_instance_versioned_etl_value" gin (etl_value jsonb_path_ops)...
- 1,638
0
votes
1
answer
108
views
SQL rebuild Index Maintenance Plan is missing the Index Stat Option to set fragmentation percentages?
My maintenance plan is missing the section in the config window that allows you to set the fragmentation > a percentage. Does anyone know how to get this option or is it maybe I'm on too old a ...
0
votes
2
answers
464
views
Create an index on a column that is not part of unique together index
I have this table that has a 'unique together' index:
CREATE TABLE IF NOT EXISTS entity_properties
(
entity_id INTEGER NOT NULL REFERENCES entities,
property_id UUID NOT NULL REFERENCES ...
- 5,214
2
votes
1
answer
39
views
PostgreSQL: If I define 1-to-1 views for each of my tables and query only from my view layer, will the indexes of the underlying tables be used?
I am using PostgreSQL and I want to create a layer of views on top of all the tables in my database schema. I will implement a 1-to-1 mapping of view to table, so these views will not have joins in ...
- 974
3
votes
2
answers
110
views
MySQL SELECT on multiple FULLTEXT indexes. Extremely slow results
10 million rows. MySQL server V. 5.7 Two indexes called "tagline" and "experience".
This statement takes < 1 second:
SELECT count(*) FROM pa
WHERE MATCH(tagline) AGAINST('&...
- 678
0
votes
1
answer
176
views
Is Django db_index helpful in text search queries?
I've the following model in a Django project:
from django.db import models
class Book(models.Model):
title = models.CharField(verbose_name='Title', max_length=255, db_index=True)
authors = ...
- 2,477
0
votes
1
answer
55
views
Configuring indexes in postgres
this is my first time dealing with indexes and would like to understand few things.
I have the tables of the following schemas:
Table1: Customer details
id
name
createdOn
username
phone
address
1
...
- 87
1
vote
2
answers
528
views
Automatically delete documents based on a condition mongoose
I have a database with users collection which contains a verified field in its schema. I want users that have not been verified 5min after account creation to be deleted. How can I do this?
I am ...
-2
votes
1
answer
77
views
Can table have both primary & clustered index together
Say we have a Student table in a MYSQL database and its schema is below. This table has around Million Records.
ID (PK)
Name
Aadhaar_ID
Address
Admission_year
Can the above table have the following ...
- 241
2
votes
1
answer
58
views
Index Not use in basic query
Having the table block:
CREATE TABLE IF NOT EXISTS "block" (
"hash" char(66) CONSTRAINT block_pk PRIMARY KEY,
"size" text,
"miner" text ,
"...
- 56
0
votes
1
answer
247
views
Is there a built-in MariaDB method to export index creation statements in SQL form?
I would like to temporarily disable index updates. This seems not to be possible, so instead I would like to just drop and recreate the indexes after I'm done changing the data. I have found the SHOW ...
- 5,966
0
votes
1
answer
151
views
How to use database index correctly when performing search on multiple fields?
Suppose we have the following model (i'm making a Django example, but i suppose my question extrapolates on any framework):
from django.db import models
class Card(models.Model):
title = models....
- 367
0
votes
0
answers
157
views
Database index that support arbitrary sort order
Is there database index type (or data structure in general, not just B-tree) that provides efficient enumeration of objects sorted in arbitrarily customizable order?
In order to execute query like ...
-1
votes
1
answer
448
views
Index seek instead of index scan for is not null in condition
I have the query below...
SELECT Compra_ID
FROM [Fart_Compras_dss_tracking]
WHERE [local_create_peer_timestamp] IS NOT NULL
CREATE TABLE [DataSync].[Fart_Compras_dss_tracking]
(
[COMPRA_ID] [int] ...
- 17
0
votes
1
answer
106
views
Optimal PostgreSQL single/multicolumn indexes?
I'm trying to determine the best indexes for a table in PostgreSQL. I expect on the order of ~10b rows and ~10TB data.
The table has 5 main columns used for filtering and/or sorting
Filtering: 3 ...
- 15