StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
6970766
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2011-08-07T04:36:46.773
FavoriteCount
0
LastActivityDate
2011-08-07T04:36:46.773
LastEditDate
LastEditorUserId
0
OwnerUserId
735614
ParentId
6921198
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
<p>Okay, so I eventually figured out what I'm gonna end up doing. I couldn't make the 'data' query any faster - that's still the bottleneck. But now I've made it so the total query process is pretty close to linear, not exponential. I split the query into two parts and made each one into a temporary table. Then I added an index for each of those temp tables and did the join separately afterwards. This made the total execution time for 1 month of data drop from 8 to 2 seconds, and for 1 year of data from ~10 minutes to ~30 seconds. Good enough for now, I think. I can work with that.</p> <p>Thanks for the suggestions. Here's what I ended up doing:</p> <pre><code>create table listing ( SELECT nutrdesc, nutr_no, date_time, units FROM meals, nutr_def WHERE meals.users_userid = '2' AND date_time BETWEEN '2009-8-12' AND '2009-9-12' AND ( nutr_no <100000 OR nutr_no IN ( SELECT nutr_def_nutr_no FROM nutr_rights WHERE nutr_rights.users_userid = '2' ) ) ); create table data ( SELECT nutrdesc, date_time, nut_data.nutr_no, sum(ingred_gram_mass*entry_qty_num*nutr_val/100) AS total_nutr_mass FROM nut_data, recipe_ingredients, food_entries, meals, nutr_def WHERE nut_data.nutr_no = nutr_def.nutr_no AND ndb_no = ingred_ndb_no AND foods_food_id = entry_ident AND meals_meal_id = meal_id AND users_userid = '2' AND date_time BETWEEN '2009-8-12' AND '2009-9-12' GROUP BY date_time,nut_data.nutr_no ); create index joiner on data(nutr_no, date_time); create index joiner on listing(nutr_no, date_time); SELECT listing.date_time,listing.nutrdesc,data.total_nutr_mass,listing.units FROM listing LEFT JOIN data ON data.date_time = listing.date_time AND listing.nutr_no = data.nutr_no ORDER BY listing.date_time,listing.nutrdesc,listing.units; </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POOptimizing / improving a slow mysql query - indexing? reorganizing?
  singulars
  PostTypePostTypeId
  PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USNato Saichek
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POOptimizing / improving a slow mysql query - indexing? reorganizing?
  singulars
  PostTypePostTypeId
  PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
  singulars
  PostPostId
  PO
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTAcceptedByOriginator
CommentsPostId
1. This table or related slice is empty.

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.