Databases
StackOverflow2013
Postsdbo
POExtremely slow join with join buffer
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExtremely slow join with join buffer
    primarykey
    data
    text
    <p>I am having a problem with a complex query with multiple joins. When running EXPLAIN:</p> <p><strong>Query</strong></p> <pre><code>explain select ud.id from user_detail ud cross join ticket t cross join guest_list gl cross join event e cross join venue v where t.guest_list = gl.id and gl.event = e.id and e.venue = v.id and (ud.account = 10 or ud.venue = 10 or ud.event = 10 or ud.guest_list = 10 or t.reference_user = 10 and (ud.guest_list=t.guest_list or ud.event = gl.event or ud.venue = e.venue or ud.account = v.account) and (t.guest_list = 10)) </code></pre> <p><strong>I get this:</strong></p> <pre><code>id, select_type, table, type, rows, extra 1, SIMPLE, v, index, 2, "Using index" 1, SIMPLE, e, ref, 2, "Using where; using index" 1, SIMPLE, gl, ref, 1, "Using where; using index" 1, SIMPLE, t, ref, 418, "Using where" 1, SIMPLE, ud, ALL, 44028, "Using where; Using join buffer" </code></pre> <p>The data model is like this:</p> <p>Account &lt;1-<em>> Venue &lt;1-</em>> Event &lt;1-<em>> GuestList &lt;1-</em>> Ticket UserDetail has an account, venue, event or guest list as a parent.</p> <p>And what I am trying to do with this query is to get all of the UserDetail that has one of the specific account/venue/event/guestlist as a parent, OR that has a guestlist as a parent that has a ticket that has the reference_user field set to a specific user.</p> <p><strong>Hibernate criteria</strong></p> <pre><code>public List&lt;UserDetail&gt; listUserDetails(final Collection&lt;UserDetailNode&gt; anyOfNodes, final User orTicketReferenceUser, final Collection&lt;GuestList&gt; andAnyOfGuestlistsForTicketReferenceUser, final Collection&lt;User&gt; anyOfUsers, final Date fromLastModificationDate, final Date toLastModificationDate, final Boolean deletedNodes, final Boolean deletedUsers, final Boolean deletedUserDetails) { final CriteriaBuilder cb = entityManager.getCriteriaBuilder(); final CriteriaQuery&lt;UserDetail&gt; cq = cb.createQuery(UserDetail.class); final Root&lt;UserDetail&gt; userDetail = cq.from(UserDetail.class); Predicate criteria = cb.conjunction(); if (anyOfNodes != null || orTicketReferenceUser != null) { Predicate subCriteria = cb.disjunction(); if (anyOfNodes != null) { Predicate anyOfNodesCriteria = cb.disjunction(); Collection&lt;Account&gt; anyOfAccounts = null; Collection&lt;Venue&gt; anyOfVenues = null; Collection&lt;Event&gt; anyOfEvents = null; Collection&lt;GuestList&gt; anyOfGuestLists = null; final Set&lt;UserDetailNode&gt; anyOfNodesWithParents = new HashSet&lt;UserDetailNode&gt;(); for (UserDetailNode node : anyOfNodes) { while (node != null) { anyOfNodesWithParents.add(node); node = node.getParentNode(); } } for (final UserDetailNode node : anyOfNodesWithParents) { if (node instanceof Account) { if (anyOfAccounts == null) anyOfAccounts = new ArrayList&lt;Account&gt;(); anyOfAccounts.add((Account)node); } else if (node instanceof Venue) { if (anyOfVenues == null) anyOfVenues = new ArrayList&lt;Venue&gt;(); anyOfVenues.add((Venue)node); } else if (node instanceof Event) { if (anyOfEvents == null) anyOfEvents = new ArrayList&lt;Event&gt;(); anyOfEvents.add((Event)node); } else if (node instanceof GuestList) { if (anyOfGuestLists == null) anyOfGuestLists = new ArrayList&lt;GuestList&gt;(); anyOfGuestLists.add((GuestList)node); } } if (anyOfAccounts != null) anyOfNodesCriteria = cb.or(anyOfNodesCriteria, cb.or(userDetail.get("account").in(anyOfAccounts))); if (anyOfVenues != null) anyOfNodesCriteria = cb.or(anyOfNodesCriteria, cb.or(userDetail.get("venue").in(anyOfVenues))); if (anyOfEvents != null) anyOfNodesCriteria = cb.or(anyOfNodesCriteria, cb.or(userDetail.get("event").in(anyOfEvents))); if (anyOfGuestLists != null) anyOfNodesCriteria = cb.or(anyOfNodesCriteria, cb.or(userDetail.get("guestList").in(anyOfGuestLists))); subCriteria = cb.or(subCriteria, anyOfNodesCriteria); } if (orTicketReferenceUser != null &amp;&amp; (andAnyOfGuestlistsForTicketReferenceUser == null || !andAnyOfGuestlistsForTicketReferenceUser.isEmpty())) { final Root&lt;Ticket&gt; ticket = cq.from(Ticket.class); Predicate ticketCriteria = cb.equal(ticket.get("referenceUser"), orTicketReferenceUser); ticketCriteria = cb.and(ticketCriteria, cb.or(cb.equal(userDetail.get("guestList"), ticket.get("guestList")), cb.equal(userDetail.get("event"), ticket.get("guestList").get("event")), cb.equal(userDetail.get("venue"), ticket.get("guestList").get("event").get("venue")), cb.equal(userDetail.get("account"), ticket.get("guestList").get("event").get("venue").get("account")))); if (andAnyOfGuestlistsForTicketReferenceUser != null) ticketCriteria = cb.and(ticketCriteria, ticket.get("guestList").in(andAnyOfGuestlistsForTicketReferenceUser)); subCriteria = cb.or(subCriteria, ticketCriteria); } criteria = cb.and(criteria, subCriteria); } if (anyOfUsers != null) { if (anyOfUsers.isEmpty()) return new ArrayList&lt;UserDetail&gt;(); criteria = cb.and(criteria, userDetail.get("user").in(anyOfUsers)); } if (fromLastModificationDate != null) criteria = cb.and(criteria, cb.greaterThanOrEqualTo(userDetail.&lt;Date&gt;get("lastModificationDate"), fromLastModificationDate)); if (toLastModificationDate != null) criteria = cb.and(criteria, cb.lessThanOrEqualTo(userDetail.&lt;Date&gt;get("lastModificationDate"), toLastModificationDate)); cq.select(userDetail).distinct(true).where(criteria); return entityManager.createQuery(cq).getResultList(); } </code></pre> <p>From what I can see the last row is the problem, but how can I fix this? This query is auto-generated by hibernate, so I am not sure how much I can alter it.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPiotr
    1. USPiotr
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COdo you have any indexes on user_detail table?
      singulars
      1. POExtremely slow join with join buffer
      1. USaF.
    2. COYes, all used columns are either indexed or foreign keys
      singulars
      1. POExtremely slow join with join buffer
      1. USPiotr
    3. COwhy on earth are you using cross joins for this?
      singulars
      1. POExtremely slow join with join buffer
      1. USHLGEM
 

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.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload