Wikipedia:Reference desk/Archives/Computing/2014 August 15

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is an archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

August 15

Primary key

I have to design a series of tables for a database. One of the tables will look like this: (those are the fields): [1] numb_id int (11) AUTO_INCREMENT; [2] attribute-1 double; [3] attribute-2 double [3] attribute-3 double.

I make the first field a primary key.

My question is: will I gain in performance speed with the primary key vs. no primary key? My understanding is that there will be no gain in performance. Primary key is an index field, it means that DBMS will create a separate (index) table that will have this key in the first column but it is already in the first column. That column will be ordered but it is already ordered. It will then probably create pointers to each row in the first table to the row where the primary key is but it's not really needed. Once you get the row in the original table you can read this information directly. Is it all correct?

What could be the situation, what kind of structure the table should have to benefit from introduction of primary key in terms of improved performance. What could be the gain?

Thanks, - --AboutFace 22 (talk) 01:43, 15 August 2014 (UTC)

You don't mention the database system you are using, but the syntax looks like mysql. I don't know mysql specifically, but in general you get the first index for free - combined into the main table. This is called the clustered index. Typically the primary key will also be the clustered index unless you explicitly specify something different in your table definition. A database index is some variation of a tree data structure that allows for an efficient look-up of a specific key value or range of values. For secondary (non-clustered) indexes, the leaf node will be a reference to the record number of primary key value into the main table. After database system looks up the index record, a second look-up into the main table is performed to get the actual record. For a clustered index, the leaf node of the index is the record itself, so there is no extra work to retrieve the target record contents. How much performance gain that gives you depends on how often to access a record (or join to that table) using that primary key. -- Tom N talk/contrib 04:08, 15 August 2014 (UTC)

Thank you, Tom. I actually do not know the DBMS that is going to be used. I do not design the whole thing, only a conceptual framework so to speak. I do have some (fairly extensive actually but years ago) background in SQL server and dBase IV now defunct but this is not applicable here. Another person does the implementation. He mentioned a few options, MySql is one of them. The actual choice is still up in the air and will be apparently discussed at a later date but this is not something I can influence because I am not familiar with them.

This is also my impression that in order to be effective the primary key must be a clustered index, otherwise any advantage is not there. It sounds like you are saying that the primary key is always a clustered index even if not designed as such. Thanks, --AboutFace 22 (talk) 13:40, 15 August 2014 (UTC)

I believe each table normally has a hidden column, named something like ROW_ID, that sounds like it would be identical to your NUMB_ID column. So, you could possibly skip your column entirely. However, if you would ever want to change the numbering to be anything other than the order in which the rows are created, then you need to keep it in. Also, if you intend to display the column to the user, it might be easier to keep it in, than try to use the ROW_ID for that. StuRat (talk) 17:19, 15 August 2014 (UTC)

Thank you, StuRat, you are always helpful. Still one of my questions is unanswered: how much will I gain in speed of storage and retrieval with introduction of primary keys. --AboutFace 22 (talk) 17:59, 15 August 2014 (UTC)

It sounds like there would be no savings in speed by introduction of the primary key, if it's just redundant with the ROW_ID index which you get by default. It might actually slow things down by a slight amount, as there's some overhead in using it. However, as each DB management system is a bit different, I can't guarantee it. I'd do some benchmark testing to confirm it on your specific DB.

BTW, I don't think adding a primary key ever makes row creation faster, as there's now more info to store. It's designed to make retrieval faster. Deletes and updates may also be faster, as the first step there is retrieval of the desired record. And, of course, if you do a retrieval using some fields other than the primary key, it won't help the speed there, either. StuRat (talk) 22:28, 15 August 2014 (UTC)

Chili's credit card security

This restaurant chain has placed card readers on each table so you can pay by credit card there. However, they are wireless. What type of security precautions do they take so the credit card info can't be compromised ? (This is a practical Q, as I eat there myself, and want to know if it is safe to pay that way.) StuRat (talk) 22:21, 15 August 2014 (UTC)

