We have an "appointments" table with following schema.


app_type - type of appointment
location_id - keeping the location for multiple location setup.

We are using MySQL and expect about 20,000-50,000 rows inserted per day around 365 days.

The expected number of operations = number of appointments * 5

For each operation in our app, we'll read this appointment table to get particular appointment details and update certain flags in the same table.

SPEED is our main concern.
My question is, performance wise is it OK to

  1. leave this schema as it is (large number of rows and many reads across it), or
  2. keep separate appointments tables for locations?
  • 1
    Is this schema normalized? (= is there no redundancy in location_id which would be solved by adding the new table?) If it is then you can just add proper indexes for your queries and it should be reasonably fast. Covering indexes might help. – jkavalik Sep 3 '15 at 8:12
  • Yes Normalized. what i planned was to have separate appointments tables for each locations without location_id column. According to our existing data, that will result in 60% 80% reduction of records for each table. my issue is is it worth the trouble of having separate tables with regard to performance? – eric Sep 3 '15 at 8:50
  • Separate tables with the same structure and location_id in name or something similar will make things too cumbersome. Tablename should not contain data. Is (or can be) location_id part of primary key? In InnoDB primary key is clustering, so you might get similar effect to separate tables (better locality) by putting this column first in primary key. – jkavalik Sep 3 '15 at 8:55
  • Unfortunately location_id is not a part of primary key. guess i'd take a chance and go with single table with covering index on location_id. – eric Sep 3 '15 at 9:42
  • When you know the expected numbers you can try generate enough random data to actually test it. – jkavalik Sep 3 '15 at 9:57

You are talking about less than 5 queries per second. A well maintained database can handle 100 qps or more.

A poorly handled db will melt down even with 5 qps.

It is rarely a good idea to split a table artificially.

It is usually a good idea to normalize repeated data. But don't normalize 'continuous' data, such as numbers, dates, floats, etc.

What will the SELECTs look like? And SHOW CREATE TABLE. Then we can discuss what indexes to have, and whether you have under/over-normalized, etc.

| improve this answer | |
  • most common select would look like select appointment_no, appointment_date, tempname, phone, .... where operator_ref = 124 and location_id=5 – eric Sep 17 '15 at 10:34
  • Have a composite INDEX(operator_ref, location_id) in either order. Regardless of the table size, this query will be fast. – Rick James Sep 17 '15 at 14:40

I'm assuming you'll use a appointment_date field. If so I would partition the table by [appointment_date and location_id] OR appointment_date only depending on what type of queries you'll be running most the time. Mysql supports many type of partitioning and sub-partition. 18 MR (millions rows) / years is not much (especially if columns are mostly date and int). I would also put in place an archiving mechanism to keep the table light (archive once a year).

| improve this answer | |
  • yes finally decided to go with one table structure. thank you. – eric Sep 17 '15 at 10:39
  • Partitioning won't speed up "point queries". Partitioning is handy for archiving (in 5.6, and more so in 5.7). – Rick James Sep 17 '15 at 14:42
  • I'm sorry but partitioning can dramatically improve your select queries. You just need to make sure you partition (and optionally sub partition) your table properly. I've dealt with a 150 GB data (and 90 GB index) without issues. – greenlitmysql Sep 17 '15 at 15:49
  • Make sure to use "explain partitions Select ..." When testing your queries. – greenlitmysql Sep 17 '15 at 15:51
  • greenlitmysql, thanks for the insight on partitioning, learned that from u. will definitely consider archiving as time goes – eric Sep 17 '15 at 17:19

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.