513 questions
3
votes
1
answer
68
views
Is a clustered primary key in InnoDB considered a covering index when selecting only PK columns?
I have a table like this (InnoDB):
CREATE TABLE group_member (
group_id INT NOT NULL,
member_id INT NOT NULL,
content TEXT,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
...
- 55
1
vote
1
answer
32
views
Character and integer performance differences in MySQL clustered index
I would like to know the difference between character type and integer type when configuring MySQL clustered index.
If you look at the principles of clustered index, it's a sequential configuration.
...
- 25
0
votes
3
answers
469
views
SQL update query sometimes too slow
When update statement, the execute time sometime taking 60 second sometimes taking 1-2 sec. Total row count around 18 million. CARDID is our PK. Almost each 2-3 sec our system send a request to that ...
- 13
0
votes
1
answer
145
views
External fragmentation when we do not have clustered index
If we don't have a clustered index, and consequently, the data is physically stored in heap order, and we create a non-clustered index on the heap. Data could store everywhere without any limitation. ...
- 3
0
votes
0
answers
219
views
DELETE Command taking a lot of time with clustered index
I don't have large amount of data in my table. it contains only 16000 records. I want to delete around 33 records from this table which is taking 9-10 seconds. Below is the schema of the table and ...
- 139
0
votes
1
answer
317
views
What is the difference clustered vs non-clustered index when we calculate number of accesses pages?
Employee (Ssn, FirstName, LastName, Gender, Age, Salary, DepartmentID)
Assumptions:
• There is no index.
• There are total 8,000,000 rows.
• 250,000 rows fall within the 2,500 to 3,000 salary range.
• ...
- 11
-2
votes
1
answer
275
views
Sqlserver how to create composite unique key with one of column is nullable
i am not expert with indexing.
I would like to create a composite key unique constraint. How to create if one of the column is nullable?
CREATE UNIQUE CLUSTERED INDEX [IX_User_Email_PeronsId] ON [dbo]....
- 6,602
2
votes
1
answer
1k
views
when exactly does a lock pseudo-record supremum occur?
I need a example, please
What do you mean by applying the lock to a pseudo-record?
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-next-key-locks
For the last interval, the next-...
user19551894
2
votes
1
answer
2k
views
InnoDB Locking - Does record lock use indexes?
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks
Record Locks
A record lock is a lock on an index record. For example, SELECT c1
FROM t WHERE c1 = 10 FOR UPDATE; ...
user19481364
-2
votes
1
answer
99
views
Can table have both primary & clustered index together
Say we have a Student table in a MYSQL database and its schema is below. This table has around Million Records.
ID (PK)
Name
Aadhaar_ID
Address
Admission_year
Can the above table have the following ...
- 241
1
vote
1
answer
625
views
MySQL Clustered vs Non Clustered Index Performance
I'm running a couple tests on MySQL Clustered vs Non Clustered indexes where I have a table 100gb_table which contains ~60 million rows:
100gb_table schema:
CREATE TABLE 100gb_table (
id int ...
- 15
0
votes
1
answer
143
views
If primary key is clustered index in a table then the other columns are eg any unique column is a table is non clustered?
CREATE TABLE people(
personID int,
FirstName VARCHAR(255),
LastNanme VARCHAR(255),
Address VARCHAR(255),
City VARCHAR(255)
PRIMARY KEY (PERSONID)
);
0
votes
1
answer
345
views
Unique clustered index vs primary key
I have a table with composite primary key of 7 fields, but table is allowing duplicate entries with primary key. Later I noticed it also has Unique clustered index with 10 fields including 7 of ...
1
vote
2
answers
808
views
What is stored in the leaf node of clustered index
I understand that in the leaf node of clustered index the table record is stored together with say primary key.
But I found some articles stated that primary key is stored with block address of real ...
- 175
0
votes
2
answers
679
views
Clustered Index and Sorting
If you run the following query, for example, the rows could come back in any order:
select *
from [table_a];
A clustered index sorts the table based on whatever column(s) you choose.
That being said, ...
0
votes
1
answer
977
views
Unique constraint and index
I have a table in SQL Server containing some user related info where the primary key is id (auto increment by 1) and has a column named userId. Each user can only has one record in the table, so I ...
- 722
0
votes
1
answer
167
views
Clustered index in SQL Server : any advantage for the columns to be first in schema?
I have a table in SQL Server with a three-column clustered index.
I have a table with columns (CustomerID, A, ProductID, C, OtherID) and I have a clustered key on (OtherID, CustomerID, ProductID).
Is ...
- 1,597
0
votes
0
answers
51
views
Index Strategy - Table Design Hourly Data
I am working on a financial project and I am trying to design a table with best index strategy which stores hourly data and I need faster data retrieval. Since its confidential I will explain with an ...
- 3
0
votes
0
answers
96
views
Should we fix our schema to avoid using uniqueidentifiers as our Primary Keys if we have SSD disks?
Having inherited a system and addressing performance issues, noticed that for many tables (some with 1-2 million rows) the Primary Key is a GUID and new GUIDs are not set to be Sequential.
Being aware ...
- 2,524
1
vote
0
answers
98
views
Hibernate Search: Index is not updated (works on local cluster but not on dockerized containers on AW$)
Currently using Hibernate Search 5.10, I prepared it to use an infinispan as a directory provider, and in a local 4-cluster JBOSS, it works okay.
However, when the containers are on AWS and there's ...
- 11
2
votes
1
answer
645
views
Heap vs Clustered index full table scan
I've been googling back and forth for this, and could not get a grasp of how are the table data blocks structured on the disk.
Many resources state that doing a full table scan reads the blocks ...
- 323
0
votes
1
answer
39
views
Can I cluster these records without having to run these loops for every record?
So I want to cluster the records in this table to find which records are 'similar' (i.e. have enough in common). An example of the table is as follows:
author beginpage endpage volume ...
0
votes
0
answers
256
views
Recalculation the cluster centroids
The first step of k-medoids is that k-centroids/medoids are randomly picked from your dataset (X), how those centroids are recalculated when there are some clusters changing by the updating x1? ...
0
votes
1
answer
957
views
MySQL-Why does this query use full index scan?
I'm currently studying DB index.
In the MySQL classicmodels db, I don't know why does this query use full index scan.
select salesRepEmployeeNumber, count(*)
from customers
where ...
- 23
1
vote
2
answers
207
views
Does clustered index have a separate index file? [closed]
Do clustered indexes have a separate index file or is it the table itself?
I read from my textbook that clustered indexes change the physical ordering of the actual file. So does it need a separate ...
- 145