I have a query like this: EXPLAIN SELECT b.* FROM bills b WHERE (b.id IN (SELECT u.id FROM bills u WHERE u.updated BETWEEN '2023-12-19 14:17:48' AND '2023-12-19 14:27:40' AND u.organisation_id = 'ABC123') OR b.id IN (SELECT c.id FROM bills c…
I'm writing a query in MySQL for a Grafana dashboard, but the performance of only this particular query is very poor. This is the query: SELECT br.reading ,CONCAT(ino.project,"_",SUBSTRING_INDEX(br.sensor,"_",-1)) AS new_metric ,ino.vessel ,ADDTIME(DATE('2023-01-01'),-TIMEDIFF(ino.date,br.datetime)) AS norm_date ,br.sensor FROM db.milestones br INNER JOIN…
I have a three table. When I execute below query it takes 3 to 5 min. SELECT u.Id,cmc.completionstate,cmc.coursemoduleid FROM User u JOIN role_assignments ra ON u.id =ra.userid LEFT JOIN course_modules_completion cmc ON u.id=cmc.userid LIMIT 1000000000 Execution Plan: id select_type table…
I am currently working on a project where a wordcloud is created for the NYT. Therefore I am scraping all the articles from the NYT from their API, and then store dates of the article, the articles href and all…
I have a SQL query which works but is slow. I wondered if there is a more efficient way to express it using joins. Scenario: Tables: productions, scripts and a join table, productions_scripts productions id (int) production (text) scripts id…
I have following timeseries data table, Id Object_Id Datetime Sensor1 Sensor2 1 175 '2022-03-24 22:01:00' 0 0 2 175 '2022-03-24 22:02:00' 0 0 3 175 '2022-03-24 22:03:00' 5.5699381666667 81.342836833333 4 175 '2022-03-24 22:04:00' 5.5668366666667 81.281143 ... ... ... ... ...…
I have two tables T1 and T2. I'm doing a simple inner join select t1.a, t1.b, t1.c from T1 t1 inner join T2 t2 on t1.c = t2.c Table T1 has got 2 million rows and T2 has got 4…
I'm trying to use a compound index on a table to assist in creating daily report counts. My table looks like this: CREATE TABLE inquiries ( id bigint NOT NULL, identity_id bigint NOT NULL, received_at timestamp(0) without time zone NOT…
Using Postgres and Postgis I was experiencing a very slow spatial join using st_equals. Out of curiosity, I tried using = instead, and the join was much faster. Using EXPLAIN, I noticed that the st_equals join uses a nested loop,…
Problem Statement: We will be getting a request of a number (11 digits) and have to efficiently lookup in the database and return a single row (last updated) based on range in which it fits. Also, I have already used…