All Questions
Tagged with subquery optimization
58 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
88
views
Is there a "correct" way for an optimizer to process a subquery if the outer query has additional where filters?
This topic came up for me as the result of investigating an error in Redshift. We have a table containing a column of numbers represented as text, but for rows where no value is present a single ...
- 111
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 ...
-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 ...
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
2
answers
857
views
MySQL multiple subqueries - slow ordering
Any idea how to optimise this query?
It runs very slow when using the ORDER BY (~10 seconds) clause. If I remove the order_by clause, then it executes for milliseconds.
Problem is, that I need to been ...
1
vote
1
answer
93
views
Is there anything I can do to optimise this complex query?
I have a rather complex query that spans several layers of subqueries, the two primary ones fetching data from the exact same fields, only one takes the values themselves, the other takes aggregates.
...
- 151
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
3
votes
1
answer
81
views
Query optimisation
I want to optimize 2 queries from 2 tables(orders, actions) with relation one to many
Now I make one select from orders:
SELECT "id"
FROM "orders"
WHERE "orders"."...
- 142
2
votes
0
answers
628
views
Correlated subquery with common table expressions in SQLite
Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers ...
- 131
0
votes
1
answer
101
views
Optimize query with a subquery (index is not used)
I have following query:
SELECT
`status`, `is_client_coordinator`, id, first_name, last_name
FROM `users` `t`
WHERE t.status!="CLOSED"
and t.is_client_coordinator
or t.id in (select ...
- 103
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
2
votes
2
answers
1k
views
Postgresql expanding view multiple times instead of executing it once
I have a rahter long subquery which I store as a view
CREATE VIEW scans_minmax AS
SELECT tmp.source,
value ->> 'Ok' AS "Ok",
...
- 123
-1
votes
1
answer
2k
views
Aggregate function on subquery
I have got a SALES table that has 4 document types, and they are created with reference to each other in the following order:
Offer -> Order -> Delivery -> Invoice.
For each new row their ...
- 1
0
votes
1
answer
80
views
optimize query -- remove nested selects
How can I rewrite the query to increase performance?
Here is the query:
SELECT flight.*,
DAYOFWEEK(flight.flight_time) AS day_of_week,
IF(SUM(...
- 101
0
votes
1
answer
2k
views
Optimize slow COUNT in MySQL subquery
I have the following query that takes minutes to complete:
SELECT DISTINCT Designation, Model, (
SELECT COUNT( Equipment.EquipmentID )
FROM Equipment
INNER JOIN EquipmentDesignation_Vw ...
- 1
0
votes
1
answer
39
views
MySQL - Optimize subquery in Select, where and order by
Im running a query like below.
SELECT DISTINCT c.`id`
, c.`job-cat`
, (
SELECT COUNT(*)
FROM `jobs` j
WHERE j.`mul_category` IN (c.`id`)
AND j.`ending` >...
- 1,960
2
votes
0
answers
94
views
Is it possible to replace count distinct inside Correlated sub-queries?
I'm trying to optimize the query bellow. The execution Plan is showing multiple sorting before aggregations (I think this is due to the count distincts in the correlated sub-queries)...
Is it ...
- 21
0
votes
1
answer
756
views
How to optimize UPDATE with a nested SELECT subquery?
I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do:
In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
0
votes
1
answer
236
views
How to make subtraction without repeating the subqueries? [closed]
I need to efficiently obtain:
players who play more than one world cup and who have never been champion.
primary-key | foreign-key | pk-and-fk
PLAYER<player_id, player_name, country_player, .....
- 109
1
vote
1
answer
453
views
Logic of Assigning values to variables in Scalar-valued Functions
I am currently working on updating old scalar valued functions with the hope of making them more efficient or changing them into Inline TVFs.
While troubleshooting my rewrites I came across some ...
- 113
0
votes
2
answers
2k
views
handle subquery with joins where same result is to be joined with multiple tables
I have this query which is going through table B twice I want it go through tableB once as I should be able to select all necessary fields from a table once and use that selected result table in ...
- 13
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
5
votes
1
answer
581
views
How to get all records from one table and see if they are used in another without a sub query
I'm trying to write a SQL statement that pulls down all the records from one table and looks to see if they are used in another. SQL is not my strongest language, so I'm sure this is a pretty simple ...
- 191
2
votes
1
answer
1k
views
Best way to move CTE into query so planner can better optimize and I can add indexes speed it up? [closed]
I'm trying to incorporate the CTE into my final query. I can do it, but it slows it down a lot because I have to do the same sub query for CTE d over and over again (I think):
with d AS(
SELECT
...
2
votes
1
answer
40
views
selecting next todo item from child table
I've a table with millions of records, relationship is one (object) to many (object_items).
object_items:
CREATE TABLE `object_items` (
`item_name` varchar(50) NOT NULL DEFAULT '',
`object_id` ...
- 123
0
votes
2
answers
903
views
Optimization by replacing subquery with named subquery having INLINE hint
Let's have these two tables:
create table table_x(
x_id varchar2(100) primary key
);
create table table_y(
x_id varchar2(100) references table_x(x_id),
stream varchar2(10),
val_a number,
...
- 131
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
1
vote
1
answer
969
views
Can't optimize a nested query using indexes
There are two tables containing hundreds of thousands of records: client and task. It's necessary to query the following:
SELECT
id,
(SELECT status
FROM task t
WHERE t.client_id = c.id AND ...
- 121
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
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
4
votes
2
answers
18k
views
conditional subquery
I have the following query:
SELECT id,
email,
first_name as "firstName",
last_name as "lastName",
is_active as "isActive",
password,
access,
CASE
WHEN access < 3 THEN (
...
1
vote
2
answers
119
views
How to query Wikipedia's database to retrieve the main topic and the surface forms of a given title?
First, Wikipedia's database scheme diagram is available here. I have used one of their dumps locally to work with it.
Now, I need a single MySQL query to check both page and redirect tables with a ...
- 113
4
votes
1
answer
532
views
Why is it examining 2.2 billions rows for this delete query with a subquery?
We use the community software Invision Power Suite 4.1.15. The community has 15+ million posts and we've set up the search index table it has to contain a year of posts.
We discovered the following ...
- 63
0
votes
1
answer
60
views
Slow Query With Subqueries
I'm still rather new to doing more than just simple selects in mysql and I'm trying to optimize a rather large query. I was wondering if anyone who's been doing this for a while has any guidance on ...
- 1
0
votes
2
answers
181
views
Optimizing sql server MIN in the select blah where blah IN (SELECT MIN(blah)
is there any effective way to tune the query below?
I have a filtered index on OrganisationID <> 0
Primary key is on another field, not relevant for this query ItemCode varchar(20).
I have tagged ...
- 16.6k
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 - (...
2
votes
1
answer
2k
views
Optimize MySQL Query with multiple subqueries
I have the following Query:
SELECT
p.id AS post_id,
p.status,
p.user_id AS post_user_id,
( SELECT pl.id
FROM postlocks AS pl
WHERE pl.post_id = p....
- 121
1
vote
1
answer
7k
views
Reuse subquery as in another result
In a typical social-network-like application I have the following query that gathers news from a set of followed channels.
The main query gets the news, while the subquery gets the followed channels:
...
- 113
0
votes
1
answer
2k
views
get all the count of sub category from the product table in single query with mysql optimization [closed]
Table structure:
CREATE TABLE some_table (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`product_name` varchar(255) NOT NULL,
`Product_model` varchar(255) NOT NULL,
`product_discount` ...
- 117
1
vote
2
answers
1k
views
simple SELECT EXISTS takes about two hours to execute
I have mysql server hosted. Now, If I issue simple SQL statement
SELECT EXISTS (SELECT * FROM ORDERS WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42' AND PLACE_ID='2' AND PRODUCT_ID='2bc7277f-c7c3-...
- 127
0
votes
1
answer
2k
views
MYSQL join on subquery optimization
This is a distilled version of the query that I am trying to run:
SELECT * FROM table1 t1
INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2
ON t1.id = t2.id;
The above query is incredibly slow ...
- 101
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
-1
votes
2
answers
1k
views
Optimize query or split in two queries and process result separately?
I have this DB schema:
As you may notice already there is no relationship between tables and won't be on the future. If I run the following queries I will get a lot of rows per table:
SELECT COUNT(*)...
- 1,888
0
votes
2
answers
51
views
Which query performs better when searching a row where a column has maximum?
Consider a simple table for example purposes:
person(name varchar, age int)
I have a task to retrieve the oldest person. Which query is better in general for such problems?
Query 1: criteria with ...
- 101
0
votes
0
answers
62
views
Subquery optimization
I was reading through this article - http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
I am working with an existing design.
What I want is to be able to select ...
- 101
2
votes
3
answers
2k
views
Slow subquery in SELECT statement
Having tried many different methods, I can't get my query taking shorter than 40 seconds to run with a dataset of ~11000 rows.
The table is a list of hotel bookings, containing (most relevant) Check-...
- 21
8
votes
2
answers
6k
views
Optimize select on subquery with COALESCE(…)
I have a large view that I use from within an application. I think I've narrowed down my performance problem, but I'm unsure how to fix it. A simplified version of the view looks like this:
SELECT ...
- 183
1
vote
2
answers
621
views
optimization of expensive subquery
The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units:
SELECT
reports.*
FROM
reports
INNER JOIN
...
- 1,879