Looking to optimize your database’s performance and efficiency? Generated Invisible Primary Keys (GIPK) might just be the solution you’re looking for. In this article, we’ll explore how GIPK can simplify the process of adding primary keys to your database, enhancing your data integrity and improving your database’s overall functionality. So, keep reading to learn more and take the first step towards a better database today

Problem Statement

While deploying MySQL, usage of Primary Key becomes inevitable.
Primary key is an integral part of any relational database.
In a table with a huge set of records, if a Primary key is not present, performing bulk writes will create a mess in the system. The result might be a huge replication delay.

Why GIPK?

Adopting a primary key straight away, might not be a preferable option.
Invoking a new primary key might require an application or code level review and testing, etc.
While performing a change in the database system, the fear towards schema compatibility with respect to application, might be a blocker in creating new primary keys.
There can be more such reasons leading to the avoidance of a primary key, GIPK will help us overcome these hurdles, with only a little effort.

Generated Invisible Primary Keys (GIPK)

It eliminates the need to maintain a separate column for the primary key in a table. While using GIPK, an invisible column with a primary key will be automatically created, if that particular table was created without a primary key.

What are Generated Invisible Primary Keys ?

It is an invisible primary key for a table that is created without a primary key column.
This was introduced in MySQL version 8.0.30.

How Generated Invisible Primary Keys Works ?

Similar to invisible columns, Generated Invisible Primary Key (GIPK) creates an invisible primary key with the default column name ‘my_row_id’.
‘sql_generate_invisible_primary_key’ is a dynamic global variable, while set as ‘1’ it will automatically add a generated invisible primary key column to any table created.
The variable ‘sql_generate_invisible_primary_key’ will have a default value of ‘0’.

Advantages:

Improved performance: Database can skip the overhead of updating the primary key during an insert or update.
Data Integrity: The invisible primary key ensures data integrity, even though it is not visible in the table structure.
Ensures no two records have the same identifier in cluster environments. Primary key is mandatory in clustered environments.

Limitations:

GIPK is available in newer versions of MySQL only, i.e 8.0.30 onwards, tools and applications using lower versions of MySQL do not support invisible primary keys.
Only InnoDB tables support GIPK.
Understanding the relationships between tables and data can be complex if the primary key is not visible.

Working Sample:

Set ‘sql_generate_invisible_primary_key’ server variable as 1.

SET sql_generate_invisible_primary_key= 1;

Check if Generated Invisible Primary Keys is enabled.

SELECT @@sql_generate_invisible_primary_key;

Note:
‘1’ defines enabled.
‘0’ defines disabled.

Creating a table without primary key constraint.

CREATE TABLE test1 ( name varchar(20), age int ) ;

View CREATE TABLE to check GIPK column.

SHOW CREATE TABLE test1 \G ;

Automatically generated primary key column is created as ‘my_row_id’.

Inserting record into table.

INSERT INTO test1 ( name, age) VALUES (‘abc’,10),(‘abd’,22) ;
SELECT * FROM test1 ;

Now the record is inserted with GIPK.
By default the primary key is invisible.
To view the inserted primary key, an alter table command is used.

ALTER TABLE test1 ALTER COLUMN my_row_id SET VISIBLE ;

Summary:
GIPK is an invisible primary key that is automatically created for tables without a primary key column. It improves database performance, ensures data integrity, and prevents replication delays. However, it is only available in newer versions of MySQL and is limited to InnoDB tables.

At Mafiree Database Support Services, we provide expert assistance in managing your database, ensuring optimal performance and maximum uptime. If you need help with your database management needs, don’t hesitate to contact us at sales@mafiree.com.

Author Bio

sukan

Sukan is Database Team Lead at Mafiree with over a decade of experience in database systems, architecture, and performance optimization. He specializes in MySQL, MongoDB, TiDB, and ClickHouse, developing architectural improvements that make data platforms faster, more efficient, and cost-effective. Sukan writes about practical database engineering topics, real-world performance tuning, data replication, and high-scale system design, drawing from extensive hands-on experience solving complex technical challenges.

