Questions tagged [clustered-index]
A type of index mostly used in SQL-Server, which aligns the data of a table with the index.
330
questions
0
votes
5
answers
62
views
Log table without primary key
I have a SQL Server log table that includes a column "Id"
This is an identity column, but not a primary key, it is not even indexed.
This would have just been set up from some tutorial for ...
- 1
3
votes
3
answers
344
views
Dropping a unique clustered index on identity colum
I'm currently working with a SQL Server database that came from an inherited system. The previous system used a lot of auto incrementing bigint identity fields, let's call the field "KeyID". ...
- 31
-1
votes
2
answers
46
views
How important is the "clustered" property of MySQL PK index?
I am importing ~50M rows into MySQL 8, InnoDB. It's on AWS RDS with GP3 storage.
The unique key of the rows is a uuid-like string.
When querying we will never care about this unique key, except when ...
- 488
0
votes
1
answer
17
views
clustered index detection with EXPLAIN statement
I created two tables with one-to-one relationship as below,
CREATE TABLE if not exists User (
id bigint AUTO_INCREMENT,
coins bigint not null,
level bigint not null,
PRIMARY KEY (id)
);...
- 37
0
votes
2
answers
216
views
Is default ordering affected by clustered index? [duplicate]
My colleague and I are debating whether the default ordering of rows from a SELECT statement (without specifying any ORDER BY) is controlled by the clustered index or not.
He demoed this by creating a ...
- 151
-1
votes
1
answer
90
views
In Postgres, can I CLUSTER by multiple indexes?
I want to order the records in my tasks table according to completed and then according to workOrderID, just like I can to with the ORDER BY clause of a SELECT query, so that this
taskID
workOrderID
...
- 143
1
vote
3
answers
229
views
Azure SQL index rebuild after huge size reduction
We have a table in an Azure SQL database that used to have an nvarchar(max) column storing pdf files. (The wonders of external developers.) The table grew to 156 GB. It has 476000 rows.
After changing ...
- 123
0
votes
1
answer
38
views
Proper way of copying larger data from another Azure database?
Being the 3rd party that is allowed to read from the Azure SQL (in cloud), we need to simulate access to the data as in the older version of the ERP that used plain old MS-SQL server. Only the subset ...
- 143
0
votes
2
answers
420
views
Understanding of the %%lockres%% and %%physloc%% pseudo columns with index hint
I am experimenting with clustered and non-clustered indexes in SQL Server and I have noticed something interesting.
Here is a simplified version of my T-SQL code:
IF OBJECT_ID('dbo.dept') IS NOT NULL ...
- 1,199
0
votes
2
answers
430
views
Partitioning a Table Vs Partitioning a clustered index
If I have a Table in SQL server, with Time series data for every business date (~100k rows a day)
and only the last 45 days are frequently updated/deleted/inserted, it would be better to partition ...
- 1
0
votes
1
answer
102
views
Optimizing PostgreSQL Query with DISTINCT ON, Filtering, and Ordering
I'm working on optimizing a query for a PostgreSQL database used in an educational platform.
The query retrieves distinct question_id values along with their is_seen status from the ...
- 247
2
votes
1
answer
102
views
Pros/cons of two non-clustered indexes on Col1 and Col1+2
In relation to a similar question, please consider the following:
Microsoft SQL Server
Table T has at least 4 columns A, B, C and D
PK is on A (i.e. non-null, unique, and the clustered index)
Non-...
- 21
1
vote
2
answers
275
views
How to choose a clustered index for a fact table
What is a good strategy for choosing a clustered index on a fact or transactional table? I am using SQL Server 2019.
I have picked a generic sales table (FactSales) that has these properties:
no ...
- 503
0
votes
1
answer
97
views
How can I make (game_id, user_id) unique, yet (game_id, score) indexed/clustered, in ScyllaDB?
See this in ScyllaDB:
CREATE TABLE scores_leaderboards (
game_id int,
score int,
user_id bigint,
PRIMARY KEY (game_id, score, user_id)
) WITH CLUSTERING ORDER BY (score DESC);
The ...
- 839
0
votes
1
answer
105
views
Clustered index on primary key vs on non-unique column
As a starting point, is it better to use the surrogate identity column (SaleItemID) as the clustered index. Or look for a column that I expect will be used often when reading data from the table but ...
- 503
1
vote
2
answers
73
views
Does clustered index fragmentation happen in relational DBMS?
Is it possible that after a certain pattern of random inserts and deletes leaf data nodes become fragmented with clustered index?
I.e, that the physical order does not reflect the logical order (by ...
-1
votes
1
answer
600
views
Best indexing strategy for composite primary key with frequently queried columns and occasional additional column [closed]
I have a large table with the following composite primary key:
PRIMARY KEY(col1, col2, col3)
col1, col2, col3 are queried all the time.
Some times (maybe 10% of the time) I get queries for col1, col2,...
- 99
0
votes
1
answer
552
views
Adding a non-clustered, covering index, on a primary key, when clustered index exists
I went through the related articles, suggested by SO, but didn't really find all the answers, so I'll try to be specific here.
Presume we have a (MSSQL) table, with a simple ID (int) primary key, with ...
- 147
0
votes
0
answers
295
views
How to avoid cluster index scan?
I've a table with a few millions of records. Here's the table structure:
Column_name
Type
Computed
Length
Prec
Scale
Nullable
TrimTrailingBlanks
FixedLenNullInSource
Collation
Id
int
no
4
10
0
no
(n/...
- 61
0
votes
0
answers
59
views
Index reorg question
Database table is defined using FillFactor=0 and that was not changed since database deployment several years ago. FillFactor=0 is also a server setting and maintenance job is running every night. ...
- 173
0
votes
1
answer
80
views
Can a Clustered Index based on a key field be more performant than a non-clustered covering index for a big table
I am researching a DW reporting table that will grow very large. For simplicity, I will show the table as follows:
BigTable
--------
TableID INT IDENTITY NOT NULL,
CompanyName NVARCHAR(100) NOT NULL
...
- 567
2
votes
0
answers
75
views
Clustered Index being used over non-clustered with same key
I have a table that has a clustered index (CI) on the field filename.
It also has a non-clustered index (NCI) - With the key column of filename and 5 other columns in the includes. (There are ...
- 29
4
votes
1
answer
64
views
Creating a new view or table from the last record
I have 2 tables DraftProducts and Products.
The only difference between the 2 tables is the "phase" field.
I have productCode varchar(20), phase(tinyint), Name, Price, SpecialCells.... etc ...
- 101
6
votes
1
answer
2k
views
Index Design: clustered index using a DATE
Every once in a while, one needs a check. This is such a time.
I have tables that are used for reporting. They are deleted/inserted once a day or multiple times a day to "refresh the data" ...
- 187
0
votes
1
answer
119
views
Working with varchar field in Clustered Index. (Performance Test)
I am using Microsoft SQL Server 2016.
For my products table;
I need to define a ClusteredIndex with 2 fields of Product Type and Product Code. ProductType(tinyint), ProductCode(varchar(32)).
I know ...
- 101
3
votes
2
answers
674
views
Is a clustered index of 3 columns too big?
My goal is to design a table, that can be queried trough an external id(uniqueidentifier), an internal id(bigint), always in combination with companyId(bigint), userId(bigint) and dashboardId(bigint) ...
- 133
0
votes
1
answer
96
views
Clustered Index Maintenance
I am running SQL Server 2012 and I am wondering if I create a clustered index if, as time goes on and users add records, if the RDBMS will maintain the clustered index on table change or have to do ...
2
votes
2
answers
2k
views
Non-clustered Primary Key and Clustered Index
It is my understanding that in SQL Server, you can have a Primary Key that is non-clustered, and have another index that is the clustered one.
To me, this seems the same as just having a Primary Key, ...
- 839
0
votes
1
answer
663
views
Index Optimization for tables that are truncated and charged everyday
I'm a recently recruited Data Engineer, I have made a bunch of ETL pipelines planned to run daily, the destination tables are truncated and charged again. The DBMS is on-prem SQL Server.
When I ...
0
votes
2
answers
314
views
InnoDB Clustered Index is stored under memory?
firstly i'm newbie about internals databases, sorry if my question is dumb.
I have read in Stackoverflow(post link at final) that InnoDB clustered index is stored in disk and in memory.
And i have ...
- 1
2
votes
2
answers
696
views
MySQL Innodb, why does the size of primary index depend upon the order of insertion of records
I have a table with this schema
CREATE TABLE `UserIdPhoneNo` (
`userId` int NOT NULL,
`phoneNo` bigint NOT NULL,
PRIMARY KEY (`userId`,`phoneNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
I am ...
- 123
0
votes
0
answers
85
views
Adding new column to clustered column store indexed table
I designing clustered column store indexed fact tables.
Later on many new columns might be added to these currently ~90 columns tables.
How adding new nullable column is affected by the size (number ...
- 265
0
votes
1
answer
166
views
Ensure that SQL server "avoids" the clustered index when a non-clustered index can be used
I have a query, the short of which looks like the following. While this is not exact, it should show how the problematic behavior is introduced and what I am looking to prevent. And "prevent"...
- 195
0
votes
2
answers
310
views
Why Is There a Sort on this Column
This query has a plan which includes a SORT and the sort is taking up the majority (77%) of the cost:
SELECT
LFTYLM_STATUS
FROM
LFTYLM
WHERE
LFTYLM_LIS_ID = 138998 AND
LFTYLM_LFTY_ID = 78 AND
((...
- 173
0
votes
2
answers
316
views
Order of Key Columns in Index
The execution plan for this query appears to be making a very bad choice (of course I know it is not):
SELECT TOP 1 E_PER_ID FROM E WHERE E_CUS_ID = 1912 AND E_TYPE = 'R' ORDER BY E_ID DESC
The two ...
- 173
0
votes
0
answers
141
views
Sudden performance degredation fixed with non-unique index property
We are using NServiceBus message queuing service backed by a SQL Server (2016 SP2-CU15) Database.
Yesterday, after functioning without an issue for years, we started to notice server performance ...
- 146
-1
votes
3
answers
992
views
Why MySQL clustered index page size is 16k by default?
If the whole idea of the B+-tree structure, that the MySQL clustered index uses, is to be efficient in accessing disk data. Then it's logical to make index page size equal to the disk block size which ...
mind_religion
1
vote
1
answer
383
views
How does page split result in index fragmentation?
I am trying to understand the reason behind index fragmentation.
Assuming fill factor is 100% and the clustered index is on an integer column. The values on each page are as follows and assuming that ...
- 3,448
0
votes
1
answer
415
views
Clustered Column Store Index - does it have max row size limitation?
Updatable Clustered Column Store Index (SQL Server 2016+) - does it have max row size (8060) limitation, or you can put any row size you want in there ?
In other words, is it possible to get below ...
- 5,884
-1
votes
1
answer
840
views
Clustered Index and Sorting [duplicate]
select *
from [table_a];
A clustered index sorts the table based on whatever column(s) you choose.
That being said, if I have a clustered index on [column_a], [column_b], and [column_c] and run the ...
0
votes
1
answer
116
views
Is this horrendously low row estimate causing a slow insert?
reference: https://www.brentozar.com/pastetheplan/?id=Bklc3fGNt
I have a clustered index on both #LargerRange and #SmallerRange. These indexes contain 5 key columns: Name, InOrOut, LocCode, VisitID, ...
- 115
1
vote
0
answers
699
views
DACPAC: Altering primary key results in rebuilding tables during deployment
Question: Is there any better way to avoid rebuilding of table when clustered Index column is changed
Details:
We have Database project with all default settings.
There are tables with primary key ...
- 11
2
votes
0
answers
750
views
How to estimate the nonclustered index size before its creation?
I created two scenarios and expected to see the same result but the results were noticeably different. I would like to know where I did wrong.
Scenario 1
Create table mytable1 with clustered index and ...
- 1,303
18
votes
4
answers
9k
views
Is it a good idea to Partition a table (in MS SQL) based on date, when there's a clustered index present on ID (INT)
I have a table in MS SQL Server .
Table Size: 806 GB
Rows : 1.2 billion
Index Space : 1.2 GB
Table Usage: Logging from the Web Service calls
99.9% is usage is from the logging, developers look ...
- 283
2
votes
2
answers
369
views
Is the order of columns in a clustered index important?
Does it matters which field I specify first in the index?
would
CREATE SET TABLE my_table
(
validity_date DATE
, branch_id SMALLINT
, account_class_id BYTEINT
, ...
- 125
0
votes
1
answer
1k
views
High CPU usage in AWS RDS
When I visit the woocommerce orders page in WordPress, MySQL RDS CPU usage goes to 100% but the website is working perfectly fine.
In 'active sessions' section, "wait/io/tables/sql/handler" ...
- 101
0
votes
2
answers
57
views
Index Drop for Partial Overlap?
I'm working with a 50mil row table with the following:
Clustered Index keyed on [Col1]
Nonclustered Index keyed on [Col1], [Col2] (no includes)
It seems like the right call here would be to drop #2 ...
- 157
5
votes
2
answers
202
views
Unexpected update results on heap using SET @Variable = Field= @Variable + 1, fixed with clustered index
I'm just looking to understand why this is happening, and my Google searches were failing me. We are on SQL Server 2016 SP1.
This is the situation: Vendor table that manages IDs by keeping track of ...
- 663
3
votes
2
answers
524
views
How is the clustered index different from the table itself?
If the SQL Server clustered index is the physical order of the table and all columns are included, is it the table itself? How is the clustered index stored physically?
I have seen the Q & A What ...
- 906
-2
votes
1
answer
131
views
Will index on a partition that doesn't include partition column work fast?
Let's say I have a huge table with columns [CloseOfBusinessDate], [DataSourceID], etc.
[CloseOfBusinessDate] is a partition column and I have a clustered index on that column (on ...
- 97