many databases with 1 table or 1 database with many tables

[Greg]

Hello,

I've to manage many 'table' (having same scheme) on the same server.

And I ask myself what could be the best to do (and if you know, why) :

Creating as many database (the name would be a 8byte int value
(converted to a string)) as necessary, all with the same table struct
(and table name), or create 1 database and in it create also as many
table as necessary (so also a 8byte int value as name) ?

Has mysql some limitations on that (database/table) and what about the
performance issue ?

Thanks for your help.

Nb: I use innodb database/table.

[Jeff North]

On 4 May 2004 02:34:58 -0700, in mailing.databas e.mysql
ab*******@skybe l.net (Greg) wrote:

| Hello,
|
| I've to manage many 'table' (having same scheme) on the same server.
|
| And I ask myself what could be the best to do (and if you know, why) :
|
| Creating as many database (the name would be a 8byte int value
| (converted to a string)) as necessary, all with the same table struct
| (and table name), or create 1 database and in it create also as many
| table as necessary (so also a 8byte int value as name) ?
|
| Has mysql some limitations on that (database/table) and what about the
| performance issue ?
|
| Thanks for your help.
|
| Nb: I use innodb database/table.

Is there any particular reason why you want/need to have multiple
tables?
Why not just have a single table and add the field that would be the
table name.
This would make maintenance, searching, sorting etc very much
simplified.
Performance will depend upon how the table is set up and what fields
you have indexed.
How many records do you estimate will be in the table(s)?
Have you thought about normalising your data yet?
---------------------------------------------------------------
jn****@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

[Jeff North]

On 4 May 2004 02:34:58 -0700, in mailing.databas e.mysql
ab*******@skybe l.net (Greg) wrote:

| Hello,
|
| I've to manage many 'table' (having same scheme) on the same server.
|
| And I ask myself what could be the best to do (and if you know, why) :
|
| Creating as many database (the name would be a 8byte int value
| (converted to a string)) as necessary, all with the same table struct
| (and table name), or create 1 database and in it create also as many
| table as necessary (so also a 8byte int value as name) ?
|
| Has mysql some limitations on that (database/table) and what about the
| performance issue ?
|
| Thanks for your help.
|
| Nb: I use innodb database/table.

Is there any particular reason why you want/need to have multiple
tables?
Why not just have a single table and add the field that would be the
table name.
This would make maintenance, searching, sorting etc very much
simplified.
Performance will depend upon how the table is set up and what fields
you have indexed.
How many records do you estimate will be in the table(s)?
Have you thought about normalising your data yet?
---------------------------------------------------------------
jn****@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

[Greg]

Jeff North <jn****@yourpan tsbigpond.net.a u> wrote in message news:<i2******* *************** **********@4ax. com>...

On 4 May 2004 02:34:58 -0700, in mailing.databas e.mysql
ab*******@skybe l.net (Greg) wrote:

| Hello,
|
| I've to manage many 'table' (having same scheme) on the same server.
|
| And I ask myself what could be the best to do (and if you know, why) :
|
| Creating as many database (the name would be a 8byte int value
| (converted to a string)) as necessary, all with the same table struct
| (and table name), or create 1 database and in it create also as many
| table as necessary (so also a 8byte int value as name) ?
|
| Has mysql some limitations on that (database/table) and what about the
| performance issue ?
|
| Thanks for your help.
|
| Nb: I use innodb database/table.
Is there any particular reason why you want/need to have multiple
tables?

Yup, there is :-)
Why not just have a single table and add the field that would be the
table name.
Cause until now, I had indeed all in 1 big table..
This would make maintenance, searching, sorting etc very much
simplified.
Hmm not necessary I think, and more particularly if one wants the
system to be 24h/24h up, having some "separation " at an higher level
(than row) would be of great interrest. For example, when 1
table/database crashs, others users can continue working with others
tables/databases... when you want to delete 1 whole table/database
(separated from many others), that has far less impact on performance
than deleting somes rows within a bigbig table beeing continuesly
asked for 'query' (normal query (40%), update (35%), and some times
insert or delete (25% for the 2))... I think.
Performance will depend upon how the table is set up and what fields
you have indexed.
Well the rows are pretty simple:
(1 bigint, 1 int) as primary key,
1 int,
5 tinyint, and
4 bigint (representing encoded date value).
But there are 6 index also (5 on 2 fields, and 1 on 3 fields).

And more, the bigint beeing in the primary key is used on all index..
That bigint would become my database/table name in fact... so that
would reduce the primary key to an int, reduce the 5 index on 1 field
and the last index on 2 ... (I don't have to do "cross query" on
differents values of that field).
How many records do you estimate will be in the table(s)? As many as we can :-)
More seriously, there could have from 0 to .. N (N could be great
great, say 10000), and the number of records will vary in the time (by
insert/delete) !
More practically, most of those tables/databases would have a little
(say < 100 or 200) number of rows.
Have you thought about normalising your data yet?