Name *

Email *

Comment *

Slow MySQL incident resolution is often a symptom of a weak support model—not just a technical problem. Learn how to identify gaps in escalation, monitoring, automation, DBA expertise, and post-incident reviews before they lead to recurring downtime.

71 views

MySQL Deadlock Analysis: Diagnosing and Resolving Lock Contention in High-Concurrency Workloads

A MySQL deadlock occurs when two or more transactions hold locks that each other needs, creating a circular dependency that prevents them from proceeding. InnoDB detects the deadlock and rolls back one transaction; applications typically receive MySQL error 1213 and should safely retry the transaction. To diagnose a deadlock, start with SHOW ENGINE INNODB STATUS; for recurring incidents, use innodb_print_all_deadlocks and Performance Schema data_locks and data_lock_waits to investigate lock relationships. Common causes include inconsistent transaction ordering, long-running transactions, and inefficient indexing or locking patterns. The most effective fixes are to keep transactions short, access shared resources in a consistent order, optimize the indexes used by locking statements, and implement safe retry handling.

441 views

MySQL Consulting vs In-House DBA: Which Is Right for Your Team?

Choose an in-house DBA when you need continuous database ownership, deep knowledge of internal systems, and day-to-day operational control. Choose MySQL consulting when you need specialized expertise, performance tuning, migrations, complex troubleshooting, or additional database capacity without hiring a full-time specialist. A hybrid model can be the best fit when your team handles routine database operations internally but needs external expertise for complex projects, peak workloads, or specialized MySQL problems. The right choice depends on workload consistency, technical complexity, required coverage, internal expertise, scalability, and total cost—not simply the hourly or salary cost. Mafiree's managed database services provide ongoing MySQL support alongside specialized database expertise for organizations that need more flexibility than a purely in-house model.

292 views

MySQL Consultant: When to Call in a Performance Expert

Most MySQL slowdowns are fixable in-house. A smaller set of problems keep coming back, keep growing, or sit outside what routine tuning can solve — and that's the line worth knowing before an incident forces the decision for you.

364 views

MySQL Performance Issues: 7 Signs You Need Professional Tuning

MySQL performance issues are rarely sudden — they build over time through slow queries, InnoDB buffer pool misses, replication lag, lock contention, thread pile-ups, tablespace bloat, and unstable query plans. This post identifies the seven most reliable signs that your MySQL environment needs professional DBA attention, with diagnostic queries and remediation guidance for each.

13245 views

Column-Level Security: Enterprise Data Protection Without the Infrastructure Overhead

Column-level security is a native database feature that restricts access to specific table columns by user role. Mafiree implemented this for a client as a cost-effective replacement for a planned CDC replication architecture that existed solely to strip sensitive columns. The result: zero additional infrastructure, single source of truth, full GDPR/HIPAA compliance posture, and validated in production with no performance impact.

1256 views

MySQL Schema Migration Without Downtime: A Real Fintech Case Study

Schema changes on large MySQL tables can bring production systems to a halt if not handled correctly. This case study walks through how Mafiree helped a fintech client execute a zero-downtime MySQL schema migration on a 500M+ row production database — covering the real challenges faced, the three-phase tool strategy using gh-ost, pt-online-schema-change, and MySQL 8.0 INSTANT DDL, production configuration settings with performance benchmarks, and best practices for safely evolving your MySQL schema without impacting users

4382 views

MySQL Architecture Explained: Performance Tuning & Troubleshooting Guide

MySQL features a unique tiered architecture that separates query processing from data storage through its pluggable storage engine model. This guide explores the core components—from connection handling and the SQL optimizer to the physical storage of data on disk. By understanding how engines like InnoDB provide ACID compliance and row-level locking, you can significantly improve your database's scalability. We also break down the query execution workflow and provide actionable tips for performance tuning, such as optimizing the buffer pool. Whether you're managing a replica set or a standalone instance, mastering MySQL’s internal structure is essential for building high-performance applications.

