473,569 Members | 2,475 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

many databases with 1 table or 1 database with many tables

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.
Jul 20 '05 #1
8 1769
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
---------------------------------------------------------------
Jul 20 '05 #2
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
---------------------------------------------------------------
Jul 20 '05 #3
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 ;-)
Jul 20 '05 #4
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 ;-)
Jul 20 '05 #5
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
---------------------------------------------------------------
Jul 20 '05 #6
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
---------------------------------------------------------------
Jul 20 '05 #7
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.
Jul 20 '05 #8
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.
Jul 20 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3552
by: Alessandro Ranellucci | last post by:
I'm building a PHP+MySQL application for a large website, with 100.000+ records in the main table, each one with a lot of dependencies in other SQL tables. Thus each page view requires many SQL queries and/or joins. Since I want to optimize performance, I'm wondering whether it would be better to serialize full objects instead and store them...
6
1932
by: cover | last post by:
If you're writing many databases that aren't necessarily associated with each other (ie parts, vacation days, how you like your steak done, and school you attended, etc; as examples), does it make more sense to have one database name and several tables for the data topics above OR multiple databases since they aren't associated with each...
4
23613
by: Jason | last post by:
I realize that FK cannot be created across databases. The question I have is what are the alternatives? In my thinking I have two choices. 1) First choice (which I read in a different post) is to use a stored procedure as a constraint to go check the table in the other database. 2) Second choice would be to set up publish/subcribe and...
15
2131
by: kimi | last post by:
I have just started working on a project that is partially complete. It is an application that is using access to store test results. The test results are being stored in two Access 2000 databases. DB #1 = StudentDB DB #2 = TestResulstsDB Why are there 2 dbs? I do not know - but that is one of the tings that we will be changing....
1
4192
by: Prakash RudraRaju | last post by:
Hi, I have recently migrated MSAccess Tables (nearly 120) to MySQL. To test successful migration I want to compare all tables. I have linked MySQL tables through ODBC connection. I want to create a report that compares all records between all tables in both databases. I am looking for a report with differences in number of records and...
4
4992
by: Dirk Olbertz | last post by:
Hi there, I'm currently about to redesign a database which you could compare with a database for managing a library. Now this solution will not only manage one library, but 100 to 500 of them. Currently, eg. all the data about the inventory (books) is held in one table for all the libraries. Is it useful to spread this to one table for...
48
3820
by: phillip.s.powell | last post by:
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don't ask) I have database Spring with table Students I have database Summer with table Students I am tasked to produce a query of all students in both tables with no duplicates. No clue whatsoever.
7
2565
by: jnikle | last post by:
I have a database in A2003 format that's giving me this error, but it's not the same situation I've been reading about on here. In my development copy, I have imported copies of the backend's tables, and everything works fine. I can also compile the database like this, and it works fine. However, as soon as I get rid of these copies and...
1
1984
by: rfranzl | last post by:
Hello, I need some help, I have about 200 databases that are copies of an original database that has a similiar table in all of the databases, called "tblCodebook". What I am trying to do is to create a macro that will go into each of the 200 databases, run an identical "unmatched query" to find any records that might be in one of the copies...
0
7711
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7628
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8145
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7694
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6317
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3679
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2128
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1236
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
970
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.