All Questions
Tagged with subquery query-performance
71 questions
0
votes
0
answers
38
views
Performance difference in SQL queries using JOINs with function
I have two SQL queries that are structurally the same, but have a significant difference in execution time.
The first query takes around 500ms to execute, while the second one takes only 50ms.
Both ...
- 33
1
vote
2
answers
92
views
MySQL with subquery becomes slow
I have 3 MySQL tables: products, orders and users.
I want to show all ordered products where not all products are "pre-orders" or "discounts". Note: one user can have multiple ...
1
vote
1
answer
498
views
Parallel index-only scan with two sub-queries is slow
I'm trying to analyze a slow query that uses parallel index-only scan of two filters (using sub-queries). Specifically I'm the number of 'open_questions' that returned by either subplan1 OR subplan2.
...
- 135
0
votes
1
answer
120
views
optimization with subquery not working as expected
I have a MySQL InnoDB database. One table called affymetrixProbeset contains more than 300 million rows.
Querying this table with INNER JOIN to other tables, with an ORDER BY and offset/limit takes ...
0
votes
0
answers
24
views
Mysql Query need to optimize
I have below query having proper indexes (all columns in where clause), But still query is very slow.
Can anyone help me to optimize this mysql query?
SELECT
centerID,
compID,
...
- 1
0
votes
0
answers
135
views
Is the use of a dependent subquery justified in this case?
SELECT a.id,
(SELECT ARRAY(
SELECT c.id FROM table_c c
JOIN table_d d ON d.id = c.id
JOIN table_e e ON e.id = d.id
WHERE a_id = a.id AND ...
- 209
2
votes
4
answers
2k
views
Why is my big Postgres query faster if I separate it into 3 smaller queries?
I have a big/complex query (with an OR clause and 2 subqueries that have multiple joins and an IN condition). Executing the big query is slow.
However, if I break my big query down into 3 smaller ...
- 121
-1
votes
1
answer
2k
views
Optimize 5 million records SQL query (use join or not?!)
I have a table for products that contains almost 5 million records.
I have a column for the product category (product_category). That it is for now of type INT(11) and it is an index and refers to ...
1
vote
1
answer
1k
views
How do I SELECT elements using WHERE with another table's record values in WHERE statement?
Problem summary
Today part of my app crashed, but nothing else stopped with it. I need to find the first and last tracked message ids for each channel during the downtime. I log unix timestamps ...
- 13
0
votes
1
answer
290
views
How to speed up subquery
Let's suppose we have:
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate>='01/07/1993'and o_orderdate<'01/10/1993'
and
exists
(select
*
from
...
- 235
0
votes
1
answer
159
views
How can I improve the run time of a query that groups / display same date data in one row?
I currently have a view that contains about 1k rows of user information. currently i have a query that groups all the same user information by date into one row. Currently this query takes between 20 ...
- 17
0
votes
1
answer
31
views
Improving JOIN with sub-query
I capture Users who comment on the Articles of other Users, as
INSERT IGNORE INTO AuthorCommentators (AuthorID,CommentatorID,Freq)
SELECT AuthorID,CommentatorID,C FROM
(SELECT b.UserID ...
- 4,531
0
votes
0
answers
61
views
How to avoid subquery duplication? Getting multiple rows of subquery
I need to create a view like this:
CREATE OR REPLACE VIEW bayer_pss_dashboard AS
SELECT
m.machine_id AS machine,
t.name AS time_interval,
(
SELECT SUM(TIMESTAMPDIFF(MINUTE, me....
- 101
1
vote
1
answer
404
views
basic subquery evaluation
I am new at subqueries. Is there a general strategy on how basic subqueries are evaluated and executed (besides from sql engines automatically optimizing them). For example, consider this select query:...
0
votes
2
answers
1k
views
Limit rows in Correlated Subquery
I have one big and two small issues with my query I need help with.
First, I want a SQL (postgres if it matters) query that gets SUM of last two ratings of fruits and makes a new column for each fruit ...
2
votes
0
answers
33
views
subquery for history average is slower then expected
I have a MySQL table which contains historic product values. A new record per product is updates every minute. The timestamp field is an INTEGER field with a unix timestamp. There is an index on the ...
- 121
0
votes
2
answers
53
views
Optimize duplicated but negated subquery
I have what I call a 'Hamlet' query in the form:
select * from stuff a
left join otherStuff b on (a.x=b.x) -- lots of joins ommited
where
(b.ToBe = 1 and a.Being in (select Being from stuffThatIs ...
- 101
1
vote
1
answer
1k
views
Why is this postgres query ignoring the index in the subquery?
I have a couple of tables, companies and datapoints. If I query for datapoints from a given company, it uses the index:
sophia=> explain analyze select count(distinct(source_doc)) from public....
- 133
1
vote
1
answer
1k
views
how to replace with default value when querying a JSONB column with a key that is not present in a particular row giving empty result
I have a table that has a jsonb column as 'result' which has an array of objects.
Looks like this for every row:
[{"grade": "A", "subject": "MATH"}, {"...
- 11
2
votes
1
answer
261
views
How to fetch related rows on multiple condition with ranking
I have following table for storing item information in PostgreSQL.
id | name | category | seller | m_age | status | type_id | region |
----------------------------------------------------------...
- 21
0
votes
2
answers
308
views
Inherit INNER JOIN fields in MySql SELECT QUERY
I want to know how I can join one field of an INNER JOIN table in a SELECT QUERY.
Here's what i got so far:
In this query my result would be NameA, NameB and idC, but i want to join tableA and tableC ...
- 15
0
votes
0
answers
235
views
Improve MySQL query efficiency for first row in a group
I've written the query below in MySQL to get the top 10 top landing pages across all browser sessions.
Reading other similar posts about how to access the first row in a group, it seemed like the ...
- 101
0
votes
1
answer
571
views
How to perform query operation on another query result
How to get approved , unapproved , deleted count from the below result using sql query.
SELECT approved,deleted_at,count(id)AS count FROM `payment_voucher`
WHERE company_id = 1 GROUP BY approved,...
0
votes
1
answer
44
views
Query with a few subqueries and appropriate indicies taking, on average, 613ms to execute
I have a ton of queries that do a similar thing as to what I'm doing here, but none of them have the performance issues this one does:
SELECT
(SELECT lead_id FROM leads WHERE to_id = lead_id OR ...
- 147
1
vote
1
answer
48
views
selecting data after filtering out
I am using RDBMS MySQL 5.7. I have 3 tables with the following structures:
Table A - primary table - products:
+----+-----------+-------------+------------+--------+
| id | title | description |...
- 65
6
votes
1
answer
433
views
Why an incorrect JOIN using correlated sub-query is so much slower
I'm doing some fairly lightweight data massaging/cleaning and ran into a problem where one version of JOIN using a correlated sub-query (probably an erroneous one) ran much much slower than what I ...
- 163
-1
votes
2
answers
19k
views
Group by in subquery
So my problem is the following. Consider this query:
SELECT
P.id,
(SELECT barcode FROM product WHERE publisher_id = P.id LIMIT 1) AS barcode_sample
FROM
publisher P
WHERE
P.name LIKE '...
- 3
3
votes
0
answers
2k
views
Why is Postgres choosing a Hash-Join instead of a Nested Join that is 200x faster?
I run the following query against Postgres 9.5 which returns article information along with the currently valid price:
WITH selected_article_rm(id) AS (
VALUES
('80512e52-a406-4a9b-a8df-...
- 256
0
votes
3
answers
80
views
Optimize query to filter active IDs for a given day from big table
I have a PostgreSQL table called users with 4 columns:
user_id integer, -- we can have a lot of the same user_id
name text, -- name of the user
value text NOT NULL, -- ...
- 37
1
vote
1
answer
265
views
conversion of a non correlated sub query to a join statement
Is it possible to get rid of the sub query and convert the entire query into a JOIN query?
SELECT student_id,
COUNT(attendance)
FROM student_attendance
WHERE attendance = 'ABSENT'
GROUP BY ...
- 257
2
votes
2
answers
22k
views
how to add loop in date column in sql through query? [duplicate]
If ID = 01234 and this ID has done transaction in 3 time in a month like this:
***DAY_no Balance***
1/1/2018 5000
10/1/2018 10000
15/1/2018 ...
- 21
1
vote
3
answers
12k
views
Joining two tables by the nearest match
General problem: I want to do a left join on two huge tables, but there is no matching key - I want to join the left table with the "nearest" row in the right table. In this example I want to join ...
- 211
1
vote
1
answer
54
views
Implementing a query, two types of strategies
Tables have not been created yet, but to simplify there is a groups and an items tables. A group contains an item id (and a group id). And several groups may contain the same item id.
For instance
...
- 513
3
votes
2
answers
3k
views
Optimize a query with two range conditions
I have a structure similar to this one:
CREATE TABLE `author` (
`id` int(11) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8 ...
- 131
15
votes
1
answer
474
views
Poor performing subquery with date comparisons
When using a subquery to find the total count of all prior records with a matching field, the performance is terrible on a table with as little as 50k records. Without the subquery, the query executes ...
- 255
8
votes
1
answer
10k
views
Should i use subquery to limit table before a join?
In the case of a join followed by a where clause, would it be better to use a subquery to limit the results, and then do the join? Example:
SELECT *
FROM Customers
NATURAL JOIN Orders
WHERE ...
- 415
1
vote
2
answers
398
views
Can some redundancy in this nested SELECT COUNT query be eliminated?
For the record, this is not a homework question. I am a professional brushing up on my SQL skills.
Given a table called world that looks like this,
I want to find the continents where all countries ...
- 175
2
votes
2
answers
3k
views
Postgres optimizing nested exists
I have a table of events that each take place during a time range, and I'm trying to create a template for a query which can find events that take place within a specified time period of correlated ...
- 185
2
votes
1
answer
148
views
Slow subquery in select join
I have a slow subquery as below (taking 2 min 35.03 seconds)
SELECT *
FROM erp_gl_batch_item i
LEFT JOIN erp_gl_batch b
ON i.gl_batch_id = b.gl_batch_id
LEFT JOIN erp_customer c
ON i....
- 369
5
votes
1
answer
5k
views
Why does adding ROWNUM to a query improve performance?
I have two queries:
1) This query has a ROWNUM column (takes 20 seconds to execute):
SELECT
ROWNUM
,ROAD_ID
,VERTEX_INDEX
,SDE.ST_X(ST_POINT) AS X
,SDE.ST_Y(ST_POINT) AS Y
...
- 1,497
1
vote
1
answer
1k
views
MySQL subquery with IN operator optimization
I have a MySQL query in which I try to get articles with keywords matching those of a given page and that are not already posted to it.
The db tables are :
items : Articles table.
features : ...
- 35
1
vote
1
answer
60
views
How to improve my mysql query execution performance
I have a main table named prog as below:
CREATE TABLE `prog` (
`prog_id` int(11) NOT NULL AUTO_INCREMENT,
`prog_insert_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`prog_edit_date` varchar(...
6
votes
2
answers
124
views
Retrieving data of vehicle drivers that have not been assigned to a job
Struggling with something for the past 24 hours. Needed some genius to shine some light please. I am trying to display a weekly job rota/log for drivers, which will display:
Jobs that have not been ...
- 95
2
votes
1
answer
169
views
JOIN with the last id
I want to select a record from one table (customers table) and join only with the last from other related table (sales table). I know two ways of doing it, but I don't know which one have better ...
- 161
0
votes
1
answer
250
views
A multi-table JOIN query as subquery to a query that JOINS the same tables. Good or bad?
I have the following tables in my postgres database
- power_relations - (id, properties)
- power_relation_members - (id, power_relation_id, member_id, member_type)
- power_lines - (...
3
votes
2
answers
190
views
Create index for multiple WHERE EXISTS subqueries
I have to find an efficient way to write a very specific query for an application that I'm developing.
The idea is the following: Find all the genetic markers for which there exists at least one ...
- 165
7
votes
2
answers
11k
views
best way to avoid redundant aggregate functions and/or group by columns
Suppose I have two tables:
Foo:
id
baz
Bar:
id
foo_id
boom
So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set of ...
- 183
15
votes
3
answers
24k
views
PostgreSQL query very slow when subquery added
I have a relatively simple query on a table with 1.5M rows:
SELECT mtid FROM publication
WHERE mtid IN (9762715) OR last_modifier=21321
LIMIT 5000;
EXPLAIN ANALYZE output:
Limit (cost=8.84..12.86 ...
- 901
5
votes
1
answer
981
views
Storing prices with price history and different specificity (global, user group, single user) in MySQL database
I need to store prices with their history. A price can have different specificity. It can refer to a single user (most specific), group (less specific) and product price (global - least specific). ...
- 151
1
vote
2
answers
4k
views
How to optimise MySQL query that uses dependent subquery
I have a web app that has a custom feed of images. The image queried are based on whether the users have previously seen the image and ordered by a Hacker News style score.
There are only 1000 rows ...
- 111