I will be concerned on cnf options only.
This four options are not related to the storage engines but to the client's sessions so my advise is to keep them in a separate paragraph of the cnf file. Their values combined and multiplied by the number of concurrent connections (max possible and max reached) impact the RAM consumption.
Add these composite indexes:
item_t0: INDEX(Qualifier, TargetPK, LanguagePK)
item_t1: INDEX(TypePkString, PK)
It sounds like you currently have INDEX(Qualifier)? If so, remove it as redundant.
For further discussion, please provide SHOW CREATE TABLE. Datatypes, engines, indexes, etc are important for query optimization.
The formula used for "memory ...
"do you think adding an index in the column item_t0.TargetPK will optimize the queries, as it is the one who is changing value from query to another ?"
That is a bad reason for indexing.
What matters is how much the index will help reduce the amount of work required to compute the query. Whether your proposed index will help will mostly depend on ...
In my case, in SSMS server report "Schema changes history" I saw the table was being dropped and recreated in the night. I tracked down the Job and then the Procedure. Sure enough the table was being rebuilt every night without specifying a FILLFACTOR (and thereby using the server default).
I believe this index is confusing SQL Server. The join conditions include
This type of join predicate is generally hard for SQL Server to reason about (greater or smaller, with columns on both sides). The index makes things worse, because it's first key column is f_time and other columns are only included. So for every row from ...
Here is an example using your index:
select * from books WHERE
(title, id) > ('KVFNdl5F', 994364)
order by title, id
Note that the first occurrence of title, id (in the > comparison) must be in parentheses, and the second occurrence (in the ORDER BY) must not be in parentheses.
There may be a substantial impact on the first few queries after the rows were deleted, because autovacuum hasn't finished processing the table yet and queries need to dig through a lot of index entries that belong to deleted rows.
However, these index scans will mark the index entries as "dead", and subsequent index scans will ignore them.
As hinted at by Andrew Sayer, one thing to consider is how many other indexes do you have?...as all the fields of your clustered index will automatically be included on any nonclustered index for that table as well. This is how nonclustered indexes are able to facilitate a key lookup operation when additional fields not stored in that nonclustered index are ...
If [Col1] is unique or is the primary key, leave the clustered index as is.
You'll need to do some investigating to find out if that NCI is providing any benefit. If it's the only NCI it might get used, even if it's not really optimal, but you won't know without doing some research.
I don't think I have ever seen JOINs parenthesized like that. Suggest you change to @Akina's way -- without parentheses. That may even fix the performance problem.
If not, then turn the query inside-out:
FROM ( SELECT ...
ORDER BY timestamp DESC
) AS r
INNER JOIN ... ...
I don't see any point in the HASH partitioning layer here. You might reduce the index tree height by one level, but only by adding another level to the partitioning "tree" height, which is not going to be a positive trade off.
You could get a benefit for doing index maintenance, if you sorted the bulk rows by their future hash partition before ...
Taking into account the parenthesis in FROM clause the query is by fact
INNER JOIN `regression` ON `regression`.`instance` = `instance`.`id`
INNER JOIN `config` ON `config`.`regression` = `regression`.`id`
INNER JOIN `thread` ON `thread`.`config` = `config`.`id`
INNER JOIN `record` ON `record`.`thread` = `thread`.`...
I assume you are storing timestamps in UTC, correct? You may want to try to convert your parameters instead of calling extract for every row. Example:
ts1 = f('2021-06-27 00:00:00') -- f transforms your argument from local timezone to utc
ts2 = f('2021-07-04 23:59:59')
WHERE (statcode = 'NL10937' OR statcode = 'NL10934')