1

We am building a SaaS backend for restaurants. We integrate directly with POS, so each POS keeps sending customer orders that we store in a following table in mysql.

CREATE TABLE name (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `amount` float DEFAULT NULL,
  `cc_last4` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `employee_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `menu_items` varchar(10240) COLLATE utf8_unicode_ci DEFAULT NULL,
  `payable` float DEFAULT NULL,
  `pos_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pos_version` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `barcode` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `receipt_datetime` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `subtotal_amount` float DEFAULT NULL,
  `token` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `transaction_no` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `business_id` int(11) NOT NULL,
  `employee_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `location_id` int(11) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `created_at_location_id_barcode` (created_at, location_id, barcode(25)),
KEY `index_receipts_on_barcode_and_location_id` (`barcode`,`location_id`),
KEY `index_receipts_on_barcode_and_business_id` (`barcode`,`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Few points of above schema:

  1. Except Primary Key(id), none of the fields are unique. so created a unique index on date, location & barcode to avoid duplicates incase the POS machine sends same data twice.
  2. menu_items column is supposed to contain individual items that customer ordered, which are normally below 255chars in 99.9% of the cases. but for remaining 0.1% cases(say its a catering order) the size of menu items increases & there is no limit on how large it can get. But we chose to truncate those to 10240chars. Not ideal, what's better alternative?
  3. Currently, this table has 30 million rows(6months of data) & it is expected to get doubled every 6months. Out of which we only use past 3-4months of data on active basis.
  4. No Partitioning or sharing has been done(yet)
  5. Data once inserted, is seldom updated. its only read. so our queries are mostly INSERT & SELECTS. No UPDATE statements are being used.
  6. Currently works but is slow.
  7. runs on linode with 8 GB RAM, 6 CPU Cores, 192GB SSD.

Goals

  1. Insertion Speed: currently inserting new records takes LOT of time. we need to be able to sustain insert speed of 20/records per second.
  2. Lookup: for newer data, past 3-4month. we query this quickly using location_id & barcode ordered by created_at DESC but for older data, its okay if it takes a while to run the query & return results.

Now, what can we do to that could improve. schema changes? dropping indexes? partitioning? better hardware? Looking for some advice.

  • 1
    You do realize that you have a seriously, seriously flawed schema here... right? Money should never be stored as FLOAT (nor as DOUBLE) and you should not be redundantly storing text for things like menu item or employee name. – Michael - sqlbot Nov 20 '14 at 13:08
  • 1
    +10 to what @Michael-sqlbot says above ^^^. Also, my guess is that most of these varchar(255) utf8 columns could be made smaller or latin (or replaced with ids to reference tables), starting from the barcode column. Having s 255x3 bytes column in multiple indexes doesn't look good at all. – ypercubeᵀᴹ Mar 6 '16 at 11:13

Your Answer

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

Browse other questions tagged or ask your own question.