Friday, August 6, 2021

Finding Your Hidden InnoDB Primary Indexes

     InnoDB is the default storage engine for MySQL and InnoDB prefers that there is a PRIMARY KEY defined when a table is defined.  Having a unique non-nullable primary key can vastly speed up queries and data is stored by the primary key in a B+ Tree structure.  

    What if a primary key is not defined, InnoDB will use the first unique key defined as NOT NULL.  Failing that, InnoDB generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column that contains a 6-byte automatically increasing number when rows are inserted. This is a key that you can not use for searches (it is hidden from you!) and is not directly benefitting you. And that is probably not what you want.

    To find those columns you need to look in the INFORMATION_SCHEMA with a query like this:


    Run this on your instance and see if there are any surprises where you thought you had a primary key but in reality you do not.

| TABLE_ID | NAME                 |
|     1204 | json/t1              |
|     1206 | so/testfloat         |
|     1209 | so/tab1              |
|     1210 | so/q                 |
|     1211 | so/j                 |
|     1238 | sunshine/gentest     |
|     1368 | testx/austin         |
|     1374 | gis/geometries       |
|     1376 | gis/xy               |
|     1377 | gis/geom             |
|     1386 | gis/worldmap         |
|     1381 | gis/geometry_columns |
|     1407 | gis/mypoint          |
|     1409 | gis/zipcode          |
|     1410 | zip/zipcode          |
15 rows in set (0.0060 sec)

    Can you leave them like this, with the hidden index? Yup, sure you can. If the columns with the name GEN_CLUST_INDEX are there then the server has been making some use of them.

    Or you can run ALTER TABLE and designate an existing column as a primary key or to add a new column for that purpose.

Or add a invisible column to be the new primary key if you have any doubts about not bothering existing queries that would be bothered by the sudden appearance of a new column in the data.