jworisek

I think I might just take the easy route and do the incoming sum in the main query and then have a query after that to look up the information for the shipped sum. Just adding those indexes alone has sped up the time it takes to print each line of the report by 40xor more so there really won't be too much performance hit by breaking up the query. My benchmark report just ran in .646 where it was over 20 seconds before.

I tried the top one and it fixes the problem with checking the entire table for committed_new, unfortunately it doesnt use keys for the incoming_data or shipping_data tables so it scans the entire tables and uses temporary tables. One quick fix that I can do is to divide the incoming sum by the number of shipments to account for the additional summations.

One large problem was that I was missing an important index in incoming_data (its was checking over 34,000 rows just from that one table). Now that I added a couple more indexes, even my old script with a bunch of seperate queries runs in under a second. This was one of those things that I never would have realized without being able to check the explain selects through php. Also, those links that you provided are just what I was looking for. I just needed to see some examples with explanation to get a feel for it. Again, thanks for the help! :) One last quick question though... I have noticed that the "committed_new" table is still searching every single row and using a temporary table. I get this when I do the explain: id:1 select_type: SIMPLE table: CN type: ALL possible_keys: current_commit_date key: key:len: ref: rows 669 Extra: Using where; Using Temporary; Using filesort with the following query [code] SELECT O.order_id, sum(required_pieces)-IF(ISNULL(sum(good_pieces+chipped_pieces+reject_pieces)),0,sum(good_pieces+chipped_pieces+reject_pieces)) as pcs_remain from committed_new CN USE INDEX (current_commit_date) INNER JOIN orders O ON (O.order_id=CN.order_id) INNER JOIN line_info LI ON (LI.order_id=O.order_id) INNER JOIN incoming_data ID ON (ID.line_info_id=LI.line_info_id) LEFT JOIN shipping S ON (S.order_id=O.order_id) LEFT JOIN shipping_data SD ON (SD.shipping_id=S.shipping_id) where CN.current_commit_date>=DATE_SUB(curdate(), INTERVAL 14 DAY) group by order_id having pcs_remain>0[/code] Another problem is that it sums up the incoming multiple times... if there has been 3 shipments then the incoming is summed up 3 times... but it runs fast ;) I'll give that last query a check with the subselects to pull the sums out.

