-2

I'm building an app aimed to store lots of records (millions) and trying to find a best way to store records identifiers that is publicly accessible from the web (record URL).

I'd like to get:

  1. Random, probably unique, unpredictable IDs that is reasonably short and possible to use in an URL, meaning UUID will not suit as it is too long.
  2. Best performance from database and reduce storage space.
  3. Avoid creating auto increment id with the only purpose to use it as a PRIMARY key.
  4. ...Avoid future replication issues?

My first thought was to generate a random string like 4r5psPxuRw and use it as a primary key (CHAR(10)). But in this case index will be 30 bytes per record and I assume that with the large amount of records INSERT performance will suffer.

Now I came to a random numeric string, for example 16 digits from 1 to 9, and store it as BIGINT(16).

Is this a good way to achieve security, speed and reduce number of surrogate keys? Am I missing something?

  • What do you mean by unpredictable? Is it (1) hard to guess next record for given user OR (2) hard to index all keys in your system by brute force enumeration? – Stoleg Dec 4 '15 at 17:04
  • 1
    Tables mostly used for SELECTs, it's a common web-app with profiles, albums, comments, etc. Unpredictable to avoid automatic parsing public profiles, pages, etc – Kanstantsin Dec 4 '15 at 17:05
  • The CHAR(10) can be just ASCII, no need for utf, so 10 bytes actually.. Whats your reason for not using auto-increment or other surrogate key? Using it internally and having unique handle with the random identifier will give you good primary key with appending and good unique access for single row lookups (or do you expect to fetch hundreds of rows per user request by the public handle?) – jkavalik Dec 4 '15 at 20:07
  • 1
    @jkavalik thank you for the ASCII hint, haven't thought about that. My reason was if I have random unique handles, that is public, and they are cheap to store, why not use them as records identifiers to use in joins or referencing, for example, instead of SELECT * FROM t WHERE comment_id IN (1,2,3) use SELECT * FROM t WHERE comment_uid IN (1425889898813533, 9892597754837953, 3721461664138416). Why do I need auto incrementing comment_id then? – Kanstantsin Dec 4 '15 at 20:41
1

Let me summarize your problem and answer it as I understand it.

You want prevent external party going through every page of you site and saving information about your users.

  1. This is exactly what search engines do. Therefore your users should have an option to set their info as public/friends only/private.
  2. To control what a user (whoever it is) does you can use a concept of Session. It allows you to monitor overall behavior and see automated users.
  3. When you have sessions, a logged in user cannot access someone else's info even if has right ID/URL.

What you need to do:

  1. Define "user" roles for your site, including automated users like search engines, APIs, advertising agents, etc.
  2. describe for yourself appropriate use patterns, what kind of info user need, how often, methods as authentication, authorization, access to various content.
  3. Find appropriate solutions to implement the above.
  • 1
    My question was about random stings or integers as primary keys and mysql performance related to that. Thanks. – Kanstantsin Dec 13 '15 at 11:31
1

You need to pass an ID from one web page to the 'next'? But HTTP is stateless? And you don't want to expose that ID in a URL?

Then use a cookie for passing it. That's what all the heavy hitters do. Think about how shopping sites work.

The ID (in the cookie) is probably a lookup key into a database table to find all the other info. And some of that info is used to validate that the ID is for the "current session", not yesterday's session. That is, it is more than "user_id".

UUID as a key (PRIMARY or otherwise) suck for performance. Avoid them if at all possible. That goes for your 'short' uuid.

"records identifiers that is publicly accessible" -- What do you mean? A URL is visible, period.

  • 1
    My question was about random stings or integers as primary keys and mysql performance related to that. – Kanstantsin Dec 13 '15 at 11:31
  • Random strings suck as keys. And they provide little, if any, security. Also, think about what will happen if the user bookmarks (for use next month) or shares his URL. – Rick James Dec 13 '15 at 19:27
  • @Kanstantsin is talking about using random strings as persistent identifiers for webpages. If you bookmark a page that is indexed on a random string, that random string will always retrieve the same page. – ndm13 Oct 5 '16 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.