Long db table vs. many shorter ones

[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...

like

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]

oh

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.