is it considerably faster to query different tables than having a where clause

imagine that we have this table:

cr开发者_开发百科eate table Foo(
id int,
name varchar,
k int --can be 1 or 2 or 3
)

or we could have 3 tables for each value of k

create Fook1(
id int,
name varchar
)
...
create table Fook2
...
createa table Fook3

is it going to be considerably faster to do:

select * from Foo where k = 3

than doing:

select * from Fook3

Potentially, using multiple tables could be faster than using a single table (particularly if those tables are going to have many millions of records), but there would be trade-offs in terms of ease of use, manageability, etc.

However, you could have the benefits of both by partitioning your table.

-Do-Not-Do-That-

Oh, wait, that's not helpful, it's just beligerant :)

Partitioning the data in this way CAN yield performance benefits. But they also introduce other costs:
- Queries that need to span all three tables become more complex
- Your schema becomes more cluttered
- It's easier to make mistakes
- It's hard to ensure referential integrity
- You may need to include a view to unify the 3 tables

You are most likely much better off with an Index that has k within it. And depending on how you query the data, k may be the first field in that index. When you specify k = ?, it just needs to do a Very quick check in the index and then you're only looking at the relevant portion of the table. And, if the index is a clustered index, the data is even physically stored in that order.

I'd highly recommend making use of indexes in this way before partitioning your data. It's an optimisation with costs, and so should be approached when it can be shown Necessary, not as a safety net early in design.

It may depend on the DB, so a real example is needed. For instance, in Oracle you can use partitioning, which does exactly what you say here behind the curtains, or create a materialized view with the union and then have the option to do both. Normally, I'd say that you should create a correct implementation and then tune; early optimization is the root of all evils, especially with DBs. I think it is quite likely that your bottleneck is not going to be where you expect it.