Large DB Should Use Multiple Tables?
I have a social networking site similar to myspace, currently my table which links who is who's friend on my site is 2 weeks old and is at 300,000 rows but is only at 15mb in size, this is the most accessed table of all my tables.
My question for performance, as this table grows should it eventually make a new table? If so here is my current select code
select userid,friendid,status from friend_friend where userid='$temp[auto_id]' and status='1'
Could someone give me an example of how o select this from multiple tables if I add more for this table in the future?
View Complete Forum Thread with Replies
Related Forum Messages:
Selecting From Multiple Large Tables Quickly
My skills with MySQL typically end at "SELECT * FROM table WHERE stuff", so I've had a lot of trouble with optimizing this query. I've tried doing multiple select statements for this, but that usually comes out with an average execution time of 99s. This method has a execution time of 6s still, so I'd like to get it down. I have 4 important tables here (I'm only posting the columns which I use): `auth` UID mediumint(8) unsigned PRI NULL auto_increment username varchar(32) MUL clan_id mediumint(8) unsigned MUL 0 clan_abbrev varchar(7) MUL `clans` ID mediumint(8) unsigned PRI NULL auto_increment Abbrev varchar(7) MUL Name varchar(64) icon_id smallint(5) unsigned 0 `stats` UID int(10) unsigned PRI 0 kills mediumint(9) NULL `usernames` UID int(10) unsigned 0 ID int(10) unsigned PRI NULL auto_increment Name varchar(32) Uses mediumint(9) 0 `auth`, `stats`, and `usernames` are linked by `UID`. `clans` is linked to a row in `auth` based on it's `clan_id`. I need to search the `usernames` table for a `Name` which matches a certain text (I use "storm" in my example below). I need to return a result with matches to that search, with each row containing:The `UID` which links the tables together for that user.The matching `Name` from `usernames`The matching `username` from `auth`The `Uses` from `usernames`.The `clan_id` from `auth`.The `Abbrev` from `clans`.The `Name` from `clans`.The `icon_id` from `clans`.The `kills` from `stats`. The result should be ordered by `Uses` (from `usernames`) -- highest to lowest. The tricky part is that some users may have Ɔ' as their `clan_id` in the `auth` table, in which case the clan id, abbrev, name, and icon_id should all be blank (0 or '' based on the type). Here's what I managed to hammer out with my limited knowledge of SQL: ( SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , auth.clan_id AS `clan_id` , clans.Abbrev AS `clan_abbrev` , clans.Name AS `clan_name` , clans.icon_id AS `clan_icon` , stats.kills AS `kills` FROM `usernames` , `auth` , `clans` , `stats` WHERE usernames.Name LIKE '%storm%' AND auth.UID = usernames.UID AND clans.ID = auth.clan_id AND stats.UID = auth.UID ) UNION ( SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , 0 AS `clan_id` , '' AS `clan_abbrev` , '' AS `clan_name` , 0 AS `clan_icon` , stats.kills AS `kills` FROM `usernames` , `auth` , `stats` WHERE usernames.Name LIKE '%storm%' AND auth.UID = usernames.UID AND auth.clan_id =0 AND stats.UID = auth.UID ) ORDER BY `Uses` DESC It works great, but the average query takes around 6s (these tables have several thousand entries in each).
View Replies !
Large Tables
So i've got a table with 15 million rows. id is the primary key SELECT COUNT(id) FROM listings WHERE id != 0; Takes about a minute to load... which is insanely slow.. anyone know what could be causing this?
View Replies !
Very Large Tables
I am looking at using mysql to store some particle physics data. So the idea is (for example) I would have a table with a list of events, then a table with a list of particle types (one event can have many particles) and so on. The problem is that quite often I want to calculate something for every single event, but this table might run into 10s of millions of events and many GB. Obviously it would be mad to load the whole result set into memory then iterate over it. My question is: is there any way to have the db push results to the users one (or a few) at a time in an effiecent way? I was thinking of something like an iterator that pulls the data from the disk one (or few) at a time. An alternative (but much less useful) method might be to hand the function I want to calculate to mysql, for example: select myfunc(x,y,z,a,b,c) from events where energy>3; But as far as I know there is no way of defining your own functions in SQL
View Replies !
Large Table Or Many Tables?
I posted a similar question about 6~12 months ago with no definate answer, so I will give it another go, as the choice has popped up again in my programming life: If the number of entries is the same is it more efficient (better) to have a single table with many columns, or many tables with few columns? The obvious answer is a single table, because there is less closing and opening tables, but the reason why I ask is what about searching through a lot of columns being even more inefficient? The situation is I need to record data for every week of the year. Say ~50 columns per week --> 2600 columns. Or, do I split it so it's 52 tables with 50 columns each? In each of these options the user (or user ID) will be the primary key. The seemingly pointless other option is to insert new columns with every new user, and have the week-item as the primary key?
View Replies !
Large Number Of Tables
I would like to ask whether it is feasible (or whether it makes sense) to run a PHP script that creates a number of tables for each user. Basically, this script allows users to sign up for a free service, and for each user that signs up, the script creates 9 tables in the database to contain that user's details. Is this a sensible way of containing user data, and what are the inherent problems this may cause especially when say are 1000 users signup?
View Replies !
Managing Large Tables
I recently inherited a database (version 5.0.18) that has 1 table constantly growing out of hand between 10GB -30GB, therefore making is difficult/impossible to query. Also, the Archive Storage Engine is not installed. Currently, the table is manually renamed (i.e. tablename_daterange) and a new table created. ....
View Replies !
Join Tables On A Large Database 200 Meg
i am trying to work out the most efficient way to list say multipl= e categories of entries, the database is quite large about 200 meg.=20 I would like to know if using join tables is more efficient than storing th= e keys in a varchar field then within the second loop doing a where in (1,2= ,3,4) where the 1,2,3,4 are the stored category keys in the varchar field = rather than a where in (1), where 1 is the pirmaryID of the entry for insta= nce ?
View Replies !
Optimizing Mysql For Large Tables
I have been working with Mysql for about 5 years - mainly in LAMP shops. The tables have been between 20-100 thousand records size. Now I have a project where the tables are in the millions of records. This is very new to me and I am noticing that my queries are really slowwwwww! What are the options that I have to speed my queries on the mysql side with regards to the my.cnf file. I have a fair understanding of sql optimization and I understand explain. I just want to see if there is something that I can do with the server also.
View Replies !
Slow Join On Large Tables
I have two tables: D (500,000 recs), and DL (2,500,000 recs) D has a PK and an index on HLQ. DL has a PK and an index on ID. The following SQL: SELECT HLQ as "HLQ", count(*) FROM D, DL WHERE D.DLID=DL.ID GROUP BY HLQ produces the following explain: tabletypepossible_keyskeykey_lenrefrowsExtra DALL500000Using where; Using temporary; Using filesort DLeq_refIDID4D.DLID1Using index The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00). key_buffer=512M table_cache=256 sort_buffer=16M read_buffer_size=16M It appeasrs that the 'Using filesort' on table D is due to the Group By clause and is the problem. I have an index on HLQ. Is there any way to get MySQL to use it?
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 !
Large Tables, Very Slow Deletes.
I've been using MySQL for a while now and are starting to run into limitations. Either my own, or something else. I have this really large table, it stores images, it has a mediumblob field, an unsigned id integer field and a timestamp field. This table contains up to a few million rows and is constantly filled real time. It grows up to sizes between 50 and 100 gb and in the future probably even larger. This data is not supposed to be stored for all time, therefor when a certain treshhold is reached, the oldest 5000 rows are deleted to make room for newer rows. This is where my problem kicks in. Whenever I try to delete those rows, it can take for ever to complete. 500 rows I can delete without problem, 2500 becomes slow, 5000 rows takes several seconds and 10.000 rows or more makes it looks like things are frozen. Now the temporary solution I have is to delete 500 rows at time, but this means I must do that every 10 seconds just to maintain status quo. I would much rather check every 5 minutes or so, and if needed delete 15.000 rows in one go. I use MyISAM tables and tried setting the key size to 128M, no luck. I run this on a dual Opteron system with a GB of memory and WinXP-SP2 Proffesional.
View Replies !
Large Unused Tables Vs Performance
I've been working on a website with a rather large a database. One of the about 100 tables in there was about 1/4 of the total database size. The table had no use anymore at the time I worked the website, so it was just sitting there, doing absolutely nothing. Obviously to spare diskspace we deleted the table. But the question I have now, can unused large tables affect the perfomance of Mysql? I've searched a bit around, but honestly I couldn't find a clear answer about it right away (maybe I haven't searched enough either :) )
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 !
Large Number Of Tables And Columns
i'm developing an online portal, which my MYSQL database contains 100 tables.. my doubt is if any table contains 30-40 columns will it become a problem? like my html front end form has 40 fields for data insertion. and i'm not maintaing any duplicate data if my DB contains 100-150 tables will it create any problems? as i'm inserting data from EXCEL via PHP to MYSQL, please suggest me whether a healthy MYSQL table is dependent or independent on NO:OF COLUMNS(FIELDS) or not?
View Replies !
Speeding Up Large MySQL MyISAM Tables
I'm somewhat new to MySql. I've been using it for a while, but pretty much out of the box setup, and am starting to suffer heavily with my larger tables. I have a table with 5,000,000+ rows that I have to search and do joins on. Although I have an index set up for it, the joined select will still take some 400+ seconds to return, which is obviously unacceptable. This is due to enormous HD access. Perhaps someone can help me with indexes here; I was under the impression that the index for the tables are cached in memory, and therefore permitted "instant" searchability, without having to retrieve data from each of the rows of the DB. Is there a startup parameter, or something in the mysql.ini file, that must be set to allow for this? I have the index configured properly, and have made sure that the query uses there parameters in the where clause in the same order that they appear in the index. Code:
View Replies !
Speed Of InnoDB DELETEs On Large Tables
I am finding delete queries on large InnoDB tables very slow - are there ways to speed this up? I have a table with about 100 million rows: I am trying to delete just a few of these rows (the following select takes a couple of seconds): [color=blue] > SELECT count(*)[/color] -> FROM UserSnap -> WHERE LogDate<now() - INTERVAL 750 DAY; +----------+ | count(*) | +----------+ | 308969 | +----------+ [color=blue] > DELETE FROM UserSnap WHERE LogDate<now() - INTERVAL 750 DAY;[/color] That delete query takes hours to run. The structure of the table is: [color=blue] > desc UserSnap;[/color] +----------+-------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------------------+-------+ | LogDate | datetime | | PRI | 0000-00-00 00:00:00 | | | Period | tinyint(4) | | PRI | 0 | | | UserName | varchar(50) | | PRI | | | | RateType | varchar(50) | | PRI | default | | | Rate | float | YES | | NULL | | +----------+-------------+------+-----+---------------------+-------+ Any suggestions on why this is slow, and what to do about it?
View Replies !
Loading Large Tables From Files (wikipedia)
I'm an Oracle DBA and new to MySQL. I was trying to load a 5.6 GB xml file onto mysql database using mediawiki tools. The performance decreased gradually and crashed at ~60% completion. Now I'm planning to use xml2sql tools to convert the dumps to txt format. Then I'm planning to load this using mysqlimport. I would like to run this load faster, and on Oracle, I would use the IMP parameters such as INDEXES=n and so on. What are the recommended steps when using mysql? Should I target any of the system variables? I have a 4G ram on the server and can use all of it for the load. Any pointers?
View Replies !
Time Out Message When Query Large Tables
I'm trying to get data from 6 large tables but the volume of data in each table is too large and even select * from one of them make the system stop. I have afew questions: 1-what can I do to avoid the system stop or time out message? 2- To use several tabels infomation should I use 'View' command or can I use other methods? 3-I need to create a new table and insert the result from query in it. If I use the "view" can I insert the result of the view in a table? (I use postgresql).
View Replies !
Joins With Multiple Tables And Multiple Rows
I'm making a good ol' forum, and i have three tables, users, threads and posts. when i query my threads table with a join, i need to access the users table twice to get the username of the first poster and last poster. But how? I can only figure out how to get one or the other. Is my design bad? eg SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic, username FROM DiscussionThreads, users WHERE DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT 10 .
View Replies !
Select Multiple Keywords From Multiple Tables
am trying to do. I have a database called "members" that contains a table called "Reviews". This table has fields S1, S2, S3, S4, etc. that I want to search for the words ad, advertisement, and promotion. Here is the syntax I came up with that doesn't work: SELECT * FROM `REVIEWS` WHERE S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, XTRA='ad, advertisement, promotion' It would be perfectly fine, if easier, to search all fields in the table.
View Replies !
Multiple Batabases Vs. Multiple Tables
I am about migrate from an old program database to MySql (Running under RH LINUX)and I'm wondering which is the best option to do : I currently have one file for each of my modules, (I'm using filepro plus) , so like CUSTOMER, WARRANTY, ZIPCODES, INV, etc... like 2,000 files each with their own fields. 1) Should I create multiple databases on MySQL 2) Should I create a single database on MySql and with multiple tables ? Which one of these options is the best and more safer ?
View Replies !
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 !
Selecting From Multiple Tables (20 Tables)
I am running MySQL 4.1.20, but use a PHP front end mostly, so I'm not too familiar with the mysql command line options. I have a database with 20 tables, which are data stamped. I'd like to do a select or delete statement that uses multiple tables. I know I can do select * from t1, t2, t3 where field= 'value' But since the table names change, is there a way to do some kind of wildcard expression for table names? Something like.. select * from t* where field= 'value'
View Replies !
Multiple Tables Better?
i have a database with 10 tables, each table has around 9,000 record, is it faster and better to put all records in ONE HUGE TABLE and define each table with a column ?? i mean i have table for games, softwares, movies, etc ... so i put ALL the records in one table and make a column named "type" and i insert in each column the corresponding value.
View Replies !
Multiple Tables DB
Does anyone know of a practical tutorial (with code) that uses multiple related tables. I want to learn how to update all related data when doing an insert. e.g.: if I add a user to users table and each user gets a car by defualt, how do I insert a car to cars table and keep a reference to the user (owner of this car).
View Replies !
Multiple Tables Or Just One?
I want to store a large set of variables into a database that are all characteristics of the users of my site. They can register by filling out their name/ adres/ city / day of birth etc. Besides that they can set a number of preferences (e.g. 'available on monday', 'available on tuesday', 'category 1', 'category 2' etc.) that can be either '1' or '0'. Furthermore I want to store some variables that tell me if the supplied email adress is validated, what the ip-adress of the registrant is and on what date he registered. Using MySql, what method is preferred to store the data: 1) one table with +/- 40 columns (note that there a lot of values are booleans). 2) 3 tables, e.g. users, preferences, other with 5 to 20 columns each. The thing is that I want to be able to perform searches that include all types of characteristics. E.g. I want to select all users that are: 'male, older that 18, are available on monday, preffer category 2 and where the email adress is validated'
View Replies !
Delete Against Multiple Tables
SELECT * from clicks, urls WHERE clicks.url=urls.url AND urls.description IS NULL The clicks table has 31 instances of url that match 5 instances of url in the urls table.When I run the following I get a syntax error DELETE * from clicks, urls WHERE clicks.url=urls.url AND urls.description IS NULL
View Replies !
Querying Multiple Tables
I need to query multiple (15) tables to build a data entry screen. Is better to create 15 separate SELECT statements and query each table independently or create 1 large SELECT and query one time?
View Replies !
Query Across Multiple Tables?
I am new to mysql and need help on how to join tables. I have a database which contains 4 tables, the main table contains information by date order and the other 3 contain data also in date order. So I need to write a query that retrieves all the information for one record, lets say I want to query on the main table any entry that is for the 2004-01-06 and this date is also in a field called 'Date' in the other tables, how do I go about pulling back all the info for that entry across all the tables? I have tried doing this: WHERE main.Date = table2.Date AND table3.Date AND table4.date however it returns loads of data when it should only pull back the one entry.
View Replies !
Choosing From Multiple Tables
I have a table of users where some users have a "commander" who will benefit from that users stats. This is updated daily where a users benefits are calculated and updated via a cronjob. I would like to update users, and if they have commanders, update them as well in the same MySQL statement. So the only way I can do it no is like this: Expand|Select|Wrap|Line Numbers
View Replies !
COUNT(*) Across Multiple Tables
I have a query to the effect of: (SELECT COUNT(*) FROM tbl_a WHERE id="1234" ...) UNION ALL (SELECT COUNT(*) FROM tbl_b WHERE id="1234" ...) This gives me two numbers--the counts of the relevant rows in each table that share the id. However, I would like to return the sum total of rows in *both* tables. Can this be done in one query with a SUM of some sort? I'm using v4.0.14-standard. I've done a quick search about the forum and couldn't quite find a solution to this.
View Replies !
Multiple Tables Or One Table
I'm designing my database right now and I've come to a point where I have to make a decision that will be very important to the future use of this database: Using multiple tables or one main table on semi-related products... I have found a few topics on this forum that helped me, but still didn't get me to decide one way or another... The question is: where do you draw the line on similarities in the products? Let me explain... The products I want in the table (or tables) have mostly the same columns (id, suggested price, name, ...) but all of them also have different columns (DVD's have bonus material, CD's have tracks, ...). Understand? If it's not clear I can explain a little better if you ask (but I think I made myself clear).
View Replies !
Get Data From Multiple Tables
I have three tables: PRICES_2006, PRICES_2007 and PRICES_2008 I want to find all the rows between certain dates and then order them by date (TIMESTAMP). My current code looks like this: .......
View Replies !
Fulltext Across Multiple Tables
Can someone provide an example on how to match against two fulltext indexes? We have two tables, both with two fields set to fulltext, but we need to join these tables and search the indexes.
View Replies !
MAX Question For Multiple Tables
I'm trying to optimize one of my reports, and came across a situation I've run into on more than one occasion. I thought I should get some other opinions on this. Lets say we have a table product, that contains id and product_name fields. Then we have several other sets tables: receive receive_detail transfer_detail sale sale_detail destroy destroy_detail The receive,transfer,sale, and destroy tables contain the location_id and date for each action, and the detail tables contain the product_id and quantity. Now lets suppose I want to write a report showing the maximum receive, transfer, sale, and destroy of each product at a specified location. I can go about this a few ways: 1) Run 1 query to get all active products for a location, then run 1 query to get the MAX receive for each product, 1 query to get the MAX transfer for each product, etc 2) RUN 1 query to get all active products for a location, then run 1 query to get the MAX for each table for that product. 3) Run 1 query to get the MAX for the receive table for all products, then run 1 query to get the MAX for the transfer table for all products... etc 4) RUN 1 giant query, combining all tables at once, getting the MAX from each table. Currently, the report is done using #1. I was assuming #4 is the correct way, but as I am building my query, it takes longer and longer to run the query the more tables I join, so I am not sure if that is the correct way, either. However, it could be a server configuration problem, or an index optimization problem, but I want to get the theory correct before I go investigating that.
View Replies !
Multiple Mysql Tables
if you take a look at this site , all the info on the page is divided into sections, seperated by blue bars. How could all this info be stored in mysql - would it work to put it all in one massive table, or would there be too much info in the table, meaning that queries would be slow?what would be the best design to hold all this info?
View Replies !
One Table Or Multiple Tables
I'm working on a project that would use huge volume of MySQL-service. So, I ask You give me an advice on how to implement it out better. The question is, What is the best solution in matter of reliability and speed: to use one table where the users can share the data among themselves or to use multiple tables for each user in which case one user that is permitted to share a data with another (on MySQL-server control level) can simply copy his data to the table of that user. But in this letter case I get thousands of those table! May there is a more fair solution. In any way, please share how would professional would make this app.
View Replies !
Query On Multiple Tables
Tables: patient ------- patientID | name scans ------ scanID | scantype | scandate | patientID What I want to do is create a table that has a list of patient names and their latest scandate and scantype I can't quite figure out the syntax
View Replies !
Searching Multiple Tables
How would i perform a search on multiple tables in my database.my form field is called search and all of the fields in the tables i want to search are called name
View Replies !
Searching Multiple Tables Using LIKE
I have a query that currently searches for the exact keyword that is entered, the query is... $query = "SELECT videos.*, users.* FROM videos LEFT JOIN users ON users.user_id=videos.user_id WHERE MATCH (videos.video_title, videos.video_description, users.user_username) AGAINST ('$search_keyword' IN BOOLEAN MODE) AND videos.video_status='processed' AND videos.is_approved=TRUE ORDER BY MATCH (videos.video_title, videos.video_description, users.user_username) AGAINST ('$search_keyword' IN BOOLEAN MODE) DESC LIMIT $offset, $rows "; I would like to modify it so that my search will contain results that are similiar or like the keyword. An example. Searching for westexasman currently returns ONLY westexasman. That is fine, but what happens if a user types in westTexasman? I would like the search result to inlcude as many possibilities as possible.
View Replies !
Sum Two Tables With Multiple JOINS
i'm trying to get more efficient at SQL rather then creating tons of PHP work arounds to get the job done. I have two queries here for example. One query shows the Budget Cost/Hours of a Work Order, the other shows the RFI(aka Change Orders) for a Work Order. I want to sum the budget and the rfi in one query and get Total Hours/Total Cost. Here are the two queries ...
View Replies !
Data From Multiple Tables
I am working on a website about sea shells and basically I have 3 tables: - shell_tb; (table of shells) - site_tb; (sites where they can be found) - instrmt_tb; (instruments used for it) As you may already have guessed there are two columns in shell_tb (site and inst) which may use more than one item from the site_tb and instrmt_tb. 1st. How do I configure the columns in shell_tb in order to accept more than one value? (I tried: CREATE TABLE shell_tb ( shell_id INT(1) NOT NULL AUTO_INCREMENT, shell_name CHAR(40) NOT NULL, site INT(20) NOT NULL, inst INT(20) NOT NULL, PRIMARY KEY (shell_id) ) TYPE=MyISAM; but no luck at all when inserting more than on value into inst column.) 2nd. How do I retrieve the info from there?
View Replies !
Select From Multiple Tables
I have multiple tables named like this wpctchie, wpctchee, wpcbasfr, wpcbasth etc. The idea is that admin will be able to search all tables beginning "wpc" (all tables), superusers will be a able to browse tables beginning "wpctch" ("wpctchie" and "wpctchee") and users will be able to search only one table e.g "wpctchie" Is it possible to do a search such as SELECT * FROM wpc% (for admin) SELECT * FROM wpctch% (superusers) SELECT * FROM wpctchie (well this just works anyway!) Does that make sense what i am trying to do?
View Replies !
|
TRACKER
