0

I'm quite new to MySQL and I'm trying to understand best practices. If I'm going to create a table that contains transactions history say:

  1. ID
  2. Transaction Type
  3. Item
  4. Date

Should I add indexing to the date column if I'm going to frequently INSERT? The data will be used to provide users of their history by any of the other attributes.

Is there is a disadvantage to indexing and should its implementation be dependant on the size of the table?

AlexH
  • 221
  • 2
  • 10
  • What will be your primary key? Do you intend to lookup transaction using a date range? – Stavr00 Jun 22 '15 at 14:07
  • You add indexes to table for two reasons. To increase performance (primarily of `select`, but sometimes for `update` and `delete`) and to enforce uniqueness of values. The question is: How are you going to use the data? Not, how are you inserting it. – Gordon Linoff Jun 22 '15 at 14:13
  • The primary key will always be ID. – AlexH Jun 22 '15 at 14:16
  • @AlexH not always. I've encountered this dogma quite frequently in academia where the professors have little if any real world experience. There are many advantages to using a surrogate key, but there are no rules saying you must always use one. If your natural key values are relatively small and will never change, then there is no reason you can't make your PK more natural. See http://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables. – Patrick Tucci Jun 22 '15 at 14:27

0 Answers0