MySQL – Duplicate Same Table for Multiple Locations

database-designMySQLperformance

We have an "appointments" table with following schema.

appointments(app_id,.....,app_type,location_id)

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

leave this schema as it is (large number of rows and many reads across it), or
keep separate appointments tables for locations?

Best Answer

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.

Related Solutions

Table contains multiple names for same company

In this case I'd create a new table called CompanyAlias that will contain all possible aliases that you get for all companies. A possible structure would be:

CompanyAlias:

AliasId - int - Primary Key - some generated id
Alias - varchar(100) - the name of the alias
CompanyID - int - Foreign Key - references the original table of companies (in case you don't have one, I believe you should create one table with all unique companies and any other details that you might get)
any other column you'd like to have (description, creator..etc)

A query that will bring all the transactions for the main company of a given alias could look like:

declare @alias varchar(100);
set @alias = 'MicroS';

Select t.TransactionId, t.Alias, c.Company
from Transaction t
join CompanyAlias ca on ca.Alias = t.Alias
join Company c on c.CompanyId = ca.CompanyId
where t.Alias in
(
    Select ca.Alias
    from CompanyAlias ca
    where ca.CompanyId = 
    (
        Select ca.CompanyId
        from CompanyAlias ca
        where ca.Alias = @alias
    )
);

Where the tables would be:

Transaction - contains monetary transactions (should contain company alias used by client)
CompanyAlias - defined earlier
Company - defined by you to have unique companies and their main details

PS: in case the SELECT statement is too bulky (written in notepad), I want to mention that I wanted to:

get the CompanyId of the searched alias (the one that you specify in the search form)
get all the aliases of the mentioned CompanyId
get all the transactions of the mentioned aliases

Advice on choosing a really fast DBMS

If you can't scale a major RDBMS then your database design (includes indexing, queries and the like) or hardware is wrong. The choice of platform is almost irrelevant.

It is that simple. Especially when you mention "few hundred megabytes" which implies low volumes (I mean a few dozen writes per second)

Best Answer

Related Solutions

Table contains multiple names for same company

Advice on choosing a really fast DBMS

Related Question