All Questions

Tagged with
Filter by
Sorted by
Tagged with
0
votes
1answer
27 views

Fetch correct stats from database

I am trying to get count of all quiz users who have made purchases. Main table for storing users is user and quiz_client is linked with user table. sales_order table holds data of users who have ...
1
vote
1answer
44 views

How to use parent table column inside subquery JOIN condition?

I am writing a SQL Query for pulling records from multiple tables for sending the update or added new values email notifications to the user. In below SQL Query I am getting all records which are ...
0
votes
0answers
39 views

Optimizing range join (x between a and b) in MariaDB

My query has a join using a BETWEEN clause and it causes a full table scan to occur if I understand the EXPLAIN output correctly. Query EXPLAIN SELECT g.id, g.name, c.id AS child_id, c.date ...
2
votes
1answer
53 views

Improving MySQL query performance for a large database

have a simple query that do a join between two table. The main table has a date field that my query filter by a range of dates. But for a month period my main table has 37412 datas and for this values ...
0
votes
0answers
19 views

Order of operations in LATERAL join?

Consider the following query to find the (spatial) nearest neighbor; the PostGIS specific components here should not be of importance for my question, or so I believe: SELECT a.id, c.geom FROM ...
1
vote
2answers
46 views

MySQL 5.6 users index instead of range type select on a big table and simple query

We have recently migrated from MySQL 5.5 to 5.6 (5.6.44-86.0-log Percona Server to be precise) and we have a huge problem. Seemingly simple queries run for minutes instead of milliseconds on a table ...
0
votes
1answer
18 views

three tables relation order is correct but values are false

i have those tables table1 | id | name | | 1 | axe | | 2 | bow | | 3 | car | | 4 | dart | table2 table3 | t1_id | ...
2
votes
0answers
67 views

What's the more efficient way of selecting with multi left joins?

I have 4 tables, the first one is "student", the other 3 tables are different records of the student's activities. All tables have a id column as the primary key. student.id is varchar(64) (similar ...
1
vote
0answers
22 views

PostgreSQL - SELECT query with ORDER BY and LIMIT 4 very slow, same query much faster with LIMIT >= 40

I've got following sample query from our Postgres DB: SELECT * FROM advertised_car a0_ LEFT JOIN car_check c1_ ON a0_.car_check_id = c1_.id WHERE c1_.status = 'ACCEPTED' ORDER BY a0_.id LIMIT 4; ...
1
vote
2answers
889 views

How to speed up query with anti-joins

I have a query with 2 anti-joins (UserEmails = 1M+ rows and Subscriptions = <100k rows), 2 conditions, and a sort. I've created an index for the 2 conditions + sort, which sped up the query by 50%. ...
0
votes
1answer
67 views

Slow query with multiple group by

I have a query to get all the comments from threads where a given user has commented and not yet seen the comment. So if user 1 commented to a topic, then user 2 commented after his comment, then user ...
0
votes
1answer
64 views

What and where to index, rapid increasing table postgres

I work as a software engineer with python and django. Currently I am struggling with a design choice made before my time. We have a transaction table that logs all customer activity. Due to the ...
1
vote
0answers
67 views

PostgreSQL - Speeding up Index scan in left join

I have two tables: Order Lines, Orders. Orders table has a Status column, which is text. It can have values like 'Draft', 'Approved', 'Cancelled' etc. I need a query to return pending order lines for ...
0
votes
1answer
61 views

Costly HASH MATCH Aggregate

I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index) Here is the view and the plan. execution plan CREATE ...
0
votes
2answers
167 views

Speed up slow query which uses ifnull, if, sub-query, join and order in MySQL

I have one SP which generates a final query (like the one below) which is taking too much time to execute. Basically it has the following parts: IFNULL to check that if value is null then send ...
1
vote
1answer
98 views

Is there a way to speed up JOIN ON LIKE?

I have millions of things in a table. I'd like to assign groups to the things based on theirs prefixes. I tried to add all the indexes but the planner still does nested loop left join. A demo case ...
2
votes
1answer
56 views

Why does MySQL optimize query with “WHERE … OR” so badly?

