I have a simple subquery in MySQL which produces a set of codes, something like 'LPR','LFR','LFL' (more codes can be returned). Using this in my main query slows it right down, like it can take a minute or 2 to…
I am working with a MySQL database and I have a column (let's call it status_column) which contains comma-separated values. The possible values in this column are 'NA', 'NON_NA', 'PSG_REQUIRED', or any combination of these, such as 'NA,PSG_REQUIRED' or 'PSG_REQUIRED,NON_NA',…
Can someone explain why the optimizer chooses different indexes if Limit is used? MariaDB > EXPLAIN SELECT dms_meta.docid FROM dms_meta WHERE (dms_meta.metid = 3 and dms_meta.value = '2015-10-01') ORDER BY dms_meta.docid ASC limit 25; +------+-------------+----------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type |…
I have rewritten the below query from using a correlated subquery to using a LATERAL JOIN. It is now faster, and I would like to understand why? This SO answer says that there is no golden rule and that it…
I have a PostgreSQL (v15) DB view that rolls up a bunch of data per user for a single organization, to display a report of data like fees owed/paid per user, etc. This is executed with an org ID and…
I have a request to add new column that will count distinct column named kode_buatan from january to the month in the record (so if the record have date in march 2024 count distinct kode buatan from january 2024 to…
Mysql 8 takes 2 minutes to sort an empty set The following table on 8.0.33 MySQL Community Server CREATE TABLE `eventiAcc` ( `chiave` int NOT NULL AUTO_INCREMENT, `tipo` tinyint NOT NULL, `account` int DEFAULT NULL, `utente` int DEFAULT NULL, `timestp`…
Some background: I'm running the following simple select statement on Postgres 15.3 server with 128GB memory, and contrary to my belief it takes ~6 minutes. The statement in involve two relations, big_table_70m with approximately 70M rows and other_table_50m with a…
When I perform EXPLAIN on the following query to fetch users with bookings SELECT user.id FROM user LEFT JOIN booking ON booking.user_id = user.id AND booking.end_timestamp > 1706878800 AND booking.end_timestamp <= 1706882400 AND booking.status IN ('pending', 'progress', 'done'); I get…
I have two tables "Tasks"(300k rows) and "TaskAssignedRoles"(500k rows). CREATE TABLE public."Tasks" ( "ID" uuid NOT NULL, "RowID" uuid NOT NULL, "Planned" timestamptz NOT NULL, CONSTRAINT "pk_Tasks" PRIMARY KEY ("RowID") ); CREATE INDEX "idx_Tasks_ID" ON public."Tasks" USING btree ("ID"); CREATE…