CODEWITHSUNDEEP
mariadb
bgth
bgth

Reputation: 460

mariadb varchar 120 characters primary key

I have a mariadb (version 10.2.10) table with utf8mb4 enabled. There is a varchar(120) column jobID which uniquely identifies the row. At present, I am using jobID with length of 60 characters(with option to increase in the future). So, I made that into primary key.

Now, I have 60K records. So, when I checked the size of the datafile + indexfile using detail given here, its coming as datafile size almost equal to indexfile. Thus, the index file is rising 1:1 as the datafile is rising because of the primary key which I declared.

So, I started thinking of adding a integer auto increment primary key as discussed here and make the jobID column as unique key. But the problem of Index file rising 1:1 with the data file and also the reduction in performance of new inserts to the table as the size of table rises (the table will have 20 million records in the coming days) made me confused. What should be the right approach? Thanks in advance.

My present create table.

CREATE TABLE jobsTable (
 `jobId` varchar(100) NOT NULL,
 `status` varchar(15) NOT NULL,
 `addedTime` varchar(13) NOT NULL,
 PRIMARY KEY(`jobId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Upvotes: 0

Views: 437

Answers (1)

Rick James
Rick James

Reputation: 142296

A PRIMARY KEY is, by definition (in MySQL), UNIQUE. It sounds like you have this:

CREATE TABLE ...
    ...
    PRIMARY KEY(jobID),
    UNIQUE(jobID) -- redundant, and a waste of space; DROP it.
...

In InnoDB, the data and the PK coexist ("clustered"). But every "secondary" key takes up more space.

If that does not explain it, then ... Can't answer without seeing SHOW CREATE TABLE. And what does a sample jobID look like?

Depending on the number of secondary keys, and what they are like, I may recommend for or against using an AUTO_INCREMENT PK.

Upvotes: 1

Related Questions