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…
I am using mariadb to store a large number of measurements. A common query I do selects for a data range. It takes a little long, something like 10 to 20 seconds, and it gets worse and worse, so I…
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…
How do I get the rows for each year by scenario that are associated to the latest scenario for each year while being at-most prior month (future budgets & forecast scenarios exist as well) Fields: scenario (VARCHAR) Distinguishes between budget…
I have a Laravel application where I'm retrieving data from two tables: LoginData and LocationStatus. The LoginData table contains records with a single IMEI, while the LocationStatus table contains multiple records for each IMEI, with a serverDatetime timestamp indicating when…
I am using MySQL 8.0.27-18, with InnoDB as engine. I have a huge database, with few tables containing 100M+ rows in them. For example: TABLE_A with 110M and TABLE_B with 300M+ rows. Sometimes I had to join these tables in…
I have the query (PostgreSQL) which works correctly. with xml_test as ( select '<!--Build-Version: 1.564-REVb37dd024a8-20220530T1418--><tns:Message xsi:schemaLocation="https://appprod.be/Trivia/documents/Docs/XSDs/v1/T013 T013_v1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tns="https://appprod.be/Trivia/documents/Docs/XSDs/v1/T013"> <Header> <Agent>90015299</Agent> <SocialFund>299</SocialFund> <MessageId>T013</MessageId> <Timestamp RequestDate="2022-06-16T23:20:00" ProcessDate="2022-06-16T23:25:20" ResponseDate="2022-06-16T23:25:20"/> <Reference NRK="6e6806740a6ea06d7ca6ae7b31edeb8e" Requestor="90010299"/> <Result>OK</Result> <ReturnCode>ResponseOnly</ReturnCode> </Header> <Body> <Request> <TransmissionMethod>BULK</TransmissionMethod> <SocialFundCode>299</SocialFundCode> <FileNumber>9913894</FileNumber> <FiscalYear>2020</FiscalYear> <FiscalHouseHolds>…
we have in our system a query that supposed to filter reports based on some factors the query is indexed , and when use explain it shows like it uses the indexes but the row numbers are the same amount…