Re: [NF] Getting MySQL integer primary keys (NOT AUTOINC) idea...similar to VFP's old IF SEEK("TheTable","Keys","TheTable") AND RLOCK('Keys') idea
On Fri, Apr 15, 2016 at 2:57 PM, Gene Wirchenko genew@telus.net wrote:
Of course, if you were to create GUIDs, the universe would likely end first.
GUIDs are not that bad, are they?
Oh, indeed they are! The Entropy Heat Death of the Universe is not far behind the exhaustion of the GUID space. Perhaps they should be considered a Fifth Horseman of the Apocalypse.
On 2016-04-15 15:28, Ted Roche wrote:
On Fri, Apr 15, 2016 at 2:57 PM, Gene Wirchenko genew@telus.net wrote:
Of course, if you were to create GUIDs, the universe would likely end first.
GUIDs are not that bad, are they?Oh, indeed they are! The Entropy Heat Death of the Universe is not far behind the exhaustion of the GUID space. Perhaps they should be considered a Fifth Horseman of the Apocalypse.
Just to reiterate, I was only thinking about going back to INTs for the size and efficiency. For large tables, I'm told by the corporate gig boss that GUID keys don't do as well and I was just thinking more could be held in memory with integer keys.
At least I'm glad I chose the 16-byte GUID instead of the alternatives:
FUNCTION CreateKey(tiLevel as Integer) as String * Returns newly created GUID using class from VFP2C32. LOCAL lcKey as String * 0 = ansi human readable (38 wide) * 1 = unicode (76 wide) * 2 = binary (16 wide) IF VARTYPE(tiLevel) <> "N" THEN tiLevel = this.iDefaultKeyLevel ENDIF IF NOT ("vfp2c32.fll" $ SET("Library")) THEN && mjb 04-16-14 minor tweak. Happy birthday, Bob! :-) SET LIBRARY TO vfp2c32.fll && mjb 05-17-14 took out LOCFILE ENDIF lcKey = CreateGUID(tiLevel) IF NOT this.ValidKey(lcKey) THEN && try again..don't like those as it caused issue with cboJobType this.TrackBadKey(lcKey) lcKey = this.CreateKey(tiLevel) ENDIF RETURN lcKey ENDFUNC && CreateKey(tiLevel as Integer) as String
Why not use mysql to do it? SELECT UUID();
Guids are tough on inserts because they don't go to the bottom in the btree index so they have to force their way in and possibly create new pages all over the place.
If you are doing inserts at high speed say the last seconds of an auction on eBay with hundreds of bids a second you might experience a stress point. For these types of systems I don't think you are even close to that level.
I have a gl table transaction with 87 mill rows and we use guid for account, and the various "dimensions" to code the transaction. This is in our DW. I can pull specific account rows to a year period in under 5 seconds only looking for a specific dimension value on the first request of this table. Do it 2,3,4 times in a row and sub second because all the data is pulled from disk to ram.
That answers the report question How much did we spend on machinery at a specific plant for a specific project.
Guids rock for being a pointer. Disk space is so cheap today that you might consider going SSD drives in your next san. We still use rotary disks and have a total of 75 TB in use. Most of that is email and backups as well as real files.
On Fri, Apr 15, 2016 at 10:06 PM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 2016-04-15 15:28, Ted Roche wrote:
On Fri, Apr 15, 2016 at 2:57 PM, Gene Wirchenko genew@telus.net wrote:
Of course, if you were to create GUIDs, the universe would likely end
first.
GUIDs are not that bad, are they?Oh, indeed they are! The Entropy Heat Death of the Universe is not far behind the exhaustion of the GUID space. Perhaps they should be considered a Fifth Horseman of the Apocalypse.
Just to reiterate, I was only thinking about going back to INTs for the size and efficiency. For large tables, I'm told by the corporate gig boss that GUID keys don't do as well and I was just thinking more could be held in memory with integer keys.
At least I'm glad I chose the 16-byte GUID instead of the alternatives:
FUNCTION CreateKey(tiLevel as Integer) as String * Returns newly created GUID using class from VFP2C32. LOCAL lcKey as String * 0 = ansi human readable (38 wide) * 1 = unicode (76 wide) * 2 = binary (16 wide) IF VARTYPE(tiLevel) <> "N" THEN tiLevel = this.iDefaultKeyLevel ENDIF IF NOT ("vfp2c32.fll" $ SET("Library")) THEN && mjb04-16-14 minor tweak. Happy birthday, Bob! :-) SET LIBRARY TO vfp2c32.fll && mjb 05-17-14 took out LOCFILE ENDIF lcKey = CreateGUID(tiLevel) IF NOT this.ValidKey(lcKey) THEN && try again..don't like those as it caused issue with cboJobType this.TrackBadKey(lcKey) lcKey = this.CreateKey(tiLevel) ENDIF RETURN lcKey ENDFUNC && CreateKey(tiLevel as Integer) as String
[excessive quoting removed by server]
-
mbsoftwaresolutions＠mbsoftwaresolutions.com -
Stephen Russell -
Ted Roche