New answers tagged query-performance
1
vote
Accepted
LIKE query with leading wildcards in large MySQL production database
Approach 1: A fulltext index can't do everything a LIKE condition can do. The most common problem I see preventing use of fulltext is that MySQL's implementation only indexes whole words, so if your ...
- 9,373
0
votes
Accepted
Searching against a large list of people at the same time in Postgres
I don't see a case for full text search here.
You can provide your input string as is to this query:
SELECT t.id, firstname, lastname
FROM user_table t
JOIN (
SELECT split_part(fullname, ' ', 1) ...
- 151k
1
vote
Improving a MySQL query that result in a Lock wait timeout exceeded; try restarting transaction
Sargable
If the datatype of executed is DATE, then remove the function call. If it is TIMESTAMP or DATETIME, then change the test to:
executed >= UTC_DATE()
AND executed < UTC_DATE() + ...
- 68.2k
0
votes
Could MySQL perform better on a 10GB network storage with raid vs local HDD?
A Hardware RAID controller with Battery Backed Write Cache makes nearly all writes take essentially zero time.
If your app is reporting on Data Warehouse data, the I/O decreases dramatically if you ...
- 68.2k
1
vote
Should we create new tables instead of filtering 1 large table?
The "Table-per-whatever" Model almost always breaks down eventually.
It's usually when some "Administrative" function needs to go across the 20,000 tables you've built up in one ...
- 6,509
1
vote
Could MySQL perform better on a 10GB network storage with raid vs local HDD?
You won’t get close to SSD speeds no matter how you set up HDDs (well, unless you give them a cache). That said, the impact on your application performance is going to be against your IO time only. If ...
- 2,368
4
votes
Accepted
SQL Server: Is it worth checking for change in a field before the update?
Is it worth it to do the WHERE part to prevent the unnecessary updates?
Yes. Every update to a row even if nothing has actually changed causes a write to the log and potentially to the data file⁰. ...
- 30.1k
5
votes
Accepted
Execution plan with a lot of ComputeScalar operations
The compiler is taking your IN clause and attempting to optimize by removing duplicate values. It does this by taking all of your parameters, sorting them, merging them in order using Merge Interval, ...
- 5,214
3
votes
Accepted
Why is the optimizer not using the clustering index on my table?
Your filter
l_shipdate<='31/08/1998'
Is not very selective, we can see from the plan that it was responsible for removing only 293,696 rows and it ended up needing to use 19,701,655. If it was to ...
- 2,368
10
votes
Accepted
Do datatypes of columns affect query performance?
Yes. And probably a lot.
You never gain performance using character(n)(alias char(n)). You don't gain anything at all because that type is outdated, mostly useless, and discouraged. Related:
Would ...
- 151k
0
votes
How to speed up subquery
You need an index on orders (o_orderdate), which should avoid the expensive sequential scan.
If it doesn't, and the sequential scan is actually slower than the index scan, PostgreSQL is probably not ...
- 32.4k
1
vote
Accepted
Query with EXISTS-subquery taking about 280 ms in 80 % of cases, and less than 1 ms in 20 % of cases
"A search in company_aliases is as fast as a search in companies, so it's a viable workaround to query companies and, if no result was found, query company_aliases. The total query time is still ...
- 2,368
1
vote
Accepted
Optimizing a PostgreSQL query with many small lookups
This lists all pairs of books that were actually bought by at least one person. (All other pairs were never bought together, no point in diluting the result):
SELECT p1.book_id AS book1_id
, p2....
- 151k
0
votes
Optimizing a PostgreSQL query with many small lookups
You can achieve the same with something like
select book_1_id, book_2_id, sum(cnt) person_count
from (
select b1.book_id book_1_id, b2.book_id book_2_id, 0 cnt
from book b1
join book b2
on ...
- 2,368
1
vote
Optimizing a PostgreSQL query with many small lookups
Your query falls under this condition:
The following operations are always parallel restricted:
Plan nodes that reference a correlated SubPlan.
The top nested loop could be parallelized, but it ...
- 31.6k
3
votes
how to find out the `Key Lookups` in the current query plan?
You can use XQuery to filter the XML plan. For example:
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
..........
..........
WHERE er.session_id <> @@...
- 5,214
10
votes
how to find out the `Key Lookups` in the current query plan?
This is not a good idea.
Take it from me. A long time back I coded a check into sp_BlitzCache that found plans with Key Lookups in them, and then compared that operator's cost to the total cost of the ...
- 33.5k
2
votes
Moving from Amazon RDS to own server made queries slow (maybe because heap fetches increased?)
Looks like the visibility map gets out of date quickly, requiring the engine to visit data pages even during the index-only scan to confirm visibility of each row. May be you need to make autovacuum ...
- 21.2k
0
votes
Optimising a query for matching multiple values on a related table
The current plan involves
Do a JOIN, thereby exploding the number of rows into an intermediate table
Shrink back via GROUP BY
Filter down further
See if this provides the correct result and does it ...
- 68.2k
0
votes
Optimising a query for matching multiple values on a related table
Not sure whether this solves your problem:
SELECT players.game_id AS gameId
, GROUP_CONCAT(DISTINCT units.unit_type_id) AS unitTypes
FROM players
JOIN units
ON players.id = units.player_id
...
- 21.1k
1
vote
Accepted
Optimising a query for matching multiple values on a related table
I hope I understood correctly the question, if not please let me know.
Try:
SELECT p.game_id AS gameId
FROM players p
INNER JOIN units u ON p.id = u.player_id
WHERE u.unit_type_id in (1,2)
GROUP BY p....
- 991
-2
votes
Optimising a query for matching multiple values on a related table
select distinct(p.game_id) game_id
from players p
inner join units u on u.player_id=p.id and u.unit_type_id in ('1', '2')
where t.game_id in (1,2,3,4)
avoid misunderstanding
select game_id
from (
...
- 49
-2
votes
Optimising a query for matching multiple values on a related table
The query filter clause:
where (players.game_id IN (1, 2, 3, 4))
AND units.unit_type_id = 1
AND units.unit_type_id = 2
AND units.unit_type_id = 3
Where is for filtering.
Grouping is for ...
- 333
Top 50 recent answers are included