We are find a specific query running very very slow if the LIMIT value is larger than the number of returned rows. We are running MySQL on RDS - 8.0.mysql_aurora.3.02.0 and have no other performance issues. Example 1 - query…
I have a very simple two-table set in Postgre 16.0: CREATE TABLE fk_table ( fk_id INT PRIMARY KEY , fk_name VARCHAR(50) ); CREATE TABLE main_table ( id INT PRIMARY KEY , date TIMESTAMP , fk INT REFERENCES fk_table(fk_id) --, tens…
Context To describe the situation I find myself in currently, let's take the 3 tables relevant to this: CREATE TABLE steam_user ( id SERIAL PRIMARY KEY, username TEXT NOT NULL, steam_id TEXT NOT NULL, profile_url TEXT NOT NULL, avatar_url TEXT…
I want to have a case-insensitive unique key on a large table. I'm looking for the fastest approach. I found two options: my_table_a with a generated column and my_table_b with a unique index CREATE TABLE my_table_a ( my_table_id_a int GENERATED…
I am trying to retrieve data using below query which is running forever. basically I am doing student level grouping by passing parameters from t4 tables rows. t1 row count: 25 million CREATE TABLE `t1` ( `id` int NOT NULL…
I have a table race_racer that contains time serialized logs for each racer in a race. I am trying to select the lastest record for a given racer in x number of races prior to a date. Basically selecting their…
SELECT d.USPS_IMB AS IMb, d.CorrelationId, d.EINNo AS EIN, d.UniqueFormId AS FormId, d.ProductionFacility AS Facility, CASE WHEN d.ProductionStatusNo < 5 THEN 'Received' WHEN d.ProductionStatusNo = 5 THEN 'Printed' WHEN d.ProductionStatusNo = 6 THEN 'Folded / Sealed' WHEN d.ProductionStatusNo = 7 THEN…
I have a query like select C.customerId, C.firstName, C.LastName, R.IsActive from Customer as C Inner join Rider as R ON R.customerId = C.customerid where R.rideid = 'xyz' and R.rideareacode = 'abc' and R.isActive = 1 This is taking too much…
I want to ask a question, imagine i have this query: SELECT COUNT(DISTINCT (`patient_id`)) AS `num` FROM `visits` AS `Visit` INNER JOIN `patients` AS `patient` ON `Visit`.`patient_id` = `patient`.`id` WHERE `Visit`.`doctor_id` = 21293 AND `Visit`.`office_id` = 14712 AND `Visit`.`started_at` IS…
I have a PostgreSQL table named users which contains between 20 to 30 million records. The table has the following columns: id, tenant_id, name, company, location, and description. I need to perform generic search queries on this table with various…