Large Join Query
I am at a loss. Right now I have a PHP script that needs to run a huge join query. They query joins two tables of approximately 200,000 records. I'm running mysql 3.x so I can't use a view... at this point indexes don't seem to help. I am thinking the only way to do this is with script processing
View Complete Forum Thread with Replies
Related Forum Messages:
Help Me Refine This Large Join Query
$sql = "SELECT f.*, p.post_time, p.post_username, u.post_subject,u.post_text,v.username FROM (( " . phpbb_forums . " f LEFT JOIN " . phpbb_posts . " p ON p.post_id = f.forum_last_post_id ) LEFT JOIN " . phpbb_posts_text . " u, phpbb_users v ON u.post_id = p.post_id and ON v.user_id=p.poster_id) ORDER BY f.cat_id, f.forum_order"; Right now its select all the posts from every forum, I just want it to select the last post from every forum and the second join on phpbb_users don't seem to be working as the user_id doesn't seem to be matching on the poster_id, any suggestions? The query also doesn't work, I want phpbb_posts to left join to two tables, posts_text and posts_topics, how to I do that?
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 !
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 !
Equi-join On A Large Table?
I'm running on MySQL 5.x Let's say I have the following table of data: Code: Table: test USER_ID|CLASS_ID 12 13 14 22 24 32 34 I'm trying get all the classes that these users have in common. E.g. If i ran a query to do this I would get class_id 2 and 4, because those are the only classes that all three users have in common. I think I want to do a equi-join, something like: Code: ....
View Replies !
Optimizing Join W/ Large Table
Here is a high level description of my tables I have a table called Items items are grouped by an ID, but it is NOT unique I have an index on ID for the Items table and a table called CompletedItems completed items consolidates information about the items, and here the ID is unique. I have unique index on ID for the CompletedItems table What i need is a query that will return all item id's which have not been completed. I have this query: select distinct(items.id) from items left join (completeditems) on (items.id = completeditems.id) WHERE completeditems.state is null; this gives me the data I want except there are two problems a) this query takes a long time to run. while it is running, the completed items table is locked, which causes several other processes to block while this is happening. to give you an idea of scale, the items table has about 20 million records in it, and the completed items has about 4 million.
View Replies !
Mysql_fetch_array() And Large Query
I am running a SELECT query on a table of about 1.6 million records. The number of rows I get back is about 1.5million. SQL runs fine, in command line and php, but when I try to move data from 'resource' to an array with mysql_fetch_array() my browser stalls. So my question is, am I overflowing the buffer somewhere, and if anybody has a clue how to fix this.
View Replies !
Noob: Select Large Query
If I had to select large number of records, say 1 million from the mysql server. What problems would I be facing? MySQL connection timeout, network latency? Anyone has done a large simple mysql select query and what problem did you face? I am not doing any joins.
View Replies !
Retreiving Large Query Results In Chunks
I'm running queries with MySql 4.0.17 that return thousands of records. Because I need to present them in GUI, I returieve the results in chunks using LIMIT, for example - get first 100, then the range 100-2000 and so on. The problem is as follows: in the first chunk, MySQL uses one strategy to fetch the results, and in the following chunks - a different strategy. This means that records from the subsequent queries might have records that already appeared in the first query or that some records will be left out. For performance issues it is a problem to add a unique secondary sorting criteria (like id) to the query. Is there a clean way to force MySQL to relate to the first (initial) query result set?
View Replies !
Large Table, Slow Query Question
I have a table with ~800,000 records. I need to grab random rows from the table based on certain criteria. The problem is that average lowest subset to grab the random row is around 200k. Here is what I'm trying to do: There are 4 columns: data,n1,n2, and n3. I need to get the value of the data column based on criteria using the n1-n3 columns. The most common query is SELECT data FROM table WHERE n1 = ? The problem is that n1 can be only 1 of 5 possiblities. When the table is finished being populated there will be roughly 1.5 million records and 250k for each value of n1. Of course, I have an index on each n column. Right now with just the 800k records it can take over a second, sometimes multiple seconds to run the following in order to get a random row from that subset: SELECT COUNT(1) AS total FROM table WHERE n1 = 3; index = random number from 1 to total SELECT data FROM table WHERE n1 = 3 LIMIT index,1; How can I speed this up? I need it to take less than half a second if possible. Thank you.
View Replies !
Batch Query Or Large List Of IN() Arguments?
I'm receiving a potentially large list of productID numbers from an external data source. Each productID has a number of partID's associated with it and those partID's are stored in my database. I would like to create a list of products sorted by the number of parts each has. If I only have one table called "part" which stores a partID and its associated productID, which of the following methods would be more efficient assuming the application and DB are on the same server. Option A) SELECT productID, Count(*) counter FROM part WHERE productID IN(large list of productID's received, maybe 1 or >10000) GROUP BY productID ORDER BY counter DESC LIMIT ?, 10 Option B) Execute a batch query of prepared statements (SELECT COUNT(*) FROM part WHERE productID=?) for each product in the received product list then sort the returned results in the Java app that is making the query. Option C) ???
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 !
Query On Large Table When Implementing Paging With LIMIT?
I'm working on a paging implementation but I am stuck on a couple of things: 1) I tested with a simple query which yielded this: EXPLAIN SELECT create_date FROM threads LIMIT 0,25 id=1 select_type= SIMPLE table=threads type=index possible_keys=NULL key=create_date key_len=4 ref=NULL rows=57852 extra=Using index I thought that the LIMIT clause would limit the number of rows scanned, why is it still showing 57k rows? 2) Assuming there is a simple fix to the above, now if I want to order by create_date (SELECT create_date FROM threads ORDER BY create_date LIMIT 0,25) I understand that mysql must pull the entire table to sort the data set before applying the limit. So the explain would probably look a lot like the above. In this instance, is there a more scalable solution to implement a sorted paged result set?
View Replies !
Self Join Query
I am currently stuck on how the concept of a self-join works. At least in the format I want it. I need to create a query that will show only Active parts. Parts are being replaced by new revisions and new models constantly ....
View Replies !
Join-query
I cannot figure out how to query the db. I got 3 tables: players (player_id, name, number) stat_start (opponent_id, name_id) name_id = players.player_id stat_avb (opponent_id, name_id name_id = players.player_id Now I want to list all my players, and count how many times they have occured in stat_start and stat_avb. I guess I need to do an Inner join as i want all my players listed...
View Replies !
Using Join In A SQL Query
I have a problem which cant sort out. I have 3 tables where the primary keys in two tables also serve as keys in the third table. Table1:one two three four Table2:five six seven eight Table3:nine ten eleven one holds the same information as nine five does the same for ten Now I need to get information from the first and third tables but also need to use the second table to get the information from the third table. I can get the info from the first table grand , but its joining the 3 tables together is where I get stuck. I know its very abstact but this is the way I got the Tables.
View Replies !
Wish To Join A Query
I have this query: PHP Code: SELECT * FROM `news` ORDER BY `id` DESC LIMIT 6 I wish to also select the users id and username from the members table where the users id equals the "owner" field from the query above. As using another query apparently would be stupid?
View Replies !
Using Both JOIN And AVG In One Query
I'm looking to use both JOIN and AVG in the same query but not sure how I'm going to it. I'm VERY new to using MySQL. The ultimate goal is to take one table which counts votes for various criteria in a poll. I want to average those results together. Then I want to join that averaged table with another table and GROUP By URL (which is the column common to both tables). I've successfully used AVG with the first table and used JOIN (with a totally different) table, I'm not sure how to approach using both together.
View Replies !
Query: Inner Join Bug
What is wrong with this query? I cant get it to match the "company" field and it is throwing an error... this query works fine if i do "MATCH(date_year, market1, market2, market3, market4, market5, market6)" , but if I put "MATCH(company)" it breaks.... what am I missing here? PHP $query = "SELECT u.id , u.username , r.id , r.company , r.description , r.market1 , r.market2 , r.market3 , r.market4 , r.market5 , r.market6 , r.location , r.date_year , r.date_month , r.source , r.video , r.audio , r.pp , r.execsum , r.report_url , r.exec_url FROM user as u INNER JOIN user_reports as p ON p.user_id = u.username INNER JOIN emt_report as r ON r.id = p.report_id WHERE username = '$username' AND MATCH(company) AGAINST ('$P_search' IN BOOLEAN MODE) ORDER BY date_year DESC, date_month DESC, company ASC"; Here is my database schema: ================= user_reports ================= user_id report_id ================= user ================= id username ================= emt_report ================= id company description market1 market2 market3 market4 market5 market6 location date_year date_month source video audio pp execsum report_url exec_url
View Replies !
Help With A Query Join?
I have a query whereby I look in two tables a teams table and results table to output some data for some football scores. SELECT th.team_name AS home_team, ta.team_name AS away_team FROM results r INNER JOIN teams th ON r.team_one_id = th.team_id INNER JOIN teams ta ON r.team_two_id = ta.team_id Now I want to add a third join there on another table named reports to see if the match_date in the results table matches a match_date in the reports table SELECT th.team_name AS home_team, ta.team_name AS away_team, re.match_date FROM results r , reports re INNER JOIN teams th ON r.team_one_id = th.team_id INNER JOIN teams ta ON r.team_two_id = ta.team_id INNER JOIN reports WHERE r.match_date = re.match_date Now this works great, but I want to output everything in the results table, and not just where results.match_date = reports.match_date as there may not be a report for every result and I want to still output every result nomatter if there is a report or not?
View Replies !
Using 'if' In A 'join Query' ?
I have two tables items and food_names in mysql db items ->structure **************************************** id food_items 1 veg 2 non-veg 3 veg & non-veg **************************************** food-names ->structure **************************************** id items_id foods 1 1 vbvcb 2 2 cvbvbv 3 3 gfdgdfgd 4 3 bbvcbvcb ******************************************* if i choose veg if(id=1) I want to write a db query for both veg and veg & non-veg else if i choose non- veg I want to write a db query for both non-veg and veg & non-veg else if i choose 'veg & non-veg' then i have to dispaly all.
View Replies !
JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of student numbers for each school from two tables School,Student. select sc.name, Sum(if(student.SNO is not null, 1, 0)) nmbr, from school sc left outer join student on sc.school_id = student.school_id group by sc.school_id this runs just 0.30 sec with Mysql3.23 but 30 Sec with Mysql 4.0.13.
View Replies !
Query Involving JOIN
I have two tables populated during the use of an application to log user events and application states. They are named "EventTable" and "StateTable" and the structures follow: EventTable: ID EventTimeStep EventID -- ------------- --------- 1 5 E1 2 22 E2 3 56 E3 StateTable ID StateTimeStep StateID -- ------------- ------- 1 1 S1 2 39 S2 I want to perform a query that reports the StateID of the application at the time that each event was logged to the EventTable. The desired output is: ID TimeStep EventID StateID -- -------- ------- ------- 1 5 E1 S1 2 22 E2 S1 3 56 E3 S2 I have tried to create a query with an INNER JOIN where the value for the StateID output field comes from the last row in the StateTable WHERE StateTable.TimeStep <= EventTable.TimeStep and where I use a GROUP_BY EventTable.ID to merge the following rows from the join: 3 56 E3 S1 3 56 E3 S2 However, the closest I can get is a query that gives me the wrong state when applying the GROUP BY clause 3 56 E3 S1 I also think that the queries I have written is slow and inefficient. Is there a better way to perform this query or is my database design fatally flawed?
View Replies !
Join Query By Date
I've these tables: - PERSON (id,name,age,....) - EXAM (id,date,note,exam_type,id_user) One person has 0 or more exams. I have to do this report: name, age, date, note, exam_type This looks easy, but I have to list for each person, his/her LAST EXAM GIVEN. I mean, only the last exam must be shown for each person, and well, if person has no exam yet... to show blank (if possible)
View Replies !
MySQL JOIN Query
I am a recreational MySQL user, and I tend to make some non-optimal queries. In my latest project I made some LEFT JOIN queries that were extremely slow. I made two changes that made the queries almost instant. 1) indexed the columns that the joins were working on 2) optimized the tables being joined (using phpMyAdmin > Operations) All I can say is WOW!!! I had no idea that the resulting difference in speed would be so great. After I did step (1) it added a tremendous amount of speed, and after I did step (2) the queries responed almost immediately. So if you are running slow with JOINS, give indexing and optimization a try.
View Replies !
Left Join Query
I have this query SELECT user.user_id, email, password, full_name, company_name, first_login_date, last_login_date, first_draft_date, last_draft_date, first_submit_date, last_submit_date, rowc.* FROM user LEFT JOIN rowc ON rowc.user_id = user.user_id WHERE user.user_type = 'USER' ORDER BY user.email Which returns all records in user table plus rowc records if there are matches in the user id. but user.user_id is null when there are no matching recordings in the rowc. Isn't left join meant to pull all the records in the user table? What am I doing that is wrong here?
View Replies !
Table Join Query
I'm trying to join multiple database tables and having a little trouble with it, hope someone can help: There are 3 tables with columns: id, user, date, time, action I'm trying to get user, date, time and action from all 3 tables where the user equals "anyuser" and sort the result by date DESC, time DESC.
View Replies !
Run Outer Join Query
I am trying to run following query in mysql. select a.name, a.record, b.data from A a, B b where a.name*=b.name and b.date='2006-07-28' How can I do this in mysql? I looked up LEFT JOIN but still can't figure it out.
View Replies !
Multiple Join Query
I have a database with quite a lot of tables all with foreign keys of each other. One of the tables has a one to many relationship (it being the one) with two tables, but it only uses one of them for any one record. Which one is used depends on a field in the main table. This essentially defines three different types of the same item, which has mostly the same fields, but two of them also require additional, multiple record (hence the one to many) information. So the table has a field 'type' which takes the value 1 (in which case no other table is referenced), 2, (in which case the first other table is used) and 3 (in which the other table is used). What I would ideally like to do is have one SQL query which returns information (from the main table) about any record which applies to the day (as it is part of a calendar system) specified in the query. The problem is that to decide if the record is relivent, the query must look through at most two of the three tables. I doubt that there is a way to specify in an SQL query to join to a table depending on the field of the first, and to join all three and perform the query could produce unexpected results if there were for any reason records in the third which referred to the first table. I could do this with several queries, but it would probably be more efficient to do it by one query. Would I be able to do an outer join on this?
View Replies !
Delayed JOIN Query
I have tried to join 3 tables via 2 different column entries. the query is very slow. I suppose it has to do with the design of the tables. I read that for JOIN it is important to define primary keys. My problem is, that in one of the tables some entries of the column (which is used for joining) are empty. When I try to make a primary key on the table, phpmyadmin says that there are double entries in the column... I have tryed to declare the entries as NULL, but it still says that there are double entries (by the way, the other entries are not double, I have checked). For a better understanding, here my command: ....
View Replies !
Help With Join Query Mysql 4.0
As I understand subqueries do not work in version 4.0 Here is what i am trying to do; select a, b from table1 where table1.b != (select distinct c from table2); Other words: table1.b and table2.c have values I want to match on I run select a, b from table1; subtract all out using select distinct c from table2; and leave me with a and b from table 1 that have no corresponding value of c in table 2
View Replies !
SQL Query JOIN And LIKE Clause
I need to run a query which uses a like query like the one below, no problem: SELECT * FROM footballers WHERE sname LIKE '%$criteria%' however, if I want to join two tables and still use the LIKE clause how would the syntax look?? My guess below was incorrect. any help would be appreciated. thank you WHERE sname LIKE '%$criteria%' AND (footballers.footballerID = PremTeams.footballerID)
View Replies !
Join Query Chaos
I cannot get this join to give me what I need. Here is my setup. I have 3 tables: courses, modules, sessions. courses has this structure:
View Replies !
Join Query Problem
student ------- id name 1 name1 2 name2 3 name3 Class ----- cls_id stu_id marks 1 1 100 1 2 300 5 1 400 5 3 500 I want to show only those student that are not class Ƈ' So I wrote select s.id,s.name,c.marks from student s,class c where s.id=c.stu_id and c.cls_id<>Ƈ' but it gives 1 name1 400 3 name3 500 but name1 is already in class Ƈ' . I desire the output only 5 3 500
View Replies !
Need Query To Join 2 Tables Through A 3rd
I have a table called client (primary key = clientID), another called clientFamily (pk = clientFamilyID) and a table that joins those two called client2clientFamily. The latter table only has two rows: clientID and clientFamilyID (no pk). On the client table, say the client has 3 children and 2 siblings. Those would go on the clientFamily table. The part I don't know how to do is how to assign those 3 kids and 2 sibs to that particular client. I know I do it via the client2clientFamily table but I don't know the query. I'm also not sure I'm thinking of this right. The fields on clientFamily start off like: clientFamilyID spouseFirstName spouseMiddleName spouseLastName spouseOccupation motherFirstName motherLastName motherOccupation fatherFirstName fatherLastName fatherOccupation I think that part's ok, but if I add: childFirstName childLastName childBirthday childSex and sibFirstName sibLastName sibOccupation how are, for example, a child's birthday and sex are going to be linked to the correct child? And a sib's occupation linked to the correct sib? Now I'm wondering if I need a separate table for clientChildren (and a joining table client2clientChildren) and another one for clientSibs (& client2clientSibs) instead of putting everything in clientFamily. Ugh.
View Replies !
Join Query Problem... Help!
I have two tables: STORES ------- id name ORDERS ------- id total cost store_id date_ordered I want to select ALL stores and their total sales for a date range. Here's my query: SELECT stores.id, stores.name, SUM(orders.total), SUM(orders.cost), COUNT(orders.id) FROM stores LEFT JOIN orders ON stores.id = orders.store_id WHERE orders.date_ordered > 'xxxx-xx-xx xx:xx:xx' GROUP BY stores.id This works, except that it only selects stores that have orders. If I leave out the WHERE clause, it then selects ALL stores, even without orders, and populates the sums with "null" That is what I want - if the stores don't have any orders, the results should simply return null or 0. However it seems that stores without orders are simply not being returned in the results at all.
View Replies !
Left Outer Join Query
I have noticed when I do a Left Outer Join in short form that many rows become missing as result of null values. i.e Left Outer Join (t1,t2,t3) ON (....) T3 being table with null values in It seems to be all to hard when I log a bug.
View Replies !
Join Query Result Difference Between 3.23.49 And 4.0.13
I have what seems to me a very common operation i'm performing. I need to find the balance on an invoice. i was not having any problems until the production server was upgraded to mysql v4.0.13-standard for pc-linux. There must be a better way to query for this information than the method i'm using, since the result with v4.0 is not what I expected, nor what I received with v3.23. I'm including sample data and queries with my results.....
View Replies !
Select With Join Query Question
I'm trying to write a select query that involves 2 tables. One table (Submissions) has a one to many relationship with the other table (Jobs). I'm trying to find all the records in Jobs that do NOT have a corresponding record in Submissions with a status of 1. The problem I'm having is that when there is more than one record in Submissions that match a record in Jobs and one Submissions record has a status of 1 and one record doesn't, my query matches the one with status !=3D 1 and returns the record for Jobs (even though it has a record in Submissions with a status of 1 also). I've tried a variety of queries including left outer joins and more simple join relationships. I'm using MySQL 3.23.47 on Windows. Here's an example query: select j.job_id from jobs j left outer join submissions s on (j.job_id = =3D s.job_id) where s.status_id !=3D 1 group by j.job_id Any help is greatly appreciated.
View Replies !
DELETE Query With Left Join?
I want to delete items from a table that are determined with a left join. using DELETE ( SELECT * FROM item_colors LEFT JOIN items ON item_colors.item_id = items.item_id WHERE items.group_id =3 ) FROM item_colors does not work. how would be the right syntax? The SELECT part returns the items I want to delete from item_colors correctly.
View Replies !
Union Query To Join 4 Tables
I am trying to make a union query to join 4 tables. I have reduced the tables to just 5 fields and made sure that the field types and names are the same. I keep getting an error message ODBC call failed. I have tried various combinations of the tables and find that I can use any 2 of them but as soon as a third is included in the statement the query fails. Is there a limit on the number of tables in a union query. The union query looks like this in its simplest form when I have made sure that the fields names, types and position match but I have also tried by specifically naming the fields in the same order for each table select * from T1 union select * from T2 union select * from T3 UNION select * from T4;
View Replies !
Table Join Query Error
The query’s work fine when I try them separate. $query = "SELECT id,loginName,car,yearmod,city FROM Member UNION SELECT imgName1 FROM Gallery"; $result = mysql_query($query) or die ("DOH!"); I have tried with and without the "(" ")" phpinfo says my MySQL Client API version is 4.0.21 so that shouldn’t be a problem, right?
View Replies !
Foreign Key Speeds Up INNER JOIN Query?
I've read the foreign key topic on the mysql manual but it says nothing if foreign keys optimize the queries with INNER JOIN on the foreign key. I've read that indexes in general speed it up, but is it valid too for foreign index? I am often linking a medium sized table to a huge sized table via INNER JOIN and I wonder if setting up foreign keys would speed up the INNER JOIN. And is this effect valid to all engines? If not, to which ones is it valid?
View Replies !
Simple JOIN Query Issues
What I want to do is have multiple tables interact with each other. payments TABLE id | paidfrom | paidto | paidmethod 1 | 2 | 1 | 2 2 | 3 | 1 | 3 users TABLE userid | username 1 | Jake 2 | Todd 3 | Spencer methods TABLE methodid | methodstring 1 | Cash 2 | Check 3 | Credit Card
View Replies !
Outer Join Count Query
I want to create an outer join along with usage of group by & count... So, I want the rows with count=0 to be part of the result... Select a.area, count(l.id) as no_listing from area a left join listing l on a.area=l.area group by a.area order by a.area; Note that an area can have multiple listings... Now, this query returns me only the areas with some positive count of listings, in spite of using a left join...
View Replies !
|
TRACKER
