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…
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…
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…
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…
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? 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…
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…
I created an immutable function as below: CREATE OR REPLACE FUNCTION GetCurrentDate() RETURNS DATE AS $p$ BEGIN RETURN CURRENT_TIMESTAMP::DATE; END; $p$ LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER; And I use this in a UNIQUE INDEX's WHERE clause like: CREATE UNIQUE INDEX…
I have the following query: select count(*) as aggregate from users where date(created_at) < '2024-04-01'; It returns 2337355 which is a correct number. But it takes over 1.5 secs to be executed. Any idea how can I make it more…
In the database, there is a field that stores JSON, and the content is roughly as follows: "content":{"systemToken":false,"timezone":"America/Los_Angeles","totalCount":false,"userIds":[]}" The requirement is to query data where totalCount is true. SELECT * from task where column_a like '%"totalCount%' This query returns results,…