All Questions

Tagged with
Filter by
Sorted by
Tagged with
0
votes
1answer
53 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
61 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
78 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
185 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
54 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
90 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
161 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
413 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
372 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
337 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
216 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
991 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
322 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
545 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
362 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
165 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
532 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 ...