Apparently it's a Ziosk. This on Chilis' website says it "meets the comprehensive PCI security standards". The Ziosk website doesn't say much at all about security considerations, except for mentions of "Secure payments" and "encrypted credit card reader". Rojomoke (talk) 07:28, 16 August 2014 (UTC)

Thanks. Of course everyone claims to have a secure system, but all the recent thefts of credit card info shows they were wrong. So, if they won't say how it is encrypted and secured, I guess I have my answer: don't trust it. I'll continue to pay with cash. StuRat (talk) 13:37, 16 August 2014 (UTC)

StuRat, your post last night made me thinking. What is the guarantee that cash registers at Sears, Macy's or other large department stores don't have wireless connections with their credit card readers. What would be a wired connection? A phone line which is perhaps much more expensive to operate in a long run. You also need lots of them to service a large store. The answer ultimately would be those credit cards with chips inside they are talking about now. Thanks for your note at my primary key post. --AboutFace 22 (talk) 14:52, 16 August 2014 (UTC)

I think the recent thefts show a more important point. Whether the connection is wired or wireless, how does it help anyone if the POS systems are hopelessly insecure? Also experience in Europe where chipped cards are the norm shows they help, but are no magic bullet. In other words, you can be pretty sure there will be breaches,a nd it may not be in the areas you think.

However I'm not recommending mass panic. People seem to worry about this sort of stuff too much when they are end users. Sure you should take reasonably protections like making sure you hide your pin entry, a cursory check for any skimming devices and not handing your credit card over to a dodgy looking person who disappears with it for minutes, but there's no need to analyse the security of every machine except for personal interest reasons. (And speaking of dodgy people, I wonder how many people willingly hand over their unchipped card to someone who disappears with it and yet are worried about the security of machines.)

Obviously it's annoying to have to replace your credit card or make reports and wait for your bank to cancel transactions you didn't make but if you're paying attention to your statements (which you should be), you should primary consider such credit card misuse fraud a concern of the banks and stores involved, not yours, unless you're in some weird jurisdiction or with some weird bank with totally crap credit card liability policies. (Identity theft is of course another issue.)

Nil Einne (talk) 15:58, 16 August 2014 (UTC)

Note that recent major thefts of credit card info have been either from the back office systems or from malware infected POS terminals (like that suspected in the Target#2013 Security Breach). The payment details were not snatched from the air while being transmitted from a card reader to the store's systems. Your card information is probably just as safe as if you were to go to the desk and pay there with your card. The added security provided by Chili or any other location with portable POS devices, is that you don't hand over your card to someone who disappears off into a back room and skims your card. In any case, the Payment Card Industry Data Security Standard mandates that information such as the PIN remain encrypted at all times. Unfortunately, not all information is required to be encrypted and that is how the thieves are able to get access to customer names, card numbers, expiration dates, etc, but not the PIN numbers. The thieves are then able to shop online, so long as they can avoid things like 3-D Secure, but can't produce a duplicate card and go shopping at regular stores, or retrieve cash from ATMs. Astronaut (talk) 15:58, 16 August 2014 (UTC)

Yes, they do take the credit card away for several minutes. I wish they would just bring the card reader right to the table. So maybe the Ziosk is the safest way to use a CC, although still not as safe as cash. StuRat (talk) 03:18, 17 August 2014 (UTC)

Of course carrying around lots of cash all the time makes the risk of being mugged or pick pocketed higher :P I think the physical risk outweighs the virtual one by a long shot. I work in IT for a financial institution and I think people worry FAR too much about how secure their credit card is. I think Nil Ennie's reply is very reasonable. Banks guarantee credit cards against fraud, credit cards now are safer than ever before. Do you remember the days of swiping your card in those carbon imprint machines? You think "that" was secure? Vespine (talk) 23:02, 17 August 2014 (UTC)

I carry my wallet in my front pocket, not back, deep down in the pocket, so it would be difficult to steal. I also have a secret compartment for the big bills (be careful how you read that), so anyone eyeballing me as I use it to pay a restaurant bill will only see small bills. StuRat (talk) 16:46, 19 August 2014 (UTC)