Long db table vs. many shorter ones

Anon

Hello Folks,

I'm looking for the group's collective wisdom on the following issue, I'm sure many have been confronted with it.

I'm designing a db for a website that will utilize SQL Server 7. I have few tables that I think will grow very large row wise and that will be written to an read from frequently. I sense that I might be able to get better perfomance out of the system if I split many of these large tables up (row wise) into many smaller tables.

For example, The website that I'm working on has a "MailingAddresses" db table that contains the mailing addresses for the sites users (subscribers and other misc users - 1 record per user, anticipating 70,000+ users). Each user can update their record, and there will be frequent queries to the tables to get addresses for both internal admin use and for display on public webpages.

The website has five distinct sections that are in some sense like five distinct websites. Each user belongs to only one of these sections and they won't migrate between sections. . Therefore I'm considering breaking up the one large "MailingAddresses" tables into five smaller tables, one for each section, i.e., "MAddressesSectionA", ..., "MAddressesSectionE".

These tables will have the same fields and constraints. Also of course there would be the same number of reads and writes in total with the five smaller tables as compared to that for the one big table. Also the combined size of the info in the five smaller tables would be the same as that for the one large table.

Though it's going to more of a pain to manage five tables versus one, I have a hunch it might be easier for the dbms to handle reads and writes with five smaller tables than with one large table...

...Of course this is true when queries are mainly respective to users from just one or two of the sections (as might be the case) - the big table then has the overhead of the address records for users in the other sections. BUT is there any benefit with the five smaller tables route when they are all frequently accessed??? Sure each select query has fewer records to go through, but with all five tables in play the dbms has to deal on average with the same amount of info as in the one big table.

What do you folks think, to divide or not to divide the big table(s) up row wise into smaller tables?

I guess the issue is summed up in this question: In general, can a dbms better handle in memory, and more quickly write to and query access - one BIG table with a+b+...+n records, or N smaller tables with a, b,...,n records respectively?

Thanks for the collective wisdom, - Jerry

Anon

obviously N smaller tables if they are all getting updated a lot. (due to table locking)

toma42

well, the issue you have is NOT a database design issue...it's a DBA issue.

I don't know MSSQL, but if it tries to compete with Oracle, it should have this funcitonality (which oracle does):

divide tables into smaller tables that are searched on queries based on a set field/rule.

An example of this would be: given the first character of a text column, insert the row into the table partition that has other rows with the same letter. When a query is done where mycol = 'axxs'; the first thing Oracle does is decide which table partition to use and then does the rest of the where statement.

You should be able to design a database application without ever caring how many rows will be in each table...as I said, dealing with that is a DBA issue.

cjones

If you break it into smaller sections you can also have the option of expanding it over several servers later without much change.

I don't use MSSQL a lot, but I do this in mySQL.

[deleted]

I doubt that MS-SQL will use table-level locking. It's not MySQL....

[deleted]

Ehm.. that's what replication and load-balancing is for.

[deleted]

Database fragmentation. Very cool stuff. Not sure if MS-SQL supports it. Informix does.. very cool.

[deleted]

Save yourself a lot of work and worries, and stick with one big table.

Anon

I have a question is:

how to search many small table in MySql?
this small table have same construction.

in a big table

can search it use

select f1 from bigtable where f1="abc"

when use many small table
example
table1
table2
...
table999

select f1 from table* where f1="abc"

it's not work. 🙁

help!

vincent wrote:

Save yourself a lot of work and worries, and stick with one big table.

[deleted]

This is where the proverbial doody hits the fan in MySQL.

What you have are two or more tables that contain the same type of data,
but that are in no way related to eachother.
None of the records in the first table have any relation to the records in the other tables (through Primary key of foreign keys), so you can't simply JOIN the tables.
A JOIN without a relation would give a cartesian product (nnn*... rows, if you have 300 rows in the first table, and 300 in the second, the result would be 90k rows where you really only want 600. This number grows very quickly and it can make your database do 'the lemming thing')

In MySQL the only solution I see in (and I didn't bother to spend much time finding one) is to query each table seperately.
The thing is, using many smaller tables means that you have to have some 'intelligent' routine in your application that works out where a record may be found, and where new records are supposed to be stored.
eg: records for users with userid 1-100 into table 1, usersids 101-200 into table 2 etc. That could be used in reverse to query data for userid 203 in table 3 only.

But still, if you want info about all your users, you'll have to query all tables seperately and process the output.

In 'real' RDBMS's there's the UNION operator, which lets you run several queries at the same time, and present the output as one big resultset (as opposed to MySQL's many small resultsets). Not much of an improvement speedwise, but easier to work with.

In short: don't split up your tables just to make the tables smaller.

Anon

i thing they many columns smaller in width, not number of records...

table_1
id name address

table_2
id phone fax mobile

table_3
id parents

or something like that...

story people with id 1-100 in a DIFFERENT table than 101-200 just seems silly to me. if the DB wants to store it on the disk like that for optimization, GO AHEAD, but i shouldn't be forced to make my backend completely sloppy because the dba didn't optimize the table structure

Anon

Now I use text file to do it:
table1.txt
table2.txt
...
table1000.txt

use grep to search it.
It's can search any string in table1 ...table1000

But i find when have more user,

It's slow.

So ,I want move to MySql to do this.

But I cann't to find a good way.

[deleted]

Making several tables with fewer columns will only slow you down because you cannot never ever get a complete record without a JOIN.

Splitting a single table up into several tables with fewer rows can give a huge speed improvement due to MySQL's horrible table-level locking.
One insert locks the entire table. If your insert takes 2 seconds, then nobody else can do anything during those 2 seconds.
In that case it makes sense to make several smaller tables, because then your update will only lock one table for 2 seconds. If you have 10 tables, you are only blocking 10% of your users at that time.

But if you are not doing any heavy inserts or updates, then use a single table.
It's much easier to setup/maintain and if you use proper indexing, it's probably just as fast as a small table.