For the first time in a long time, my brain turned to mush while starring at code. I mean serious mush. I lost it. I couldn't think straight.. my eyes were blurry and I seriously think I would have fainted if I looked at the code any longer.
Maybe some of you database savvy folks can help.
I can't do anything about the table structure, so don't suggest a change there. I have four tables: ckts, orders, tasks, jeops. For every row in orders, there will be one or more rows in ckts. In addition, there will be one or more rows in tasks (but no more than one row per task_type, which is a field in the tasks table). For every row in tasks there may or may not be (OUTER JOIN) one (but no more than one) row in jeops. The orders, tasks, and ckts tables are related by two fields: document_number and region. The tasks, and jeops table are related by the task_number field.
There are several other tables that describe report criteria. My mission is to build those reports. The additional data specifies various filters that limit the amount of data returned in each table. For instance: a user can filter on which state the order is in (ckts table), when the order is due (tasks table with a task_type of DD). The user can also select which fields they would like to see and which task types they would like to see. One big join is not a good idea, unless you can think of a better way to do it than I have. If a filter limits a particular document_number and region combination from being shown in the ckts table, rows with that same document_number and region being returned from the tasks and/or orders table should be discarded as well. In other words, in the end, the report should join all four tables into one row (creating field names based on the requested task_types for those items in the tasks table) and those rows should meet all the filter criteria specified for each table..
The database server being used is MySQL. The ckts table holds about 60,000 rows, the orders table about 25,000 rows, the tasks table about 120,000 rows, and the jeops table about 30,000 rows.
One idea is an elaborate data storage mechanism implemented with arrays to hold the data as each table is queried with the included filter criteria. By running through each of the arrays several times, data can be removed when it is not found in ALL tables.
Another idea would be to start with the ckts or tasks tables (as there tend to be more filters on those tables) and then query for the document_number and region criteria from the additional tables to see if those tables match the additional requirements.
The report should not take more than 3 to 5 minutes to generate.
Any help or ideas are appreciated.
