Re: BINARY(N) as primary key?
I'm curious to know why simply having a UNIQUE constraint on the column is inadequate... -JF On Nov 7, 2006, at 6:47 PM, Michael Fischer wrote: Any thoughts on using BINARY(N) or CHAR(N) as a primary key? Performance issues? In mysql, in general? Yes, in the context of the application, there is a very good reason for doing this, and not using an auto increment integer. Michael -- Michael Fischer Happiness is a config option. [EMAIL PROTECTED]Recompile and be happy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BINARY(N) as primary key?
On Wed, Nov 08, Jon Frisby wrote: On Nov 7, 2006, at 6:47 PM, Michael Fischer wrote: Any thoughts on using BINARY(N) or CHAR(N) as a primary key? Performance issues? In mysql, in general? Yes, in the context of the application, there is a very good reason for doing this, and not using an auto increment integer. I'm curious to know why simply having a UNIQUE constraint on the column is inadequate... -JF I'm concerned with performance, not integrity constraints. I was wondering, with respect to mysql internals, whether there was any substantial penalty to using CHAR or BINARY vs. INTEGER based primary keys. I imagine, though I have not probed the source code, that comparisons are done with bitwise-ands, or memcmp(3). For this of course, VARCHAR and VARBINARY fields would be a pain, because, from row to row, you couldn't accurately optimize for the width of the data in the key field. However, it might be perfectly reasonable to do so with fixed length CHAR or BINARY ones. Thanks. Michael -- Michael Fischer Happiness is a config option. [EMAIL PROTECTED]Recompile and be happy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BINARY(N) as primary key?
It's my understanding that a PK in MySQL is basically the same as a unique index -- for MyISAM tables at least. For InnoDB it's a bit different with InnoDB storing rows within the PK index (and inserting a hidden PK if none is provided). In short: I don't think you'll see any better performance by making your PK be this BINARY column, instead of just using an auto_increment PK column and having a UNIQUE constraint on your BINARY column. I doubt you'll see significantly worse performance by doing it either. -JF On Nov 8, 2006, at 6:02 PM, Michael Fischer wrote: On Wed, Nov 08, Jon Frisby wrote: On Nov 7, 2006, at 6:47 PM, Michael Fischer wrote: Any thoughts on using BINARY(N) or CHAR(N) as a primary key? Performance issues? In mysql, in general? Yes, in the context of the application, there is a very good reason for doing this, and not using an auto increment integer. I'm curious to know why simply having a UNIQUE constraint on the column is inadequate... -JF I'm concerned with performance, not integrity constraints. I was wondering, with respect to mysql internals, whether there was any substantial penalty to using CHAR or BINARY vs. INTEGER based primary keys. I imagine, though I have not probed the source code, that comparisons are done with bitwise-ands, or memcmp(3). For this of course, VARCHAR and VARBINARY fields would be a pain, because, from row to row, you couldn't accurately optimize for the width of the data in the key field. However, it might be perfectly reasonable to do so with fixed length CHAR or BINARY ones. Thanks. Michael -- Michael Fischer Happiness is a config option. [EMAIL PROTECTED]Recompile and be happy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