Well, this is already done ;-)

[Greg]

Jeff North <jn****@yourpan tsbigpond.net.a u> wrote in message news:<i2******* *************** **********@4ax. com>...

On 4 May 2004 02:34:58 -0700, in mailing.databas e.mysql
ab*******@skybe l.net (Greg) wrote:

| Hello,
|
| I've to manage many 'table' (having same scheme) on the same server.
|
| And I ask myself what could be the best to do (and if you know, why) :
|
| Creating as many database (the name would be a 8byte int value
| (converted to a string)) as necessary, all with the same table struct
| (and table name), or create 1 database and in it create also as many
| table as necessary (so also a 8byte int value as name) ?
|
| Has mysql some limitations on that (database/table) and what about the
| performance issue ?
|
| Thanks for your help.
|
| Nb: I use innodb database/table.
Is there any particular reason why you want/need to have multiple
tables?

Yup, there is :-)
Why not just have a single table and add the field that would be the
table name.
Cause until now, I had indeed all in 1 big table..
This would make maintenance, searching, sorting etc very much
simplified.
Hmm not necessary I think, and more particularly if one wants the
system to be 24h/24h up, having some "separation " at an higher level
(than row) would be of great interrest. For example, when 1
table/database crashs, others users can continue working with others
tables/databases... when you want to delete 1 whole table/database
(separated from many others), that has far less impact on performance
than deleting somes rows within a bigbig table beeing continuesly
asked for 'query' (normal query (40%), update (35%), and some times
insert or delete (25% for the 2))... I think.
Performance will depend upon how the table is set up and what fields
you have indexed.
Well the rows are pretty simple:
(1 bigint, 1 int) as primary key,
1 int,
5 tinyint, and
4 bigint (representing encoded date value).
But there are 6 index also (5 on 2 fields, and 1 on 3 fields).

And more, the bigint beeing in the primary key is used on all index..
That bigint would become my database/table name in fact... so that
would reduce the primary key to an int, reduce the 5 index on 1 field
and the last index on 2 ... (I don't have to do "cross query" on
differents values of that field).
How many records do you estimate will be in the table(s)? As many as we can :-)
More seriously, there could have from 0 to .. N (N could be great
great, say 10000), and the number of records will vary in the time (by
insert/delete) !
More practically, most of those tables/databases would have a little
(say < 100 or 200) number of rows.
Have you thought about normalising your data yet?

Well, this is already done ;-)

[Jeff North]

On 4 May 2004 14:59:07 -0700, in mailing.databas e.mysql
ab*******@skybe l.net (Greg) wrote:

| Jeff North <jn****@yourpan tsbigpond.net.a u> wrote in message news:<i2******* *************** **********@4ax. com>...
| > On 4 May 2004 02:34:58 -0700, in mailing.databas e.mysql
| > ab*******@skybe l.net (Greg) wrote:
| >
| > >| Hello,
| > >|
| > >| I've to manage many 'table' (having same scheme) on the same server.
| > >|
| > >| And I ask myself what could be the best to do (and if you know, why) :
| > >|
| > >| Creating as many database (the name would be a 8byte int value
| > >| (converted to a string)) as necessary, all with the same table struct
| > >| (and table name), or create 1 database and in it create also as many
| > >| table as necessary (so also a 8byte int value as name) ?
| > >|
| > >| Has mysql some limitations on that (database/table) and what about the
| > >| performance issue ?
| > >|
| > >| Thanks for your help.
| > >|
| > >| Nb: I use innodb database/table.
| >
| > Is there any particular reason why you want/need to have multiple
| > tables?
|
| Yup, there is :-)
|
| > Why not just have a single table and add the field that would be the
| > table name.
|
| Cause until now, I had indeed all in 1 big table..
|
| > This would make maintenance, searching, sorting etc very much
| > simplified.
|
| Hmm not necessary I think, and more particularly if one wants the
| system to be 24h/24h up, having some "separation " at an higher level
| (than row) would be of great interrest. For example, when 1
| table/database crashs, others users can continue working with others
| tables/databases...
....and if the entire system goes down????
| when you want to delete 1 whole table/database
| (separated from many others), that has far less impact on performance
| than deleting somes rows within a bigbig table beeing continuesly
| asked for 'query' (normal query (40%), update (35%), and some times
| insert or delete (25% for the 2))... I think.
Database maintenance should be scheduled for off-peak times so this
would have little or no impact on the users.
| > Performance will depend upon how the table is set up and what fields
| > you have indexed.
|
| Well the rows are pretty simple:
| (1 bigint, 1 int) as primary key,
| 1 int,
| 5 tinyint, and
| 4 bigint (representing encoded date value).
| But there are 6 index also (5 on 2 fields, and 1 on 3 fields).
|
| And more, the bigint beeing in the primary key is used on all index..
| That bigint would become my database/table name in fact... so that
| would reduce the primary key to an int, reduce the 5 index on 1 field
| and the last index on 2 ... (I don't have to do "cross query" on
| differents values of that field).
|
| > How many records do you estimate will be in the table(s)?
| As many as we can :-)
| More seriously, there could have from 0 to .. N (N could be great
| great, say 10000), and the number of records will vary in the time (by
| insert/delete) !
10-20K records in your database. Get back to me when you can start
talking about millions of records.
| More practically, most of those tables/databases would have a little
| (say < 100 or 200) number of rows.
|
| > Have you thought about normalising your data yet?
| Well, this is already done ;-)

