Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

New answers tagged

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 ...
user avatar
  • 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) ...
user avatar
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() + ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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⁰. ...
user avatar
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, ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 <> @@...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar
-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 ( ...
user avatar
-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 ...
user avatar
  • 333

Top 50 recent answers are included