2328 views

MariaDB vs MySQL: What's Different in 2026 and Which One Should You Use

Discover how MariaDB 11.x is redefining open-source databases with cutting-edge features like system-versioned tables, native AI-ready vector support, UUIDv7 for scalable inserts, and enterprise-grade security; all in the Community Edition, without the paywall.

1759 views

MySQL’s Connection Control Plugin for Brute-Force Protection: A DBA Security Guide

“Fortify Your MySQL Security: Slow Down Attackers with Connection Control Plugin” Learn how the MySQL Connection Control Plugin helps defend against brute-force login attempts by introducing intelligent, progressive delays—without locking out legitimate users.

13228 views

MySQL 8.4 Migration: Key Changes for IT Leaders

Explore the 8 critical changes in MySQL 8.4 you need to know before migrating your production environment. From authentication updates to InnoDB defaults, this release brings significant operational implications for developers and DBAs.

348 views

Load Balancing in MySQL: Read and Write Traffic Optimization Using MySQL Router

Optimize MySQL traffic with MySQL Router — smart load balancing made easyRouter Architecture image widget

2000 views

Check Constraints in MySQL

Master Database Integrity with Check Constraints: Improve Data Quality and System Performance

13221 views

Guarding Your Account: 5 Essential Tips to Evade Common Password Mistakes.

How to Secure your mysql database with the mysql validate password component

3584 views

Access Control & Account Management In MySQL 8

Discover how MySQL 8 enhances security through features like password expiry, password validation, account lock/unlock, privilege management, and dual passwords. Safeguard your valuable data and streamline access control in your MySQL environment.

3910 views

Optimize Your Database: Exploring MySQL 8.0 JSON Functions

This blog highlights the some of the important JSON function in MySQL 8.0 with examples. Level up your SQL skills with MySQL 8.0's JSON functions.

5183 views

Best practices on enabling Audit log on MySQL, MariaDB and ProxySQL

Importance of audit log in MySQL, MariaDB and ProxySQL. Best practices to enable audit log

5229 views

High Availability for MySQL using Orchestrator and ProxySQL

Auto slave promotion without any DBAs intervention using Orchestrator and ProxySQL. If you are looking for a HA solution without going for synchronous replication or Aws RDS then Orchestrator with ProxySQL is a great choice.

3535 views

Understanding the Spider Storage Engine

MariaDB Spider is a storage engine that supports federation and sharding across multiple MySQL or MariaDB servers. It helps distribute data and workloads, making it useful for scaling large datasets, balancing traffic, and improving data retrieval through parallel processing.

1783 views

Avoid deadlocks, certification failures on Galera Cluster using ProxySQL

Dealing with deadlocks, certification failures on Galera cluster by running single node writer using ProxySQL

7141 views

MySQL Character Set: UTF8MB4 vs UTF8 and How to Choose the Right Encoding

Choosing the right MySQL character set is critical for ensuring accurate data storage, multilingual support, and proper text comparison. While the legacy utf8 (utf8mb3) character set supports only up to 3-byte characters, it fails to handle modern Unicode requirements such as emojis and certain international symbols. In contrast, utf8mb4 provides full 4-byte Unicode support, making it the recommended standard for modern applications. This guide explains the differences between UTF8MB4 and UTF8, their impact on storage and performance, and provides step-by-step instructions to configure and migrate your MySQL databases safely.

5880 views

Real time data streaming from MariaDB to Columnstore

How real time data from transactional database(mariadb) to analytic database(columnstore) is achieved using MariaDB's maxscale binlog streaming

1516 views

Fixing the MariaDB Galera Bug: WSREP Lock Waits

A MariaDB Galera cluster can experience severe stalls when concurrent updates to the same primary key trigger an unexpected deadlock issue. This blog explains how we reproduced the issue in MariaDB 10.2, why the affected node became unresponsive, and the recovery and upgrade options used to resolve it.

4606 views