All Questions
Tagged with subquery optimization
47
questions
2
votes
2answers
32 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",
...
0
votes
1answer
57 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 ...
0
votes
1answer
62 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(...
0
votes
1answer
95 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 ...
0
votes
0answers
23 views
How can i remove filesort for this subqueires?
This is the Query:
SELECT
A.*
FROM
messages_thread_user mtu,
LATERAL (SELECT
m.*, u.userName
FROM
messages m
JOIN
...
0
votes
1answer
21 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` >...
2
votes
0answers
27 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 ...
0
votes
1answer
190 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
0answers
55 views
PostgreSQL - Optimize query with multiple subqueries
I have 2 tables, users and sessions. The tables look like this:
users - id (int), name (varchar)
sessions - id (int), user_id (int), ip (inet), cookie_identifier (varchar)
All columns have an index....
0
votes
1answer
62 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, .....
1
vote
1answer
96 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 ...
0
votes
2answers
163 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 ...
0
votes
3answers
42 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, -- ...
4
votes
1answer
414 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 ...
2
votes
1answer
375 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
1answer
29 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` ...
0
votes
2answers
353 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,
...
2
votes
2answers
1k 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 ...
0
votes
1answer
218 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 ...
2
votes
1answer
2k 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 ...
2
votes
2answers
1k 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 ...
3
votes
2answers
7k 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
2answers
55 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 ...
4
votes
1answer
326 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 ...
0
votes
1answer
50 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 ...
0
votes
2answers
143 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 ...
0
votes
1answer
128 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
1answer
1k 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....
1
vote
1answer
3k 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:
...
0
votes
1answer
1k 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` ...
1
vote
2answers
546 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-...
0
votes
1answer
367 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 ...
1
vote
2answers
1k 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 ...
-1
votes
2answers
166 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(*)...
0
votes
2answers
45 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 ...
0
votes
0answers
52 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 ...
2
votes
3answers
1k 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-...
8
votes
2answers
3k 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 ...
1
vote
2answers
533 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
...
0
votes
1answer
82 views
Can this where in subquery be optimized?
Currently the query takes ~ 0.2 seconds. Is this the best it gets? Because it feels rather slow for the amount of data I have in my database. I've tried creating an inner join out of it but I can't ...
7
votes
1answer
9k views
Why does SQL Server run a subquery for each row of the table it's qualifying?
This query runs in ~21 seconds (execution plan):
select
a.month
, count(*)
from SubqueryTest a
where a.year = (select max(b.year) from SubqueryTest b)
group by a.month
When the subquery ...
0
votes
1answer
262 views
Optimizing multiple MySQL sub-queries and joins for multiple runs
The query (below) that's running is taking ~0.6 sec which normally would be fine but it gets run almost 1300 times for different custids. How can I make it run faster?
Setup:
Mac OS X 10.7.5
MySQL ...
2
votes
1answer
1k views
MySQL subqueries that use range based on values of main queries don't use indices properly
I think I've isolated a problem that has been affecting many of my queries lately.
And would like some help to figure out a solution for this.
Ok so my findings are that a normal query that runs very ...
7
votes
1answer
18k views
Subqueries run very fast individually, but when joined are very slow
ypercube solved the problem. Subqueries were totally unnecessary, and the whole thing works with plain joins. It is still strange that MySQL's optimizer could not make use of my original query, though....
6
votes
2answers
2k views
Is there a way to hint to query optimizer to MySQL which constraints should be done first?
This is my current query:
SELECT BusinessID as ID,
111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from
(
SELECT *
FROM
...
1
vote
2answers
152 views
How to restructure this slow query
For the following query:
SELECT l.*
FROM `leads` AS `l`
INNER JOIN `users` AS `u` ON l.client_id = u.id
WHERE (`l`.`lender_id` = 1)
AND (l.claimed_date IS NULL)
AND (`l`.`disabled` = 0)
AND (u....
8
votes
4answers
10k views
MySQL subquery slows down drastically, but they work fine independently
Query 1:
select distinct email from mybigtable where account_id=345
takes 0.1s
Query 2:
Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above ...
