9
votes
Accepted
Why are these columns specified in the include clause of an index rather than as key columns
Why oh why oh why
Because the missing index recommendations kind of suck, that's why.
The only columns that will end up in the key of an index recommendation are columns in the where clause. Other ...
3
votes
Why are these columns specified in the include clause of an index rather than as key columns
Job_Id is included and indexed because it is used in JOIN condition.
Run_date, Run_time are included because they are used in the expression of the most inner subquery condition, and Run_date is also ...
3
votes
Accepted
Questions about the @DatabasesInParallel parameter for the Ola Hallengren Index and Statistics Maintenance scripts?
Solution
If you want to perform Ola jobs in parallel, then you have to watch out for a couple of things. It's not very clear in the documentation.
You need to download two additional scripts:
Queue....
3
votes
Accepted
potgresql uses only one worker for index creation
No. No one has implemented parallel build for hash indexes.
See the docs:
For index methods that support building indexes in parallel (currently, only B-tree)
So you would need to implement it ...
2
votes
Accepted
Should I use this partitioning/indexing strategy for a table that grows by about a billion rows per year?
id integer [primary key] -- This needs to be BIGINT UNSIGNED
contact_id unsignedBigInteger -- You are hoping for billions of contacts?
Summary tables will be your salvation! For that matter, ...
2
votes
Does the logical query processing change when indexes are involved?
Indexes would indeed affect the physical operations, altering access paths, I/O CPU etc.
However your diagram is logical. Indexes do not change the nature of these operations, only how they might be ...
1
vote
How do I find missing sub-partitions of an index to make main index complete?
While I was unable to actually figure out a way to get Postgres to tell me which partition was missing, I did come across the relispartition flag on the pg_class table that I was able to query on and ...
1
vote
MySQL query optimisation
To speed the query up a little bit you could add a where clause based on your comment
Is "chat.com" always at the beginning of jid? Yes
The jid column is already indexed and the following ...
1
vote
What is the difference between CREATE INDEX and CREATE INDEX CONCURRENTLY in PostgreSQL?
The difference is only in the way the indexes are built, but once that is done, the results is the same. A concurrently created index can have some bloat, but that difference will even out after a ...
1
vote
Accepted
Should a frequently-growing log file be stored as a table or a blob in the database?
Based on the following which is the main concern
An admin of the system should be able to view the log file filtered to
any students, printers and/or time period (to my knowledge this
requires ...
1
vote
Does creating an index on BIT(n) attribute in MySQL make sense?
As of now, the accepted answer is no longer valid, at least for recent Mysql 8 versions. Tested on mysql 8.0.32, and optimizer is using b-tree index on a integer field when bitwise-and (&) is used ...
Only top scored, non community-wiki answers of a minimum length are eligible
Related Tags
index × 3273sql-server × 941
postgresql × 860
mysql × 809
performance × 613
index-tuning × 352
query-performance × 232
optimization × 215
oracle × 169
database-design × 146
innodb × 146
postgresql-performance × 129
sql-server-2008 × 125
execution-plan × 116
sql-server-2008-r2 × 111
clustered-index × 104
mariadb × 90
sql-server-2012 × 88
partitioning × 87
mongodb × 82
primary-key × 74
full-text-search × 71
join × 69
nonclustered-index × 64
unique-constraint × 61