StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POImprove performance of mysql query
primarykey
Id
19989247
data
AcceptedAnswerId
0
AnswerCount
2
ClosedDate
CommentCount
6
CommunityOwnedDate
CreationDate
2013-11-14T21:57:20.643
FavoriteCount
0
LastActivityDate
2013-11-14T22:55:58.087
LastEditDate
2013-11-14T22:55:58.087
LastEditorUserId
819732
OwnerUserId
819732
ParentId
0
PostTypeId
1
Score
1
ViewCount
80
LastEditorDisplayName
text
Body
I have a mysql sql select that's taking way to long to return data. Tables <pre><code>╔════════════════╗ ╔════════════════╗ ║ ITEM ║ ║ Workspace ║ ╠════════════════║ ╠════════════════║ ║ id ║ ║ id ║ ║ guid ║ ║ guid ║ ║ workspace_id ║ ║ company_id ║ ║ deleted ║ ║ deleted ║ ╚════════════════╝ ╚════════════════╝ Indexes: id, guid Indexes: id, guid, workspace_id company_id ╔════════════════╗ ╔════════════════════╗ ║ COMPANY ║ ║ item_category_xref ║ ╠════════════════║ ╠════════════════════║ ║ id ║ ║ item_id ║ ║ deleted ║ ║ category_id ║ ╚════════════════╝ ╚════════════════════╝ Indexes: id Indexes: item_id, category_id ╔════════════════╗ ╔═════════════════════╗ ║ item_image ║ ║ tracking_action ║ ╠════════════════║ ╠═════════════════════║ ║ item_id ║ ║ id ║ ║ sequence ║ ║ guid ║ ╚════════════════╝ ║ action ║ Indexes: ║ context ║ (item_id, sequence) ║ deleted ║ ╚═════════════════════╝ </code></pre> SQL <pre><code> SELECT itm.id "item.id", ws.id "workspace.id", co.id "company.id", ((SELECT count(*) FROM item_category_xref icx WHERE icx.item_id = itm.id AND icx.featured = 1) > 0) "featured", (SELECT COUNT(*) FROM tracking_action ta1 WHERE ta1.context = 'ITEM' AND ta1.context_guid = itm.guid AND ta1.action = 'VIEW') ta_view_count , (SELECT COUNT(*) FROM tracking_action ta2 WHERE ta2.context = 'ITEM' AND ta2.context_guid = itm.guid AND ta2.action = 'SEARCH_RESULT') ta_search_count FROM item itm JOIN workspace ws ON itm.workspace_id = ws.id AND ws.deleted != 1 JOIN company co ON ws.company_id = co.id AND co.deleted != 1 JOIN item_category_xref icx ON itm.id = icx.item_id AND icx.category_id = 1 LEFT JOIN item_image ii ON itm.id = ii.item_id AND ii.sequence = 1 WHERE itm.deleted != 1 HAVING featured > 0; </code></pre> EXPLAIN <img src="https://i.stack.imgur.com/xihfT.png" alt="SQL EXPLAIN"> This query is a result of my efforts to reduce and improve. I've gone from the original query that took 180 seconds down to this that now takes about 20 seconds but its still not enough. Can anyone offer performance improvements for this query? We're searching through a few million rows of data so every little bit will help.
Tags
<mysql><sql><query-performance>
Title
Improve performance of mysql query
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USkasdega
UserOwnerUserId
1. USkasdega
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
 POImprove performance of mysql query
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

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.