MongoDB two indexes with different directions
I have one index created { v: 2, key: { xxxx: 1 }, name: 'xxxx', background: true }, But I have a lot of long queries with "sort": { "xxx": -1 }, Will creating an additional index with -1 help…
Javascript – Matrix indexing/flattening
I am given a matrix with dimensions dim = nxmx4. I would like to flatten this matrix to a new 1-dimensional matrix with length l = n*m*4 using 3 nested for-loops. for (let x = 0; x < n; x++)…
Postgresql – Postgres not using index when filtered/indexed value does not exist
In Postgres 16.2 I've a table Product with extra index: CREATE TABLE Product (id SERIAL , name: text, companyId: number, PRIMARY KEY (id)); CREATE INDEX product_company_id ON Product (companyId); The table has about 12M rows. When I search for a…
Postgresql – Optimize query on partitioned table without partitioning key in the WHERE clause
We are trying to optimize a query to a partitioned table, the query looks something like this: SELECT col1, col2 FROM partitioned_table WHERE profile_id = '00000000-0000-0000-0000-000000000000' AND product_id = 'product_a' ORDER BY created_at DESC LIMIT 500; Parent/partitioned table definition: CREATE…
Postgresql – How to make Postgres use an index for a set of values?
I have a table with ~35M rows, and trying to find "processed" records to remove from time to time. There are 14 valid statuses, and 10 of them are processed. id uuid default uuid_generate_v4() not null primary key, fk_id uuid…
MySQL specify primary key sorting order in secondary index
In MySQL 5.7.25 and MariaDB 10.6.15, using InnoDB by default to create below table create table t1( id serial, employee_id int not null, name varchar(20), status tinyint, created_at datetime(3) not null, updated_at datetime(3) not null, event CHAR not null, primary…
Multi-criteria search query optimization in MySQL
I'm using MySQL 8.0.35 and I've two tables in my database: users and clients. Users can be searched by username, firstname, lastname, email, phone, document or even id. The clients table is used to track which users are clients of…
Postgresql – Does having an index on columns that are not used in a select query make a difference
I have Table T T(A , B , C , D) With this query: Select A from T Where B = C Does having an index on D make a difference in the performance of the given query? Does having…
How to Create a Case-Insensitive Unique Compound Index in MongoDB?
How to Create a Case-Insensitive Unique Compound Index in MongoDB? Question Body: I'm trying to create a compound unique text index in MongoDB with the following fields: tenant_id: 1 seller_id: 1 seller_sku_code: text However, I'm encountering an error when trying…
Postgresql – Why Postgres DB sorts, even though I have an index?
This is the table schema: CREATE TABLE IF NOT EXISTS events_table ( id UUID NOT NULL, company_id INT8 NOT NULL, employee_id INT8 NOT NULL, category_type_id UUID, event_type VARCHAR(255) NOT NULL, data_payload bytea NOT NULL, occurred_at TIMESTAMP WITH TIME ZONE NOT…