Many Databases With 1 Table Or 1 Database With Many Tables
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 ?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
100 Databases, 4 Tables Each, Or 1 Database, 400 Tables
I was able to build, tweak and do whatever necessary to establish a rather different project but am worried that it may sooner or later crash due to idiotic programming / database usage. In essense I took 1 database with 4 tables and duplicate it 100 times BUT because I need to connect to the different tables within the php script and am not experienced enough to created nested database connections, I duplicate the 400 tables within the same database. What I want to know from a MySql perspective, would it make any difference if the script "play around" with 1 database connection and 400 tables or would 100 databases with 4 tables each be better? (Maximum database queries to only 17 tables at a time)
View Replies !
View Related
Pull Data From 2 Database Tables Into 1 Table On My Site.
I got 2 tables: a dev_tasks and a task_history table. The task table has to record information about the task I am working on AND the history table is for the task history logs. I am trying to display info from both tables into 1 table on my site. Most of the info will come from the dev_tasks table BUT i want to dedicate on column of the displayed info for the latest log made to the task_history table. The common column to both tables is the 'taskid' column. What would the query look like to pull the info from both tables, knowing that the taskid is common to both and that I want to display the last log per tasks from the task_history table?
View Replies !
View Related
Two Tables From Two Databases
I have two databases in mysql server. Say I have a table called 'table1' in database A and 'table2' in another database B. I want get two coloumns' data (colA1, colA2) of table1 into their corresponding coloumns (colB1,colB2) in table2. How would I go about doing this using sql query?
View Replies !
View Related
Databases/Tables
I was thinking, that trying to make a database/table and if it failed then it already existed but then i thought it might overwrite instead so i figured i would ask before i tried.
View Replies !
View Related
Combining Two Databases/tables
Im soon oging to be setting up a website which will host forums and auctioning software, the problem I have is that I dont want to have people having to register for each one. I would like it so that once they register they can use the same login name for each feature.
View Replies !
View Related
Phpmyadmin :: Tables And Databases Not Showing Up
I have a webserver running the latest stable apache/php/mysql, i tried to install a mail client (dovecot) but it installed mysql along with it. I reinstalled the mysql I had before and found that a bunch of tables needed repairing (had not been closed). Now the problem is an odd one, all my sites work fine, but phpmyadmin says there are no databases. The dropdown still has a list of databases though. If I select one then it says there are no tables. I can run the query "Show Tables" and it lists the tables as a result (so it can see them).
View Replies !
View Related
Searching Multiple Databases And Tables
I have about 8,000 tables spread across 26 databases because my webhost won't allow me to use more then 100Megs per database. Each table name is a unique account name. Inside the table the fields I am interested in are date and dollar. I have a text file with all 26-databases, and another text document with all 8,000+ accounts. I am looking for who the top outstanding dollar accounts are for a particular day. I currently use php to open up the account.txt file, and the database.txt files. I take the first account entry and connect to each database until I find the database with the account table. Once I find it, I run the below query and insert the dollar, date, and table name into an array, then move on to the next account name. Once I have them all, I sort the array and can now spit out the top 20 outstanding dollar for that day. Is there a way I can search a full database without specifying table name? Or is there any way I can make this no so kludgie? Code: select date, dollar from `AMEC1542` WHERE DATE(date) = '2008-12-05' ORDER BY 'dollar' LIMIT 2;
View Replies !
View Related
SELECT From Different Databases/servers Tables
I can't find the exact MySQL instructions to get data from tables located on a different database in the same engine and/or on different databases in different engines. I am working to a migration from Informix. In Informix a table name is defined as: <tbl_name> (as usual) or <db_name>:<tbl_name> (same engine, different db) or <db_name>@<server>:<tbl_name> (different engine, different db) How can, if possible, replicate this syntax in MySQL ??
View Replies !
View Related
Can Connect And List Databases But Not Tables
I have two mysql servers (Centos 4, MySQL 4.1.20-log) and I need to perform admin from a remote server. I've added in both servers a user with all privileges GRANT ALL PRIVILEGES ON *.* TO 'user'@'phpmyadminserver' IDENTIFIED BY PASSWORD 'XXXXXXXXXXX' In the first one I can connect and list databases and tables. In the second one I can only connect and list the databases. A show tables command shows me : show tables; Empty set (0.01 sec) Any ideas?
View Replies !
View Related
Syncronize 2 Tables From 2 Different Databases Using A Cron Job
I want to set up a cronjob to syncronize 2 tables (only) from one database to another. The databases have the same content and reside on the same MySQL server. I want this because I have 2 websites using the same dontent from this table and I don't want them to use the same database, I prefer them to use different databases. The server is Ubuntu 8.04, if it matters, it has apache2 and php5. MySQL is also 5. I'd apreciate any help you can give me, I didn't get much from the Ubuntu forum, regarding this thing, and I hope I'll find more support here. BTW, since I'm really a noob, please post an example easy to understand rather then the explanation of the entire process :). Let's just call, for this example, the datababes DB1 and DB2 and the tables TB1 and TB2
View Replies !
View Related
How Can I Find Difference In 2 Tables In Different Databases With Same Structure
I am developing an application in VB.net 2005 that synchronizes two mysql databases of same structure. I have done most of the work but having problem in finding the difference in records in 2 tables. Let me explain the scenario There is a table in the local database having name "user". The same table exists in the online version of the database. I want to find the difference of records in the 2 tables so that i can delete surplus users from the online version of the database. I am trying something like this, which is not working ....
View Replies !
View Related
Multiple Tables Versus Mutiple Databases
In a setting of a web based application (typical LAMP installation) where multiple web users may be logging into the app and accessing data for one of numerous subgroups, each of which requires about 30 tables to run the application, which design will perform better: A) a single database with a set of identically structured tables for each subgroup differentiated by table name or B) a database for each subgroup, each with the same table scheme. In either case the two tables any individual web user will most commonly be accessing for their group have ten's of thousands of records, the remaining 28 or so required tables being much smaller (less than 100 records) and having group specific variables and data to control the application. In all cases the web application interface uses the same php code with the group identifier declared at login which selects either the table subset or group specific db. In case my question isn't clear scenario A would have a single database with a master table that controls the group numbers by an id, say 1 to 20 and then a set of 30 tables for each like (users_1, data_1, ...), (users_2, data_2, ...),...,(users_20, data_20,...). In this case the single database would have in excess of 600 tables. Scenario B would have a master database with a table to store individual group id's and each group id would have a database with a name based on their id like db_1, db_2,...db_20. Within any db_* there would be identically structured tables user, data, ... . In this case there would be 20 databases each with only 30 tables plus the master control db.
View Replies !
View Related
Multiple Databases Or Single Database
Can anyone plz tell me the advantages and disadvantages of using multiple databases / single database for all tables. I have a site with more than a 100 tables. Current all the tables are in a single database, so that I can access it with a single Database Wrapper Object (PDO). Should I split it into multiple databases? Will it affect the speed?
View Replies !
View Related
A Huge Database Or A Lot Of Small Databases
I have a question regarding database performance. Which of these two designs would be better: -Having one huge database with 50 tables or so (each table having millions of rows) or -Having a lot of databases with the same amount of tables, but each table would have only thousands of rows *The mysqlserver is running on a P4 3.0 ghz with 1 gb in ram, but I may upgrade my server soon. *Privileges to create databases is not a problem since I own the server
View Replies !
View Related
Method - Remote Databases To Central Database
I am looking for the best approach to move data from four remote MySQL database applications back to a central MySQL database in near real time. Each remote database has a low number of transactions up to 15 per second of large data sets (12,000 items). The central database will be replicated across two servers for redundancy.
View Replies !
View Related
Database Design: How Best To Manage Data Across Multiple Schemas / Databases
Our organization is looking for the best design for managing shared data. Specifically we wish to have a master table for data related to a Person. That person table would be shared by any other databases within the company that needed it. Of course, we do not want to share the whole master table, just the rows relevant to specific application that will be using it. We also want the sharing to be as transparent as possible to both the users and the support / development staff. Using a view seems like the way to go, BUT...each application has tables with FKs to the Person table. We can't do FKs to a view. We can have the application reference the view for selects / updates to the Person table, and have the other tables define their FKs to the master Person table, but we're a little worried about security since we can't narrow their permissions to only certain columns & rows like in the view. Oh, I am not doing a good job of explaining this.
View Replies !
View Related
Extracting Data From Large SINGLE-table Database To MULT-table Relational Database
I have a very large single-table database of articles that I want to convert to a multi-table, relational database. The existing single-table database contains fields for article author, article source, and article category, where several 'author', 'source', and 'category' IDs repeat dozens of times for hundreds of different articles. I want to create seperate tables for author, source, and category and populate the new tables by extracting data from the original single-table database by unique ID field. I figured out how to use INSERT and SELECT to pull data in new tables, but can't figure out how to pull only a single instance of a unique author, source, and category to create master reference tables for author/source/category.
View Replies !
View Related
Database Design Set Up Question-Many Tables Vs 1 Table Design
I have set up a construction business on the web and recently switched to MYSQL from Access. Question I have is I have four basic project tables (Specs, Materials, Labor, Tasks) that have about 2200 items in each that get uploaded into 4 master tables with the projectid and clientid's I am wondering if it would be better database design to make separate tables for each project (4 tables for each project) instead of having an ever increasing 1 large table for each category holding all project info? I could number the tables with the projectid ( 1091_materials, 1091_specs, etc ) Uploading these files has been having problems.
View Replies !
View Related
Moving Table Data Between Databases
I've recently got a 2 new domains and with that 2 new databases. The name of one of the new databases is different from the database on my old host and I have 2 sites incorporated into that old database. What I'd like to do is split the old database into 2 newly named databases seperating the tables so I end up with the 2 databases ready for the new sites. The old site doesn't have phpmyadmin installed which is what I'm used to using and this DBTools software doesn't appear to allow me to select the tables I want to dump and export to a new .sql file in multiples.
View Replies !
View Related
Adding A Table To All Exisiting Databases
I've been using mysql for 3-4 years now, but am a bit rusty. I'm trying to add to an app that I wrote for my personal business a few years ago. What I want to do is add a new blank (or populated) identical table to all the databases (except perhaps to the databases 'mysql' and 'test' which come with the system), using the CREATE TABLE command. I already have this command stored as a file (such as addtablescript.sql). For example I could do: mysql -u root -p dbname < /pathto/addtablescript.sql But this would add the new blank table to just the database "dbname". I want to add it to all of the databases without having to do this manually for each one, so I can write a small batch script for my wife, etc., and she won't have to do the hard work, also others that use the program. Is there some way I can globally add the table easily to all the databases? Since I also use php, perhaps if the above is not possible, I could run a loop in php to do this with mysql_fetch_object and then an exec command for each database, but I'm trying to find the easiest way.
View Replies !
View Related
Separate Databases Vs Separate Tables
I have a question regarding setting up MYSQL. I'm a old dBASE programmer from the 1980's. I know about relational database work. What I want to know is what are the advantages and disadvantages in setting up MYSQL in one database with many tables vs separate databases with a few tables in each database. It would seem more advantageous to work with all the tables in one database.
View Replies !
View Related
Multiple Databases Or Multiple Tables
I have an application that is supposed to make multiple connections [around 10 connections per second] to a mysql database. The connections are done by mutlitple users at potentially the same time. I have to decide if I should use : 1)one database with one table for all users. 2)one database for each user. 3)one database with one table for each user. I would really appreciate if you can tell me what choice is best and why.
View Replies !
View Related
Is It Better To Have One Big Table Or Break The Table Into Multiple Tables
would like to know what would be the more efficient method if were to create a product table that had multiple values that i would seldom reference? When sql calls for a table does it reference the whole table in the query or only specific cells? for instance Product(primary key) | Price(2008) | Price(1998)| Price (1989)| Each of the Prices will have sub prices of each month in the that year. Should i make individual tables for the prices for each year instead of dumping them altogether into one table? is this more efficient when web users get the data? what are the advantages and disadvantages in each method.
View Replies !
View Related
Move Table From A Database To Another Table In A Database
I am currently developing a project as I am only a web designer. I need to move a table from a database to another table in a database. I am using SQL Server 2005 Express. How is this done? I have been stuck for months trying to figure this out! I have even bought SQL Server Express 2005 for Dummies and cannot figure this out! I need someone to hold my hand and walk me through this literally! Or give me syntax code to do this.
View Replies !
View Related
How To Look The Tables In A Database
Can I look at the tables in a database on my hosts server? Like most hosts, the only access they give me is through DirectAdmin, but in the absense of a DirectAdmin forum, I guess the MySQL forum is the logical place for this post. I guess a MySQL command line would allow me to do this, unless there's a different way. How is this done?
View Replies !
View Related
Value In All The Database Tables
Is it possible to search for a value in all the database tables using a query ? I assume that a simple query won't do the trick, and a UNION on all tables isn't possible because I wish to keep it as much dynamic as possible (i.e. scheme changes at will). So what I'm actually asking is this : Someone possible knows or heard of a utility that can search the whole database for some value and return the output in some human readable format ?
View Replies !
View Related
Database Tables
Use the PHP admin interface with the SQL database. I would like to populate tables of the database from an Excel text file. I keep getting error messages. Does anyone out there have any experience with this? I appologize if this has already been addressed. I searched the forums and did not find this info.
View Replies !
View Related
Database With 100.000 Tables
How can I dump a database with 100.000 tables? Right now I got this error: mysqldump: Got error: 1105: File './usr_web20_4/hilarypics_usergroups.MYD' not found (Errcode: 24) when using LOCK TABLES the amazing thing is that the table exists and that the error occures one with this, another time with anohter table. I read something that solves the problem by increasing the max amout of tables to be opend set to default to 1024 in the mysql-config file
View Replies !
View Related
Truncating All Tables In A Particular Database.
Is it possible to empty all data in a database containing >300 tables? What is the best solution to truncate all the tables in a database? As I know one way is to export the database structure, drop the database, recreate the database and reimport the database structure. Is there any other way to do it ?
View Replies !
View Related
My Tables Back Into A Database
how I can get the backup ( just a file copy of the directory with .frm, .MYI and .MYD files) back into a newly created database. I am failry new to MySQL. Due to a crash of my Windows 2003 server I was in a hurry creating a very quick backup so I didn't tokk the time to find out what a correct backup mechanisme shoul be. Now I have a new server and installed the latest MYSQL (I think I had an earlier version before) I just copied the files into the "mysql datafiles" folder. It doesn't pick them up automaticaly. Can anybody tell me how to get them back in.
View Replies !
View Related
XML Specs For Database Tables
Is there any program that takes database schema in some decent format like XML (or html or plain-text) and generates the following: 1. C++ classes that represent each table. 2. C++ methods that let me get/set database records of each table.
View Replies !
View Related
Database Tables Normalization
I am working on a shipping/receiving module for a service database. Well, in a sense, it is a movement module. Moved out of warehouse, moved into warehouse. I'm wondering if these two tables are different, or if they are the same and just need a column added called "direction" (ie, in or out). They would be recording the same exact fields, but just opposite directions. Which leads me to believe that they are not related to each other in that sense. I have not been working with databases for too long (less than 1 year), no formal schooling on them, so this is all new to me, but I have had great luck with a test database that was running them as two separate tables. Another issue would be inventory would become more complicated if I use one table. It would be two SELECT [QTY] FROM tblMovement WHERE (([Direction])=0/1). Although, that might be more efficient as it is the same table. I'm not sure.
View Replies !
View Related
Database Sizes And Tables
I have created many sites using php & mysql. All the databases I have created in the past and used have never been more than 10MB. This seems massive to to me. I was just wondering and interested to find out what is the biggest mysql database you have seen running on a server or worked on? What is the biggest table size you have seen or worked on? Have you ever had to repair a mysql before. How did you repair the database and how much data was involved?
View Replies !
View Related
|
TRACKER
