From: Marcus Bointon Date: October 27 2011 3:17pm Subject: Re: Master-Master -> duplicate entry List-Archive: http://lists.mysql.com/replication/2254 Message-Id: MIME-Version: 1.0 (Apple Message framework v1251.1) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable On 27 Oct 2011, at 16:06, Ricardo Freitas wrote: > I agree when you say it is a waste of time but besides using an uuid, = is > there any other way you can use an autoinc value to a table where a = lot of > rows will be added on a daily basis? The problem that UUIDs solves is where you're inserting into multiple = databases that are not connected, yet you can still be sure that you = won't get a clash. You can do something similar with hashes (a UUID is = really a kind of carefully constructed hash), and the best way to store = those is in BINARY type fields as you can convert to and from them via = hex; storing things like MD5 hashes in char(32) fields is 50% wasteful = as hex only stores 4 bits per character. Autoincs generally work fine, and I wouldn't try too hard to find an = alternative. MySQL's implementation is less thorough than in other DB = systems - take a look at postgres for a more traditional system - but = it's perfectly adequate for most applications. > Furthermore, having this as an Int, you have till the 2147483647 id = until > the limit is reached, so you can have 2147483647 rows (regarding that = each > one has an increment of the pk of 1 regarding the previous inserted = row). >=20 > This in space requires x4 bytes (from the manual). Yes, 4 bytes =3D=3D 32 bits. > What I mean is - I'm able to store rows until the pk (autoinc) reaches > 2147483647, right? So, changing the increment value I will have this / = 10 -> > 214748364 available rows to use. >=20 > Am I reading this correctly? Yes. > Furthermore, what is your policy regarding "cleaning out" the = database? I > mean lets say you have a bunch of records that you no longer need (you = would > store them in a file in case you need them later).=20 >=20 > Is deleting this files and reassigning the autoincrement value a good > policy? (delete from record 1 to record 1.000.000 and reassign the > autoincrement to 1) It's entirely possible to do that, but doing it with MyISAM tables is = prone to orphaning your data unless you're very careful about = renumbering all your foreign keys in other tables. It's much easier if = you have foreign key constraints as in InnoDB. Overall I wouldn't worry = about it unless you are about to run out of numbers, and even then it's = easier to just switch to a bigger int type and all your problems will = solved for a long time (and if you have > 200 million records, you = probably have a client to pay for it too!)=20 Marcus --=20 Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK info@hand CRM solutions marcus@stripped | http://www.synchromedia.co.uk/