Optimizing a troublesome query

I'm generating a PDF, via php from 2 mysql tables, that contains a table. On larger tables the script is eating up a lot of memory and is sta开发者_Go百科rting to become a problem.

My first table contains "inspections." There are many rows per day. This has a many to one relationship with the user table.

Table "inspections"

---

id area inpsection_date inpsection_agent_1 inpsection_agent_2 inpsection_agent_3

---

id (int)

area (varchar) - is one of 8 "areas" ie: Concrete, Soils, Earthwork

inspection_date (int) - unix timestamp

inspection_agent_1 (int) - a user id

inspection_agent_2 (int) - a user id

inspection_agent_3 (int) - a user id

---

Second table is the user's info. All I need is to join the name to the "inspection_agents_x"

id name

---

The final table, that is going to be in the PDF, needs to organize the data by:

• by day
• by user, find every "area" that the user "inspected" on that day

                 Concrete Soils Earthwork

1/18/2011

Jon Doe           X

Jane Doe         X          X

And so on for each day. Right now I'm just doing a simple join on the names and then organizing everything on the code end. I know I'm leaving a lot on the table as far as the queries go, I just can't think of way to do it.

Thanks for any and all help.

---

Select U.name
    , user_inspections.inspection_date
    , Min( Case When user_inspections.area = 'Concrete' Then 'X' End ) As Concrete
    , Min( Case When user_inspections.area = 'Soils' Then 'X' End ) As Soils
    , Min( Case When user_inspections.area = 'Earthwork' Then 'X' End ) As Earthwork
From users As U
    Join    (
            Select area, inspection_date, inspection_agent1 As user_id
            From inspections
            Union All
            Select area, inspection_date, inspection_agent2 As user_id
            From inspections
            Union All
            Select area, inspection_date, inspection_agent3 As user_id
            From inspections
            ) As user_inspections
        On user_inspections.user_id = U.id
Group By U.name, user_inspections.inspection_date

This is effectively a static crosstab. It means that you will need to know all areas that should be outputted in the query at design time.

One of the reasons this query is problematic is that your schema is not normalized. Your inspection table should look like:

Create Table inspections
    (
    id int...
    , area varchar...
    , inspection_date date ...
    , inspection_agent int References Users ( Id )
    )

That would avoid the inner Union All query to get the output you want.

---

I would go like this:

select i.*, u1.name, u2.name, u3.name from inspections i left join users u1 on (i.inspection_agent_id1 = u1.id) left join users u2 on (i.inspection_agent_id2 = u2.id) left join users u3 on (i.inspection_agent_id3 = u3.id) order by i.inspection_date asc;

Then select distinct areas names and remember them or fetch them from area table if you have any:

select distinct area from inspections;

Then it's just foreach:

$day = "";
foreach($inspection in $inspections)
{
   if($day == "" || $inspection["inspection_date"] != $day)
   {
       //start new row with date here
   }

   //start standard row with user name
}

It isn't clear if you have to display all users each time ( even if some of them do not do inspection that thay), if you have to you should fetch users once and loop over $users and search for user in $inspection row.