Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [clustered-index]

A type of index mostly used in SQL-Server, which aligns the data of a table with the index.

Filter by
Sorted by
Tagged with
-1 votes
2 answers
38 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 ...
Anentropic's user avatar
0 votes
1 answer
16 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) );...
umarkaa's user avatar
  • 37
0 votes
2 answers
116 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 ...
user1589188's user avatar
-1 votes
1 answer
56 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 ...
aswine's user avatar
  • 143
1 vote
3 answers
108 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 ...
vacip's user avatar
  • 123
0 votes
1 answer
35 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 ...
pepr's user avatar
  • 143
0 votes
1 answer
230 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 ...
Fajela Tajkiya's user avatar
0 votes
2 answers
234 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 ...
Vid's user avatar
  • 1
0 votes
1 answer
89 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 ...
sujeet's user avatar
  • 235
2 votes
1 answer
92 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-...
Paulie D's user avatar
1 vote
2 answers
259 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 ...
Craig's user avatar
  • 503
0 votes
1 answer
83 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 ...
Nuno's user avatar
  • 809
0 votes
1 answer
88 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 ...
Craig's user avatar
  • 503
1 vote
2 answers
64 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 ...
Borisav Živanović's user avatar
-1 votes
1 answer
370 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,...
Kevin's user avatar
  • 99
0 votes
1 answer
470 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 ...
userfuser's user avatar
  • 149
0 votes
0 answers
280 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/...
Thomas Ayoub's user avatar
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. ...
Mainecoon21's user avatar
0 votes
1 answer
78 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 ...
Ross Bush's user avatar
  • 505
2 votes
0 answers
73 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 ...
Tom's user avatar
  • 29
4 votes
1 answer
56 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 ...
omerix's user avatar
  • 91
5 votes
1 answer
1k 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" ...
tbasallo's user avatar
  • 177
0 votes
1 answer
111 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 ...
omerix's user avatar
  • 91
3 votes
2 answers
630 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) ...
fmi21's user avatar
  • 133
0 votes
1 answer
81 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 ...
Daniel L. VanDenBosch's user avatar
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, ...
Nuno's user avatar
  • 809
0 votes
1 answer
551 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 ...
Hamza Hamdani's user avatar
0 votes
2 answers
269 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 ...
Vitor C's user avatar
2 votes
2 answers
656 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 ...
Aryaveer's user avatar
  • 123
0 votes
0 answers
75 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 ...
Avi's user avatar
  • 265
0 votes
1 answer
162 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"...
user2864740's user avatar
0 votes
2 answers
307 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 ((...
Nick's user avatar
  • 173
0 votes
2 answers
276 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 ...
Nick's user avatar
  • 173
0 votes
0 answers
137 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 ...
sTTu's user avatar
  • 146
-1 votes
3 answers
894 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 ...
user avatar
1 vote
1 answer
358 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 ...
variable's user avatar
  • 3,634
0 votes
1 answer
393 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 ...
Aleksey Vitsko's user avatar
-1 votes
1 answer
726 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 ...
Chicken Sandwich No Pickles's user avatar
0 votes
1 answer
94 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, ...
scarr030's user avatar
  • 115
1 vote
0 answers
620 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 ...
sanjay's user avatar
  • 11
2 votes
0 answers
707 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 ...
Rauf Asadov's user avatar
  • 1,273
18 votes
4 answers
8k 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 ...
User M's user avatar
  • 283
2 votes
2 answers
328 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 , ...
Eliy Arlev's user avatar
0 votes
1 answer
972 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" ...
Anish Sapkota's user avatar
0 votes
2 answers
54 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 ...
AccidentalDBA_CO's user avatar
5 votes
2 answers
183 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 ...
IronicMuffin's user avatar
3 votes
2 answers
475 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 ...
Metaphor's user avatar
  • 906
-2 votes
1 answer
125 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 ...
nightcoder's user avatar
1 vote
1 answer
743 views

Should I rebuild index in non clustered index?

I'll be rebuilding our index in SQL Server since our fragmentation rate on all indexes for all tables are so high. Should I include to rebuild my non-clustered index also? Or rebuilding of clustered ...
Dwightqwerty's user avatar
0 votes
1 answer
562 views

Does using PK and FK effect performance in negative way? [closed]

Few companies I noticed do not use PKs and FKs. I don't understand the logic of not using Clustered Indexes and I hear some say it can cause issues because FK couples records together and takes up ...
Habib's user avatar
  • 11

1
2 3 4 5
7