---------------------------------------------------------------
jn****@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

[Jeff North]

On 4 May 2004 14:59:07 -0700, in mailing.databas e.mysql
ab*******@skybe l.net (Greg) wrote:

| Jeff North <jn****@yourpan tsbigpond.net.a u> wrote in message news:<i2******* *************** **********@4ax. com>...
| > On 4 May 2004 02:34:58 -0700, in mailing.databas e.mysql
| > ab*******@skybe l.net (Greg) wrote:
| >
| > >| Hello,
| > >|
| > >| I've to manage many 'table' (having same scheme) on the same server.
| > >|
| > >| And I ask myself what could be the best to do (and if you know, why) :
| > >|
| > >| Creating as many database (the name would be a 8byte int value
| > >| (converted to a string)) as necessary, all with the same table struct
| > >| (and table name), or create 1 database and in it create also as many
| > >| table as necessary (so also a 8byte int value as name) ?
| > >|
| > >| Has mysql some limitations on that (database/table) and what about the
| > >| performance issue ?
| > >|
| > >| Thanks for your help.
| > >|
| > >| Nb: I use innodb database/table.
| >
| > Is there any particular reason why you want/need to have multiple
| > tables?
|
| Yup, there is :-)
|
| > Why not just have a single table and add the field that would be the
| > table name.
|
| Cause until now, I had indeed all in 1 big table..
|
| > This would make maintenance, searching, sorting etc very much
| > simplified.
|
| Hmm not necessary I think, and more particularly if one wants the
| system to be 24h/24h up, having some "separation " at an higher level
| (than row) would be of great interrest. For example, when 1
| table/database crashs, others users can continue working with others
| tables/databases...
....and if the entire system goes down????
| when you want to delete 1 whole table/database
| (separated from many others), that has far less impact on performance
| than deleting somes rows within a bigbig table beeing continuesly
| asked for 'query' (normal query (40%), update (35%), and some times
| insert or delete (25% for the 2))... I think.
Database maintenance should be scheduled for off-peak times so this
would have little or no impact on the users.
| > Performance will depend upon how the table is set up and what fields
| > you have indexed.
|
| Well the rows are pretty simple:
| (1 bigint, 1 int) as primary key,
| 1 int,
| 5 tinyint, and
| 4 bigint (representing encoded date value).
| But there are 6 index also (5 on 2 fields, and 1 on 3 fields).
|
| And more, the bigint beeing in the primary key is used on all index..
| That bigint would become my database/table name in fact... so that
| would reduce the primary key to an int, reduce the 5 index on 1 field
| and the last index on 2 ... (I don't have to do "cross query" on
| differents values of that field).
|
| > How many records do you estimate will be in the table(s)?
| As many as we can :-)
| More seriously, there could have from 0 to .. N (N could be great
| great, say 10000), and the number of records will vary in the time (by
| insert/delete) !
10-20K records in your database. Get back to me when you can start
talking about millions of records.
| More practically, most of those tables/databases would have a little
| (say < 100 or 200) number of rows.
|
| > Have you thought about normalising your data yet?
| Well, this is already done ;-)

---------------------------------------------------------------
jn****@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

[Greg]

Jeff North <jn****@yourpan tsbigpond.net.a u> wrote in message news:<uj******* *************** **********@4ax. com>...

10-20K records in your database. Get back to me when you can start
talking about millions of records.

Well, 10-20K records, even with say 1K, that's for 1 (and only 1)
table.
I forgot to mention that I would be happy if I could use up to 50K of
those databases/tables ...

I just have finished the work today for the <many databases with 1
table> option and that's seems to give effectively more 'performance'
(queries are quicker for same number) than the <all in 1 database - 1
table>. But I have to do further tests and haven't not yet optimised
the queries for that 'split'. And I'm making tests with only 3K
databases.

[Greg]

Jeff North <jn****@yourpan tsbigpond.net.a u> wrote in message news:<uj******* *************** **********@4ax. com>...

10-20K records in your database. Get back to me when you can start
talking about millions of records.

Well, 10-20K records, even with say 1K, that's for 1 (and only 1)
table.
I forgot to mention that I would be happy if I could use up to 50K of
those databases/tables ...

I just have finished the work today for the <many databases with 1
table> option and that's seems to give effectively more 'performance'
(queries are quicker for same number) than the <all in 1 database - 1
table>. But I have to do further tests and haven't not yet optimised
the queries for that 'split'. And I'm making tests with only 3K
databases.