Small Databases
I am wondering if having a database for the client back office, a database for the static website and a database for website stats would be a good idea.
The client database would hold credit card info, user info, reg info, project info, client messages and more.
The website database would carry all the content for the pages of the public website like about us, contact, home page, news and such.
The website stats database would keep track of user agents, new hits, returning hits, keyword searches made on the client websites and all that jazz.
Should I make this all ONE giant database or three smaller database?
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
Handler_read_rnd_next = 13k On SMALL Db
I have two very small DBs. One for a little number game, which only 6 people hit 1-3 times a day. The other is for a prototype of a game I'm working on, and there are no more than 3 users on this. Using phpmyadmin, I check the page with runtime info and I see Handler_read_rnd_next 13 k If I reload this page a couple times, it goes up to 14k. Most of the queries I use look like Code: SELECT c.color, b.percent FROM tbl_creatures a, tbl_creatures_colors b, tbl_colors c WHERE a.creature_id =1 AND a.creature_id = b.creature_id AND b.color_id = c.color_id And the EXPLAIN SQL for these comes up with Code: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE a const PRIMARY PRIMARY 4 const 1 Using index 1 SIMPLE b ref PRIMARY,color_idPRIMARY 4 const 2 1 SIMPLE c eq_ref PRIMARY PRIMARY 4 hq_proto_creatures.b.color_id 1 To me, this all looks fine. I can't tell if all the Handler_read_rnd_next is due to the phpmyadmin status checks or not.
View Replies !
One Big Table Or Many Small Ones?
I am designing an interactive website consisting of a few pages and I want to track individual user progress. I was wondering if it would be better to create one big table for all values on all pages (Which may have a lot of columns, easily hundred or more), or created a separate table for each page (so I could have some 10 tables with 10 columns or more). Or does it not matter?
View Replies !
Big Select Vs Small Ones
I have an array of 10 IDs. I need to query the db for values associated with these 10 IDs. In a lot of places, I have seen people using a php foreach like this foreach (ids in array) { query = SELECT values FROM tables WHERE id = [id 1 in array] do query store values } we could build one big select statement like this: query = SELECT values FROM tables WHERE id = [id 1 in array] OR [id 2 in array] OR [id 3 in array].... until we have ids.
View Replies !
One Big Database Or A Lot Of Small Ones?
My boss is implementing a system where a lot of different sites will be using the same exact database structure. He has it in his head that things would be simpler with one database shared by hundreds, possibly thousands of sites. I feel like each site deserves it's own database, even if it involves more maintainence.
View Replies !
Small Query Optimization
I need this query optimized using indexes. So I was wondering how I could optimize it to work with an index for speed.The query is: Quote: select ided from products where dates <= 2006-11-11 or cost=0.00 or url not like 'http://%' or imgurl not like 'http%' I tried making an index on (dates,cost,url,imgurl) but it doesn't seem to use the index when I do the explain part. I find that even when I miss of the like parts of the query it still is not using the index. It seems to not use the index because of the "or" that is used in the query instead of the "and". Could this be the reason and how could I over come this so that it will use the index.
View Replies !
Backup A Small Database.
I'm looking to backup a small mysql database. The problem is mysqldump does not exist on the server I'm trying to export the database from. Does a php script exist which I could use to do essentially the same thing as mysqldump?
View Replies !
Small Favor Gone Wrong
I have a VPS account and I am trying to move my sister's professional organizations site onto my server more or less as a favor (getting paid a little - but not really worth my time). It is being hosted by a professional hosting company that supplied me with ftp access to download the site for the move. I am running into a problem because I don't know how to set-up/use mySQL through UNIX. Previously I have used the Plesk interface provided with my VPS account. Plesk (or at least my hosting services configuration of it) won't allow me to create the user I need in order to import the database because the user ID is contained within the user PW. (this is the database coming from the vendor). The original host has been unhelpful at best so I thought before I go to them and ask for assistance (that I am pretty sure I won't get anyway) that I would post here to find out if there is any easy way through this roadblock or if any the unix commands are simple enough, how I would proceed that way.
View Replies !
One Large Table Or Many Small Tables?
I'm trying to decide whether to use one large table or many small tables. I need to gather information from various devices (about 500). Each device has its own Id and some data. Should I use only one table with an indexed column for the ID and another column for the data, or should I use 500 tables each with only one column for the data?
View Replies !
MySQL Upload Small Files
I'm uploading small files (>10K) and mysql is deleting it. The one field is a blob field and everytime i test upload a file thru phpMyAdmin within 2 seconds mysql deletes the row. Theres nothing in mysqld.log, there another log i should be checking out?
View Replies !
One Big Query Vs Lots Of Small Queries
I am in the process of migrating a MySQL database from one schema to another and am writing a script to extract the info from one table to be inserted into multiple tables on new new db. My question is this, is it better for me to make one giant query (about 1 million records returned) on the source table, manipulate the data, then enter in the data by cycling through the results, or would it be better to split up the query on the source table into lots of smaller queries with a short pause in between?
View Replies !
Structure Of Small Social Network
I need to setup a social network type mysql database, but not as intense as friendster, thefacebook, myspace, etc. i have a structure set up but it think it's weak and extremely limited: Code: +---------+-----------+ | user_id | friend_id | +---------+-----------+ | 5 | 4 | | 1 | 7 | +---------+-----------+ i fear it's going to be a lot of redundant IDs in a column, which i think could be a problem when retreiving friends: Code: +---------+-----------+ | user_id | friend_id | +---------+-----------+ | 5 | 4 | | 1 | 7 | | 1 | 9 | | 7 | 9 | +---------+-----------+ i want to do a "bob is friends with john" and a "jackie knows sally through richard" (aka friend of a friend). that's it and nothing more. how do i structure mySQL database to be able to this?
View Replies !
One Huge Table As Appose To Many Small Ones
i have a very large XML DB (60gb+ and growing on a daily basis) which holds complete life cycles of stock option , i used to hold it all in folders with each folder named like the option paperId so it seemed logical to me to hold it now in diffrent tables for each paper even tough the are all the same exact table , now i'm having some problems with hibernate (one of the biggest reasons i left XML) and so many tables , is it more effiecent to keeeping data like that ? ofcours i will have to change the PK from datetime to paperId+DateTime if i decide to change to one big table ... how can i calculate diffrence in retrieving data ?
View Replies !
Several Small Calls Vs One Large Call
I am working on a php interface to a mySQL db which will pull lots of rows from the database. Assuming I am selecting a hundred rows by their auto_incremented ID's, is it better to do this with a loop of 100 "SELECT ... WHERE id=" calls, or to do it with one single "SELECT ... WHERE id IN(...)" call, with IN() holding a list of the hundred items.
View Replies !
One Large Table Vs. Many Small Tables
I'm working on a design using PHP & MySQL and I'd like to get some opinions on this. My design has several tables that will be referenced but I'm wondering if those tables should be broken down even more and referenced more dynamically. The reason that I wonder about this is for long term goals. I hope that eventually there will be two or three thousand records that will be used on a regular basis. These records will need to be separated into groups, but I'm not sure if I should use a field in the database table or create a new table for each group. If a few hundred records could be in each group, do you think it's better to use one large table with a field for the group ID, or a new table for each group?
View Replies !
Help With A Small Query Optimization Using An Index. THANKS!
I need this query optimized using indexes. So I was wondering how I could optimize it to work with an index for speed. The query is: Quote: select ided from products where dates <= 2006-11-11 or cost=0.00 or url not like 'http://%' or imgurl not like 'http%' I tried making an index on (dates,cost,url,imgurl) but it doesn't seem to use the index when I do the explain part. I find that even when I miss of the like parts of the query it still is not using the index. It seems to not use the index because of the "or" that is used in the query instead of the "and". Could this be the reason and how could I over come this so that it will use the index.
View Replies !
Small Database, High Load
I have a very heavy mysql database used website. Data is constantly being updated and at peak there is well over 1,000 people online, after it hits the 1,000 mark, the website starts to lag pretty bad. My database is only small at around 90mb. We have a web server for the files, and a database server for the database. The load on the web server is fine. But the database server is pretty high. Would replication reduce lag and load? if i was too add a second database server?
View Replies !
Inserting Small Image Directly Into Database
How do you insert an image into a table directly using something like data studio? I have tried googling it and it seems all the examples are using a language to do it for you. The images are between 2-4k and the table is just an int column followed by a blob column. I just thought the statement would go like INSERT INTO sometable VALUES (1,'image.jpg'); The web suggests uploading and downloading in PHP (which im using) but im building the data first using data studio then just displaying the table information using PHP. Before i go into displaying the information on the page i just wanted to make sure the insert query is correct.
View Replies !
Small SQL & Php Webpage Field Database Task
In a webpage, imagine a little section where the user fills in his/her contact details to register. Among this section is a field called "Country". The task is to make this field automatically generate the persons current country of residence. I am told, that the IP ranges and the country to which they belong is in a CSV .xls file. I am also told that the best way to do this is to: 1) Insert the CSV into database table (MySQL). 2) Use php to get that country name onto the website field.
View Replies !
Large .sql File (break Down To Small Parts)
i have an oscommerce installation that generates a 200mb sql backup file. i want to try and rebuild the installation on my local pc (localhost) to emulate and play with, unfortunately the 200mb file is difficult to handle. phpymadmin seems to crash. i have console access on the host however. is there a way i can break down the sql file into smaller increments for easier uploading?
View Replies !
How To Reset Yahoo Small Business MySQL Database
I am a new user of "Yahoo Small Business" web hosting service and I changed a lot of settings in my "MySQL Database" using "phpMyAdmin" utility during my learning period. Now, I want to reset my Yahoo Small Business MySQL Database to default settings or to reinstall it (I want it to be the same as it was newly activated). Note: I tried to repair my database with Yahoo's "Database Setup" page and the message "Database scheduled for repair" appeared for a few days but nothing happened. And, I can not login to my database using "phpMyAdmin" utility. If you can help me to reset my "Yahoo Small Business - MySQL Database" I will be very pleased.
View Replies !
Small Tables Or A Few Big Tables
My Web site is going to use a MySQL database extensively throughout the Web site. It is a content management system type Web site with lots of users and written articles and other things. With my database I'm trying to figure out if I should create big tables that have a lot of rows in them or if I should create a lot of tables that are very specific to its data and use a lot of table joins in my select statements. I'm mostly concerned about performance. I'm thinking that if I make a lot of tables, then select statements I do will be really fast because my tables won't contain very many rows. But I don't know if this is true or not or if it matters for performance reasons or not. In my Web site I have a lot of different kinds of data that could put in many different tables. But, without data redundancy and without using extra storage space, I can still make a few big tables, and make that work. I just don't know which way is best for performance -- speed, I want speed. Anybody know what I should do or know a book I should read or a Web site to go to?
View Replies !
Cant See My Databases
I don't know what the problem is here. I used to be able to install MySQL and go to this interface (please see attachment). Right-click and 'create datbase' but it doesnt let me do that. The only options it gives me is flush hosts; flush logs; flush tables and flush threads.
View Replies !
Databases
I have a small residential design company and would like to create an online database so that I can start selling my plans online. I downloaded mysql a couple of months ago and played with it a little bit and even created a small database so that I could see what it could do. But how would I go about getting a database that people could do searches on with my website? Or is there another interface that I need to install ontop of mysql that will allow me to do this easiely?
View Replies !
Different Databases
I am developing an application that uses two separate databases. Each database is hosted by our ISP on a different IP address. I would like to write a query of the form: SELECT * FROM table WHERE db1_tbl.val = db2_tbl.val where db1 and db2 are my two independent databases. I write in ASP and I have a connection string to each database. What I need to do is select records from one database with a column containing values from the other. My current method, in code, is to open each recordset in turn, convert to an array and then do a look up from one to the other, which in code is complicated. Is there any way I can do it through a query?
View Replies !
A Doesn't Get All Databases
I have an odd mysql configuration. Suffice to say that I have over 6000 databases (small ones, all automatically created). When I run mysqldump -A, it appears that it's only getting a subset of the 6000. It's only backing up 1175 databases.
View Replies !
Use Two Databases
Use two Databases for authentication I would like to use two databases to authenticate users. The first database I would like to be checking usernames and passwords only and usernames in plain text and passwords to be in md5. The second database with the rest of the data the users need for privileges of the application that has to be in plain text. HOW? I have all the create scripts written but don’t know how to link the databases properly.
View Replies !
Where Are The Databases?
After having installed MySQL on a Windows machine, SHOW DATABASES shows test and information_schema. Yet, I see no files of these names anywhere in the MySQL directories. I'm curious about where it keeps them. More importantly, where do I put my own databases so I can access them in MySQL?
View Replies !
Each Set Of Databases
I would like to set up separate data directories and have mysql store data in those subdirectories. Each client would get their own directory. They would each contain the following databases: gl, 2004gl, 2003gl, ap, 2004ap, 2003ap, etc. I like to separate the database files for speed which was required when using MSAccess.Can this be done, or must I either wait for 5.0.2's schemas or redesign my database structure? I am using mysql 5.0.0 right now, but am only in the development phase, so I will upgrade asap before going into production mode.
View Replies !
How Many Databases Should I Use?
What do you think that would be better? To have 20 databases with 10 tables each database and a total of 2.000 rows in each database, or... To have 1 database with 11 tables and a total of 40.000 rows. How should I handle large projects?
View Replies !
How Many Databases Do I Need
I'm in the preparation stage at the moment of a really big project, now the project is going to entail people signing up to a website and then selling stuff online. We are looking at about 1000 people signing up to start and then growing to about 8000. The question I have is how many database's should I build the site around as there is going to be a "Super Admin" section where I will add all the products available on the site (about 15,000) and manage ad's and stuff like that. Then the person that pays to have an online shop will select products from the ones i have inputted and be able to change all his settings for his personal look to the site. Then there is the end-user who will come to the site sign up and then buy stuff, and his settings will need saving as well. So as far as performance goes would it be better to have a separate DB for each section ("Super Admin" - "User Admin" - "End User") or just use one and store everything in there?
View Replies !
Why Do Databases Goes Bad?
How often do databases go bad, and what is the usual cause? I've had flat file sites for 5 years that never had a problem, I've never had a problem with a MySQL based CMS thus far but I have with forum software and didn't know the cause. I hear of websites losing all their data from their MySQL database going bad. Is the solution to make a MySQL backup dump every so often?
View Replies !
Databases With Different Encodings
when I try to feed the dump into my server (mysql < DUMPFILE) as it is, I see the greek but I can't get the sorting correct. When I change the dump file (CREATE DATABASE ... CHARACTER SET greek) I cannot see the greek text. Furthermore, when I try to get the texts from a php page, I can't get it right either way. Any ideas? Also, is there a way to set the database encoding to something else than the default in php? I see there' s a "mysql_client_encoding" function, but no function like "set_mysql_client_encoding". And another thing. I get the utf8-encoded greek correct on php pages when I build php with "--with-mysql", which uses Client API version 3.23.49. If I build it with "--with-mysql=/usr/local/mysql" (where I have installed MySQL 4.1.5) yielding Client API version "4.1.5-gamma" in phpinfo(), I also get the greek text wrong (every character shows as "?"). Is there something I should configure at the Client API, and how do I do that?
View Replies !
Backup My Databases
I made a question several days before to describe my strange trouble of mysqldump. But I still can't figour it out. Well, I just want to ask another question whether I could just backup my databases by copying the data folder to some place? Then if I meet some disaster, could I just copy the backup folder back to recover my databases?
View Replies !
Show Databases
I just installed MySQL on my home PC and I tested out the show databases query, and it lists two: information_schema bbdatabank The second one is one I created myself, but I don't understand what the first one is. It is not listed in my 'data' folder, but mysql and test *are*, yet they aren't listed when I type the show command. Why is that? How can I get them listed so that I can use them, and what is the information_schema?
View Replies !
Large Databases
We are in the process of building an application to front end a database of about 2 million records. I would like to know if MYSQL can handle this and if not, what would be a good database to go with. We would like to use opensource as much as possible, but if MYSQL cannot handle this kind of volume then we will have to go in for commercial software.
View Replies !
Unable To ADD Databases
I'm just started the process of learning MySQL and databases in general. I just bought the book 'MySQL' written by Paul DuBois, who I gather is really active in this community. Anyhow.. Here's the problem that I'm having. mysql> GRANT ALL ON sampdb.* TO 'sampadm'@'localhost' IDENTIFIED BY 'secret'; Query OK, 0 rows affected (0.00 sec) mysql> show databases; +----------+ | Database | +----------+ | helpdesk | | mysql | | test | +----------+ 3 rows in set (0.01 sec) Anyone notice the problem? Even though I get no error.. the sampdb is not being greated. This is weird as I've been able to use this exact command in the past with no problems. I even tried logging in as root before starting the mysql client, still made no difference.
View Replies !
Linking Databases
is it possible to link databases within mysql? I have a access = database that I need to import into mysql this db consists of 4 other = dbs linked together sharing a common switchboard. Can I link the tables = in one db to tables in another db?
View Replies !
1 Username Databases?
Many ISP:s offer web-service with MySQL - with 1 user / 1 database. Have I understood right that this means there is that 1 read/write user only, and no chance to make read-only username, so only chance is to put username/password with write-rights into scripts?
View Replies !
Location Of Databases
It creates a folder for 'data' under c:mysql. If I want the data to reside under my local internet root directory, how do I tell Mysql where to find it?
View Replies !
Merging Databases
Suppose I have a relational database, called "BranchA." I have a second relational database, of identical structure to BranchA, but with different data, and this one is called BranchB. Can I merge the two, into, say, a BranchC somehow on occassion?
View Replies !
Mapping Two Databases
I have a MySql database I connect through using mysql-connector-...jar under java. I need to co-exist with another MySql database in order to get this one job wherein the fields of the other database can map to fields in my database. For example, they have a customers table as does mine. There may be some differences in the fields and such, but, in broad strokes, the tables are redundant. I cannot, however, rewrite this application to use their format - both tables need to coexist. So I am wondering if there is not an accepted means to monitor changes in one database/table and forward those changes to the other database/table on an ongoing and real-time basis. Does such a procedure exist?
View Replies !
2 Databases With 1 User
I am trying to run a script that needs one user with two databases. My hosting company have told me that i need to create 2 databases and 1 stand-alone user in my hosting cp, then with phpmyadmin i need to grant access from the 2 databases to the stand-alone user. I have been reading through the mysql website for ages and trying lots of things that don't seem to be working. I am sure this would be very easy for someone who knows what they are doing! Is there anyone who could give me step by step instructions, or if they were feeling really generous i can give them access to the cp and they could do it for me!
View Replies !
How Do I Reconcile 2 Databases
I had a database with about 2 1/2 years of data. I was told to go through and delete everything older than 2008. Easy enough, considering the information is timestamped. Afterwards, I found out people still needed to access this data. Oh boy. Being the good admin that I am, I of course made a backup of the database before purging. While I was able to provide the people with the information from the backup database, it's now become a daily task of finding old data. While I could put the old data up in a seperate application, I know this will just lead to problems and confusion. What I was wondering is if there was anyway of taking the backup database, the current database, and combining them into a single database? I can't just input the .sql file, because there's data on the backup that's also on the current database. In addition, to possibly make matters worse, I ran an "OPTIMIZE" on each table.
View Replies !
Recover Databases
I was able to recover what appears to be the MySQL data folder from my hard drive (which was in a Ubuntu 6.06 laptop that just got it's motherboard fried). It has the databases names as folders with .myd, .myi & .frm files inside them. How do I restore/import my databases?
View Replies !
|
TRACKER
