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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 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 !
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 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 !
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 !
Huge Database Handling
I would like to have some hints about very large database handling with MySQL. I have a big database (10M+ users and their data) and I don't know if it's better to have just one single database or several smaller dbs (partitioned by user) that in any case will be running on the same machine. What is the advised maximun size (if there is) of a single database ?
View Replies !
Very Huge Database In MYSql
I am having very huge database (12GB) in mysql, with php i am using this data but i am not able to retrieve it. It’s giving time out every time because of its size. Is there any way to optimize this data?
View Replies !
Huge Database Problem
I have a database with 8.500.000 (aprox.) rows and I want to select a row like this: SELECT *,((DEGREES(ACOS((SIN(RADIANS(36.517)) * SIN(RADIANS(f4)) + COS(RADIANS(36.517)) * COS(RADIANS(f4)) * COS(RADIANS((-4.883)) - RADIANS(f5)))))*69.09)*1.6) as km FROM locdb order by km limit 0,1 (I've divided the lines for better reading). The syntax selects the nearest city acording to the latitude (36.517) and longitude (-4.883). The latiude and longitude colums on my table are f4 and f5, respectively. The problem is that (obviously) it takes about a minute to do it. Has anyone an idea how to improve the table or the syntax to get a better results?
View Replies !
Huge Database With 800 Customers / 3.500 Products
I need for a webshopproject all customers with all products in one file. I have already both informations in two different ones. Pls. find below the relevant info. product ProductID|pricegroup| customer knr|pricegroup|%calc|price It is possible that one product is related to 800 customers with 800 different prices. In my thinking the new DB should look like pricefile ProductID|knr|pricegroup|%calc|price.
View Replies !
MYSQL Database Alteration, Repair And Restore Takes Huge Time
I am trying to run alteration queries on one of my mysql tables which has more then 22,00,0000 lakh records. Its been 23 hours and the process is still running (I have to close all the sites running on server due to same). My server specifications: Red Hat 9.0 Pentium 4 3.0 GHz 2 GB Ram, Burst RAM 5 GB Running webserver, mail server as well. Is there a way I can view the minute process details, as what table record is being updated ?
View Replies !
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 Replies !
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 !
2 Databases - Export Certain Parts To Another Database
I have a database with all users profile information, but i would like to export ceritain parts into another database, and the layout and colum names in the second database will be differnt. How would i do this on all users, then when a new users signs up, it will happen to them?
View Replies !
Efficiency Of One Big Database Versus Several Smaller Databases
I did a search and didn't find an exact answer to my question. It's a pretty noobish question which is why I'm putting it here. ;) I'm writing an app that will likely have 50 to 100 tables. Is there any performance advantage to using, for example, 10 databases with 10 tables each instead of using one big database with 100 tables?
View Replies !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
Huge Pagefile
Am running mysql 5 on a win2k server. Installed with defaults, no changes. Have 8 databases running in the single instance. Have 2GB ram, 2 2.8GHz processors on the server and after about 2 weeks of running have a 1.8GB pagefile. Neither of the CPUs is running higher than 5% with peaks at about 30%. The database is supporting multiple websites with moderate traffic. The biggest table in each of the databases has only about 35,000 rows. Needless to say, the bigger the pagefile gets, the slower the respnse time. Any ideas on what settings I can make to keep the pagefile size down? Right now am rebooting the server once a week, but that's just a band-aid..
View Replies !
Huge Log Files
The database is completely backed up and nothing has been lost; the problem is the giant log files MySQL creates ate up our available disk space (all MySQL queries are logged, and with that worm hitting our forum we probably had about 8 gigabytes of logs). I deleted them all, but for some reason most services (e.g. MySQL, FTP, email) continue to report the disk as full, while I am free to copy files all over without problem using SSH. "du -h" executed in / indicates that we now have used 800 MB of our 10 GB disk usage quota. The most detailed error message I get is "/jail1: write failed, file system is full";
View Replies !
Huge Site
I wonder if MySQL should work with tables having milions of rows, e.g. you have image uploading site and you store daily around thousands of photos and regularly querying them. What are the techniques of this? Is it possible to do this with mysql? Do you know some books, articles about this? I thought about indexing or caching(of course if possible) and I want to learn something about this.
View Replies !
Huge Server Configuration
I have a server like 8way Intel Pentium 4 Xeon processor with 12GB RAM and 1TB harddisk space. All the tables size are over 10GB and they have over 100mm records. Could some one help me get an appropriate mysql configuration(my.conf)file for the machine. I understand ther are lots of factors depends on it to get a steady working configuration. All I need is some model configuration. I think later on I can tune thar up.
View Replies !
Count(*) For A Huge Table
I'd like to get a row count for a large table, but I've recently been considering if count(*) is too resource intensive? Is there a better way? A factor here might be that an exact count is not necessary, so if it's off by a few, I'm not concerned. Now, you might say "not an exact count?!?!?" You see, I was looking at SHOW TABLE STATUS, but I don't want to have to cycle through the rows to get to the table I'm interested in...
View Replies !
Huge Server Load
This is the TOP stats from my vps: http://img28.imageshack.us/img28/3528/clipboard014rg.gif As you see server load is too much and its getting more then mysql service stops and everything is normal. This happens for 2 days, hosting company made a Linux kernel update maybe that can be the reason. My VPS load was always too high and there is only 1 site got 10.000 unique visitors. I did so many optimizations on the mysql queries and also php. I cant afford a dedicated server at this time and I dont think it's not so much traffic.
View Replies !
Select With HUGE Criteria
what the best method is for doing a select where i have say 40,000 items of criteria. eg select * from clients where 1 or 2 or 3 or 4 ... or 40,000. I can buid the query up no problem using php, but the query would be huge. Is there a better way of doing this or should it be no problem for mysql.
View Replies !
SELECT On A Huge Table
I am using a 512MB server with a mysql 4.1.13. I have a table with 5 milion row ( table has a varchar 255 ). I need to download all the rows into memory ( from a C client ). After executing the SELECT query, I use mysql_store_result to take the data, but after one or two minutes, the server crashes and restarts and the clien connection is lost. I tried to use mysql_use_result, but strangely, the client doesn't load anything with that :/ ( it returns immediately ) In both cases I use mysql_fetch_rows to load de resultset. I have tried to put max_allowed_packets to 200MB, it doesn't change anything.. Does someone have and idea to load such table without crashing the server ?
View Replies !
Indexes For A Huge Table
I'm having some trouble with indexes and I'm pretty confused as to why this is not working. I have a 5GB table with over 2 million entries. I have indexing setup as follows: KEY `ticketposts3` (`creator`,`staffid`,`dateline`) I'm trying to run this query: ....
View Replies !
Deleting From Huge Table
i went about this the wrong way now that ive investigated it further but i ran a delete from query and its taking ages its been 7 hours now. should i interrupt? if i do will the table survive? can i check how far its got? if i was to forfeit the table it would be rather time-consuming to reconstruct so ideally i need to know this will finish in a few hours or a way to speed up now that ive started!
View Replies !
/var/lib/mysql - Huge Files
Directory /var/lib/mysql has 100's of very large files everyday, I have sub directories which are my databases for example /var/lib/mysql/mydb What what are all these files in the /var/lib/mysql directory -rw-rw---- 1 mysql mysql 261976048 Dec 11 05:58 #sql_36a6_0.MYD All the files are like the one above Many of these get written per day, isn't that excessive IO? Considering I can delete them , therefor they are not needed, why are they written then? What are they? I had 50GB of these files
View Replies !
Handling Huge Records In DB
I have two tables in my Database. Table1: Master Table Table2: Child Table [Transaction Table] Actually values inserted to child table is from front end. They will get the values from Flash. The Values which get inserted to the child table can be nearly 700 - 850 rows at a time of insertion. These values change when user do some modifications in the page then the new values from flash to PHP and corresponding old values are deleted in Database and again we are inserting the new values. But we think it will cause some issues in feature since the autoincrement value of the table will increase quite soon and reach higher value as we are deleting the old values and inserting the new values. we cannot update that since, the value we receive may be in addition as to add and update for existing one.
View Replies !
Copy A Really Huge Table
I have a really huge table that I need to copy however using the Export function in MySqlAdmin does not work because it "times out". All I really need to do is generate an SQL script that will, drop the table if it exists, create the table and its structure, and then insert the data line by line. Usually when you perform a command in MySqlAdmin through Internet Explorer the MySQL command line is displayed with the results. However, this does not happen when you export. Anyone know how to do this from the MySQL command line? In a nutshell: I need to backup a table to an SQL script file.
View Replies !
Import Huge Csv To Mysql?
I have a huge csv file ( 480mb).phpmyadmin of xampp can import maximum 12mb csv file as mysql db.so how can I import this huge csv as mysql db? the ms excel even cant open a 480mb completely
View Replies !
|
TRACKER
