{"id":1383,"date":"2009-02-02T21:08:07","date_gmt":"2009-02-02T11:08:07","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=1383"},"modified":"2009-02-21T13:40:51","modified_gmt":"2009-02-21T03:40:51","slug":"row-id-in-mysql-and-drizzle-and-the-engines","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2009\/02\/02\/row-id-in-mysql-and-drizzle-and-the-engines\/","title":{"rendered":"row id in MySQL and Drizzle (and the engines)"},"content":{"rendered":"

Some database engines have a fundamental concept of a row id. The row id is everything you need to know to locate a row. Common uses include secondary indexes (key is what’s indexed, value is rowid which you then use to lookup the row).<\/p>\n

One design is the InnoDB method of having secondary indexes have the value in the index be the primary key of the row. Another is to store the rowid instead. Usually (or often… or sometimes…) rowid is much smaller than the pkey of the row. This is how innodb can answer some queries just out of the index. If it used rowid, it may involve more IO to answer the query. All this is irrelevant if you never want just the primary key from a secondary index.<\/p>\n

Some engines are designed from the start to have rowid, others it’s added later (e.g. NDB).<\/p>\n

Anyway… all beside the point. Did you know you can do this in mysql or drizzle:<\/p>\n

drizzle> create table t1 (a int primary key);\r\nQuery OK, 0 rows affected (0.02 sec)\r\n\r\ndrizzle> insert into t1 (a) values (1);\r\nQuery OK, 1 row affected (0.01 sec)\r\n\r\ndrizzle> select _rowid from t1;\r\n+--------+\r\n| _rowid |\r\n+--------+\r\n|      1 |\r\n+--------+\r\n1 row in set (0.00 sec)<\/pre>\n
Is that the rowid from the engine? No (although at least NDB will let you select the real ROWID through a pseudo column through NDBAPI). Quoting from the MySQL manual<\/a>:<\/p>\n
If a PRIMARY KEY<\/code> or           UNIQUE<\/code> index consists of only one column           that has an integer type, you can also refer to the column as           _rowid<\/code> in           SELECT<\/code><\/a> statements.<\/p><\/blockquote>\n
Unfortunately, this isn’t correct… as this lovely bit of “oh my, what an excellent way to obfuscate my database app!” shows:<\/p>\n
drizzle> create table t1 (a int primary key, b varchar(100));
\nQuery OK, 0 rows affected (0.02 sec)<\/p>\n
drizzle> insert into t1 values (1,”foo”);
\nQuery OK, 1 row affected (0.00 sec)<\/p>\n
drizzle> update t1 set b=”foobar!” where _rowid=1;
\nQuery OK, 1 row affected (0.00 sec)
\nRows matched: 1  Changed: 1  Warnings: 0<\/p>\n
drizzle> select * from t1;
\n+—+———+
\n| a | b       |
\n+—+———+
\n| 1 | foobar! |
\n+—+———+
\n1 row in set (0.00 sec)<\/p>\n
So how is this implemented? In two places: in sql_base.cc find_field_in_table() and in table.cc during FRM parsing (this is how I found it). We can even do things Oracle can’t<\/a> (insert, update and delete):<\/p>\n
drizzle> update t1 set a=2 where _rowid=1;\r\nQuery OK, 1 row affected (0.00 sec)\r\nRows matched: 1  Changed: 1  Warnings: 0\r\n\r\ndrizzle> select * from t1;\r\n+---+---------+\r\n| a | b       |\r\n+---+---------+\r\n| 2 | foobar! |\r\n+---+---------+\r\n1 row in set (0.00 sec)\r\n\r\ndrizzle> update t1 set _rowid=3 where _rowid=2;\r\nQuery OK, 1 row affected (0.01 sec)\r\nRows matched: 1  Changed: 1  Warnings: 0\r\n\r\ndrizzle> select * from t1;\r\n+---+---------+\r\n| a | b       |\r\n+---+---------+\r\n| 3 | foobar! |\r\n+---+---------+\r\n1 row in set (0.00 sec)<\/pre>\n
SQLite also has something similar (see the autoinc docs<\/a>).<\/p>\n
I do wonder if anybody uses this functionality. It’s even tested (I was quite shocked at this) in the auto_increment and heap_auto_increment tests.<\/p>\n","protected":false},"excerpt":{"rendered":"
Some database engines have a fundamental concept of a row id. The row id is everything you need to know to locate a row. Common uses include secondary indexes (key is what’s indexed, value is rowid which you then use … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[75,14],"tags":[70,628,54,92],"class_list":["post-1383","post","type-post","status-publish","format-standard","hentry","category-drizzle-work-et-al","category-mysql","tag-drizzle","tag-mysql","tag-ndb","tag-rowid"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-mj","jetpack-related-posts":[{"id":3304,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/05\/13\/the-mysql-cluster-storage-engine\/","url_meta":{"origin":1383,"position":0},"title":"The MySQL Cluster storage engine","author":"Stewart Smith","date":"2013-05-13","format":false,"excerpt":"This is one close to my heart. I've recently written on other storage engines:\u00c2\u00a0Where are they now: MySQL Storage Engines,\u00c2\u00a0The MERGE storage engine: not dead, just resting\u00e2\u20ac\u00a6. or forgotten\u00c2\u00a0and The MEMORY storage engine. Today, it's the turn of MySQL Cluster. Like InnoDB, MySQL Cluster started outside of MySQL. Those of\u2026","rel":"","context":"In "code"","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1745,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/12\/09\/drizzle-frm-replacement-the-table-proto\/","url_meta":{"origin":1383,"position":1},"title":"Drizzle FRM replacement: the table proto","author":"Stewart Smith","date":"2009-12-09","format":false,"excerpt":"Drizzle originally inherited the FRM file from MySQL (which inherited it from UNIREG). The FRM file stores metadata about a table; what columns it has, what type those columns are, what indexes, any default values, comments etc are all stored in the FRM. In the days of MyISAM, this worked\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2152,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/10\/18\/new-apis-in-haildb\/","url_meta":{"origin":1383,"position":2},"title":"New APIs in HailDB","author":"Stewart Smith","date":"2010-10-18","format":false,"excerpt":"In the current HailDB we have a couple of new API calls that you may like: ib_status_get_all() Is very similar to ib_cfg_get_all(). This allows the library to add new status variables without applications having to know about them - because we return a list of what there are. For Drizzle,\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1636,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/05\/15\/size-of-storage-engines\/","url_meta":{"origin":1383,"position":3},"title":"Size of Storage Engines","author":"Stewart Smith","date":"2009-05-15","format":false,"excerpt":"For whatever reason, let's look at \"Total Physical Source Lines of Code\" from a recent mysql-6.0 tree (and PBXT from PBXT source repo): See the spreadsheet here. Raw data: Blackhole\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 336 CSV\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 1143 Archive\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 2960 MyISAM\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 34019 PBXT\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 41732 Maria\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 69019 InnoDB\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 82557 Falcon\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 91158 NDB\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 365272 NDB has a\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"http:\/\/spreadsheets.google.com\/pub?key=r_gtZ-mKTsbSlp6anfnACXw&oid=1&output=image","width":350,"height":200},"classes":[]},{"id":2229,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/12\/13\/persistent-index-statistics-for-innodb\/","url_meta":{"origin":1383,"position":4},"title":"Persistent index statistics for InnoDB","author":"Stewart Smith","date":"2010-12-13","format":false,"excerpt":"In browsing the BZR tree for lp:mysql-server, I noticed some rather exciting code had been merged into the Innobase code. You may be aware that InnoDB will do some index dives when opening a table to get some statistics about the indexes that can help the optimiser make good query\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1876,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/04\/02\/the-drizzle-and-mysql-key-tuple-format\/","url_meta":{"origin":1383,"position":5},"title":"The Drizzle (and MySQL) Key tuple format","author":"Stewart Smith","date":"2010-04-02","format":false,"excerpt":"Here's something that's not really documented anywhere (unless you count ha_innodb.cc as a source of server documentation). You may have some idea about the MySQL\/Drizzle row buffer format. This is passed around the storage engine interface: in for write_row and update_row and out for the various scan and index read\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1383","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/comments?post=1383"}],"version-history":[{"count":4,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1383\/revisions"}],"predecessor-version":[{"id":1484,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1383\/revisions\/1484"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=1383"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=1383"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=1383"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}