First off, thanks for all the help on this. Unfortunately I might not be able to get to this until tomorrow morning... One thing that I was thinking that might also be slowing it down some is having to check the shipped table when shipments have not been made for that order... Or maybe not. Just a thought. Do you know any good references for writing and optimizing multi-table queries? Most of my work before this has been with single tables with many columns (based on more of a tree structure of files with each customer having their own DB and all of our products/services as tables. Now I've moved it to a relational table system and am starting to get into queries that involve possibly 10 tables and I really don't have experience in getting them to run fast. I'll try that explain select sample code out though, that could be a big help!

well, I did get that query to run ( Ihaven't verified if it is accurately pulling the right rows yet though) but the problem I have is that it is incredibly slow. The first time its run it will time out (30seconds) and then next time it will be instantaneous (I'm assuming most of it was cached). Is there some option that I need to increase in my my.ini file to add more memory? I suppose it could be tables joined in a poor way (performance wise). I know that you can run an explain select query from command line, but on windows I can't get the entire query in before it cuts off. Is there a way to echo back the results of the explain select using PHP or another way to do it from command line? The exact query I am using is posted below... I had simplified it greatly hoping to not have to write an essay on all that was involved ;) [code]$sql="SELECT O.order_id, sum(required_pieces)-sum(IFNULL(good_pieces+chipped_pieces+reject_pieces,0)) as pcs_remain from committed_new CN INNER JOIN orders O ON (O.order_id=CN.order_id) INNER JOIN line_info LI ON (LI.order_id=O.order_id) INNER JOIN incoming_data ID ON (ID.line_info_id=LI.line_info_id) LEFT JOIN shipping S ON (S.order_id=O.order_id) LEFT JOIN shipping_data SD ON (SD.shipping_id=S.shipping_id) where CN.current_commit_date>='2006-02-20' group by order_id having pcs_remain>0";[/code] Committed_new contains the date I am checking for (indexed) Line_info contains specific data explaining the line items Shipping contains info on the non-line item specific shipping info Everything except the shipping and shipping_data tables are guaranteed to have data in them.

personally, I would use integers as the unique ID's because it will be that much faster to find matches. [!--quoteo(post=348074:date=Feb 21 2006, 05:23 PM:name=DrAwesomePhD)--][div class=\'quotetop\']QUOTE(DrAwesomePhD @ Feb 21 2006, 05:23 PM) [snapback]348074[/snapback][/div][div class=\'quotemain\'][!--quotec--] Aw :-/ so basically i should make a login script and use the login names as unique ID's for signing up for the raids. That makes sense.... so much for being lazy :P thanks for the help!! [/quote]

That just may work... the only other thing that I just now thought of is will the whole query fail if there is no entry in the shipping_data table yet for that order_id? I would test it out now but I'm on my way out the door.

I would actually suggest making a table of all the members in your guild and give them ID numbers, then make a table for raids with unique ID numbers then make a thrid table that combines the two... for example... Members (member_id, rank, class, name, email, etc.) Raids (raid_id, raid_name, raid_description, raid_date, etc) Combined_raids (ID, member_id, raid_id, any info about the member for that raid) Then you use either member_id to get all the raids for a single person or raid_id to get everyone in a raid. Believe me, you do not want seperate tables for all of your raids. That was my thought process when I first started and its totally wrong. Relational databases are much easier to code for and deal with.

I think you misunderstood what I was trying to say... Heres basically what the tables in question look like (again, somewhat simplified) incoming_data: order_id int, line_items_received int, line_info_id int Shipping Data: order_id int, shipping_num tinyint, line_items_shipped int, line_info_id int, commit_date date Orders order_id int, customer_id tinyint, order_number int, etc... there is no outstanding line items table, the outstanding line items are generated by summing the incoming and substracting the outgoing. I can use something like this: [code] SELECT sum(line_tems_received)-sum(line_items_shipped) as remaining_items,... from orders O, incoming_data ID, shipping_data SD where O.commit_date>=subdate('2006-02-21',interval 14 DAY) AND O.order_id=ID.order_id AND O.order_id=SD.order_id group by order_id [/code] But I want to have the query determine if there are items remaining before returning data from the database.

I have to admit that I don't have extensive experience with multi table queries, so I thought some of you guys might be able to give me some tips here. Im trying to generate a report of orders we have that still have line items waiting to be shipped by the date we have committed to the customer. Incoming line items are stored in their own table. Shipped items are stored in another table and we almost always do multiple shipments. The problem that I have is that the first query is looking for the order_id #s that I need to find these sums and determine if there is any remaining. Ideally I would like to do it in just one query, but Im not sure how to do this without having an if/else query after the result is returned to determine if the remaining items are zero or not. Im using MySQL 4.1.5 on XP.

maybe I'm missing something, but you make it seem like they are from the same table which may be your problem... I would change it to say [code]SELECT DISTINCT(rRR) FROM RRInventory where rIndex='720';[/code] because whats the point in select rSite from the same table if you are never going to use it? [!--quoteo(post=347697:date=Feb 20 2006, 01:34 PM:name=pentan)--][div class=\'quotetop\']QUOTE(pentan @ Feb 20 2006, 01:34 PM) [snapback]347697[/snapback][/div][div class=\'quotemain\'][!--quotec--] Can anyone tell me what's wrong with this command? I keep getting an error messsage. select distinct(rRR) from RRInventory where rSite=(select rSite from RRInventory where rIndex='720'); Both select statments work fine alone but together the die. I looked in the manual and I must be missing something but I can't see it. Michael [/quote]

I am using mysql 4.1.5 on a Windows XP box. Up until a few weeks ago I was able to dump all of our databases, but the number of tables in our databases has grown to the point where it fails before it can get through a database... I get these errors: mysqldump: Got error 1105: File'.\file_name.MYD' not found (Errcode:24) when using LOCK TABLES. or mysqldump: Got error 1017: Can't Find File'.\file_name.frm' not found (Errcode:24) when using LOCK TABLES. the second warning occurs with the table immediately following the one that trips the first error. I'm pretty sure that it just has to do with the number of tables (currently 1093 tables in the database I'm attempting to dump). I think I had run into this problem before and modified the my.ini file to fix it but I can't seem to figure out the right variable to set... any ideas?