Hey there, this is my first question so please be patient if I miss to add something in here. My question is why the OR in the WHERE clause causes the query to be executed the way it is (which is ...
10
votes
1answer
269 views

Why doesn't join elimination work with sys.query_store_plan?

The following is a simplification of a performance problem encountered with the Query Store: CREATE TABLE #tears ( plan_id bigint NOT NULL ); INSERT #tears (plan_id) VALUES (1); SELECT T....
2
votes
1answer
60 views

Speed up SELECT query with JOIN from table with 2.2M rows in PostgreSQL?

There are two tables in PostgreSQL: //has 2.2 millions rows create table account ( account_id text primary key, email text not null, created_at timestamp not null ); //has 1.8 millions ...
1
vote
2answers
99 views

How to use GROUP BY in an efficient way without losing attributes? [closed]

I am working with PostgreSQL. My idea is to group players who appear more than 3 times in table-a. table-a = <player_id, year_world, c, d, e> With the first subquery I get player_id and the ...
0
votes
2answers
199 views

Changing numeric type slow down query big time for postgres

I have 3 tables, leads, lead_addresses and addresses. Scheme looks like this: recently we decided that foreign keys would be beneficial for us so we changed data types for lead_id and address_id from ...
0
votes
1answer
180 views

Query to join two subsets of table data (get it faster)

TABLE1: +-----+----------------------+------+-------+ | id | value |weight| stat | +-----+----------------------+------+-------+ | 5 | OB_4772FB7E65 | 5 | 1 | | 6 | ...
-1
votes
1answer
1k views

Replace Subquery with JOIN - MYSQL

Can you please suggest how i can replace subquery in fetching select columns to improve the performance of query : SELECT sa.id AS patAppIdSA, sa.rte_id AS patRTEId, sa.case_type_id AS ...
4
votes
4answers
145 views

How can I improve the performance of a sorted join?

This feels like such a common question, I'll understand if it is closed but if so please suggest a better place I could ask. I have the following two tables of interest: CREATE TABLE [dbo].[Sessions] ...
0
votes
1answer
427 views

Mysql join heavy query on large tables taking hours

I'm trying to query our entire database to build a report and I've run into the problem that I have queries like the one below that are taking near 2 hours to run and some take even longer. I've ...
0
votes
1answer
83 views

Efficient partial DISTINCT ON

I would like to get the most efficient query to give me all the rows that are distinct on one field (ID) but are identical on another field (validity). Let's set a concrete example: you have a table ...
2
votes
5answers
2k views

How to speed up update join query between 2 mysql tables?

alright long story short., i have powerful dedicated server. Intel I7-6700K - 64GB DDR4 2400 MHz 1x480GB SSD running mysql server along with nginx,php innodb-ft-min-token-size = 1 innodb-ft-...
0
votes
1answer
48 views

Getting stuck with creating better index (Optimize Join)

Im using the below query and it took min 5s to max 11s. I tried to create single column and multi-column index. But still I can't able to increase it's performance. Row count on each table: tbl1 - ...
0
votes
3answers
2k 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 ...
1
vote
3answers
10k views

MySQL join two large tables is very slow

I have two tables, one of them contains a history of downloaded urls, while the other table contains details about each url. The following query groups the URLs by the number of repetitions during ...
0
votes
1answer
655 views

How can I improve performance of the stored procedure?

Stored Procedure CREATE procedure [dbo].[ImproveProcedure] (@port varchar(50), @portdate datetime) as Declare @intdate datetime select @intdate = max(rate_date) from Interestrate ...
1
vote
2answers
1k views

Performance of a big INSERT…SELECT with multiple JOIN

I'm having trouble with a big INSERT...SELECT in my DB. For context, the targeted table already have around a million entries and my SELECT query return approximately 2 millions. On a production ...
-2
votes
3answers
143 views

View slow ONLY if requested in batches

We have a view which includes a sub-query like this: SELECT id AS OrderNo, timestamp AS OrderDate, email, (SELECT TOP (1) reasonId FROM dbo.pr_cancel WITH (NOLOCK) ...
11
votes
1answer
6k views

Undesirable Nest Loop vs. Hash Join in PostgreSQL 9.6

I've a trouble with PostgreSQL 9.6 query planning. My query looks like this: SET role plain_user; SELECT properties.* FROM properties JOIN entries_properties ON properties.id = entries_properties....
-2
votes
1answer
2k views

Sql Server Optimize left Join [closed]

Hello i've these 2 tables : Table Contac_Group ID int Grp_ID int Grp_Name nvarchar(80) Is_Grp_Hidden Bit Table Contacts ID int Contact_Grp_ID int /* this values is from Contact_Group ...
3
votes
1answer
5k views

How to speed up query with joins

I have a couple of tables that I need to join. I have an employees table (~ 400K rows), a companies table (~10 million rows) and a employee_companies table which stores where someone works. Basically,...
2
votes
2answers
143 views

JOIN on LIKE when input udtt count is > 1

This query accepts a User-Defined Table Type variable and then joins that with a table. If the input is a single record, I want the join to be LIKE '%'+input+'%', otherwise it should be =input. This ...
2
votes
1answer
44 views

I've created a view for a report, it now takes 30 minutes to run - Any ideas?

View the actual execution plan here The plan is showing what happens when a stored procedure is called to get the payments that are associated with a client. Unfortunately there is no other way to ...
5
votes
2answers
3k views

Aggregate sales of the past 12 months for the current row date

I need to compute the trailing 12 months sum of sales for a given client_id, based on each row given month. Here is the initial table in a month by month aggregated sales by client (filtered here on ...
2
votes
1answer
2k views

Does joining to the same table twice have a predictable performance impact

I have a simple dimension, for example a date dimension, which is static and unchanging over time. In the fact table, I have several dates. Is it different from a performance perspective to join to ...
5
votes
1answer
287 views

SQL Server OR operator causing massive loop join

The query below is very slow (running over a minute) and I have narrowed down the issue to the OR operator (...OR (EXISTS (SELECT...). I used live execution to verify that there is a nested loop join ...
3
votes
2answers
2k views

How to create an additional boolean column in a view whose value depends on a subquery?

Following is a view that I have created CREATE VIEW [ForMasterTable] WITH SCHEMABINDING AS SELECT [MasterID], [MasterColumnData], [DetailID], [DetailColumnData], [SomeOtherID], [SomeOtherColumnData] ...
1
vote
1answer
1k views

LEFT JOIN on BETWEEN not using indexes

I am trying to join two tables on the criteria that the value one column of table1 is between the values of two columns of table2. Table 1 CREATE TABLE `values`( `id` INT, `name` VARCHAR(50), `...
9
votes
1answer
5k views

Performance comparison between using Join and Window function to get lead and lag values

I have a table with 20M rows, and each row has 3 columns: time, id, and value. For each id and time, there is a value for the status. I want to know the lead and lag values of a certain time for a ...
1
vote
1answer
47 views

Postgres - how to avoid unnecessary comparisons inside a WHERE with a JOIN?

Background We're running this query: SELECT COUNT(*) AS "__count" FROM "xfiler_document" INNER JOIN "xfiler_tx" ON ("xfiler_document"."tx_id" = "xfiler_tx"."id") WHERE ( "xfiler_document"."...
1
vote
1answer
44 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(...
2
votes
4answers
2k views

Update a table faster than an INNER JOIN

I have a SQL Server 2012 stored procedure with an inner join that takes 7,387ms to execute (I saw it in Activity Monitor): [...] declare @childrenIds as dbo.CodeIdList; [...] Update dbo.Code set ...
2
votes
1answer
81 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 ...
0
votes
1answer
64 views

Which indexes for joining a table onto itself on two columns

I am using SQL Server 2016, and trying to join a table onto itself, but the query is very slow, and it seems its because SQL Server is refusing to use the indexes. If you'll excuse the crudity of ...
2
votes
2answers
616 views

Is 3 minutes to join a 50 million table too much?

I apologize if this is the wrong place to ask, but I really need some opinion on whether or not my query is good or not. Basically, I need to get a count of old and invalid entries per group, which ...