Database speed optimization: few tables with many rows, or many tables with few rows?

Question

I have a big doubt.

Let's take as example a database for a whatever company's orders.

Let's say that this company make around 2000 orders per month, so, around 24K order per year, and they don't want to delete any orders, even if it's 5 years old (hey, this is an example, numbers don't mean anything).

In the meaning of have a good database query speed, its better have just one table, or will be faster having a table for every year?

My idea was to create a new table for the orders each year, calling such orders_2008, orders_2009, etc..

Can be a good idea to speed up db queries?

Usually the data that are used are those of the current year, so there are less lines the better is.. Obviously, this would give problems when I search in all the tables of the orders simultaneously, because should I will to run some complex UNION .. but this happens in the normal activities very rare.

I think is better to have an application that for 95% of the query is fast and the remaining somewhat slow, rather than an application that is always slow.

My actual database is on 130 tables, the new version of my application should have about 200-220 tables.. of which about 40% will be replicated annually.

Any suggestion?

EDIT: the RDBMS will be probably Postgresql, maybe (hope not) Mysql

Answer 1

Smaller tables are faster. Period.

If you have history that is rarely used, then getting the history into other tables will be faster.

This is what a data warehouse is about -- separate operational data from historical data.

You can run a periodic extract from operational and a load to historical. All the data is kept, it's just segregated.