StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOptimizing a troublesome query
primarykey
Id
4725245
data
AcceptedAnswerId
4726503
AnswerCount
2
ClosedDate
CommentCount
1
CommunityOwnedDate
CreationDate
2011-01-18T14:33:14.703
FavoriteCount
1
LastActivityDate
2011-01-18T16:35:15.033
LastEditDate
LastEditorUserId
0
OwnerUserId
207827
ParentId
0
PostTypeId
1
Score
2
ViewCount
73
LastEditorDisplayName
text
Body
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 starting 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" <hr> id area inpsection_date inpsection_agent_1 inpsection_agent_2 inpsection_agent_3 <hr> 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 <hr> <h2>Second table is the user's info. All I need is to join the name to the "inspection_agents_x"</h2> id name <hr> The final table, that is going to be in the PDF, needs to organize the data by: <ul> <li>by day</li> <li>by user, find every "area" that the user "inspected" on that day</li> </ul>                  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. 
Tags
<sql><mysql><optimization>
Title
Optimizing a troublesome query
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USws0x9
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POOptimizing a troublesome query
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 POOptimizing a troublesome query
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 POOptimizing a troublesome query
 UserUserId
 USws0x9
 VoteTypeVoteTypeId
 VTFavorite
CommentsPostId
1. COWould it be more efficient to create a temporarily table some how to get users the multiple users per row down to one?
 singulars
 PostPostId
 POOptimizing a troublesome query
 UserUserId
 USws0x9

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.