All Questions
11 questions
1
vote
3
answers
4k
views
How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql
I have legacy tables similar to the following:
employee
------------------------------------
| employee_id | name
------------------------------------
| 1 | David
| 2 ...
- 111
1
vote
2
answers
1k
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 ...
- 109
-1
votes
1
answer
4k
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 ...
- 109
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
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(...
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
2
votes
1
answer
741
views
Delete using join or using sub-query?
I need to delete rows from a table based on what is present in a temporary table.
For me, both of these statements work :
DELETE from main_table where `id` in (select `deletable_id` from ...
- 141
3
votes
1
answer
28k
views
UPDATE a column by COUNT of another table
It might be a naive question, but what is the difference of these two queries and which is preferred?
UPDATE table1,
(SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B
SET table1.Freq = B....
- 4,531
2
votes
1
answer
5k
views
Using OFFSET in a large table by a simple sub-query
The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as
SELECT *
FROM table
INNER JOIN (
SELECT id
FROM ...
- 4,531
3
votes
1
answer
233
views
Minimizing Page Fetches
I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
- 251
6
votes
1
answer
8k
views
mySQL query optimisation — multiple joins or select … where not in (select distinct…)?
Background
I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...
- 165