Databases
StackOverflow2013
Postsdbo
POQuery performance issue with query inside where clause
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POQuery performance issue with query inside where clause
    primarykey
    data
    text
    <p>I have following somewhat complex <code>sql</code> query which has horrible performance, 'certainly' due to the inner query inside the <code>where</code> clause. In some cases it takes over a minute. Does anybody know how to rewrite this query giving better performance? <br> The query:</p> <pre><code>SELECT DISTINCT t.id as taskId, t.name as taskName, t.startdate as taskStartDate, t.enddate as taskEndDate, t.proj_id as taskProjectId FROM PROJECT p, EMPL_PROJ ep, TASK t, TIMERECORD tr WHERE ep.empl_id = ? AND ep.proj_id = p.id AND ep.proj_id = t.proj_id AND ((p.startdate IS NULL AND p.enddate IS NULL) OR (p.startdate IS NULL AND p.enddate &gt;= ?) OR (p.enddate IS NULL AND p.startdate &lt;= ? + INTERVAL 6 DAY) OR (p.startdate &lt;= ? + INTERVAL 6 DAY AND p.enddate &gt;= ?) ) AND ((t.startdate IS NULL AND t.enddate IS NULL) OR (t.startdate IS NULL AND t.enddate &gt;= ?) OR (t.enddate IS NULL AND t.startdate &lt;= ? + INTERVAL 6 DAY) OR (t.startdate &lt;= ? + INTERVAL 6 DAY AND t.enddate &gt;= ?)) AND ( (ep.empl_id = tr.empl_id AND ep.proj_id = tr.proj_id AND t.id = tr.task_id AND tr.day &lt;= ? + INTERVAL 7 DAY AND tr.day &gt;= ? + INTERVAL -14 DAY ) OR ( (SELECT count(*) FROM TIMERECORD tr2 WHERE tr2.empl_id=ep.empl_id AND tr2.proj_id=p.id AND tr2.day &lt;= ? + INTERVAL 7 DAY AND tr2.day &gt;= ? + INTERVAL -14 DAY) &lt;= 0 ) ) </code></pre> <p>I'm using mysql server 5.1.40.</p> <p><b>Edit (2):</b> With the comments and answers I came to this query which executes under a second (not bad coming from almost a minute!)</p> <pre><code>SELECT DISTINCT t.id as taskId, t.name as taskName, t.startdate as taskStartDate, t.enddate as taskEndDate, t.proj_id as taskProjectId FROM (PROJECT p INNER JOIN EMPL_PROJ ep ON ep.proj_id = p.id) INNER JOIN TASK t ON p.id=t.proj_id INNER JOIN TIMERECORD tr ON tr.empl_id=ep.empl_id AND tr.proj_id=ep.proj_id AND tr.task_id=t.id WHERE ep.empl_id = ? AND ((p.startdate IS NULL AND p.enddate IS NULL) OR (p.startdate IS NULL AND p.enddate &gt;= ?) OR (p.enddate IS NULL AND p.startdate &lt;= ? + INTERVAL 6 DAY) OR (p.startdate &lt;= ? + INTERVAL 6 DAY AND p.enddate &gt;= ?) ) AND ((t.startdate IS NULL AND t.enddate IS NULL) OR (t.startdate IS NULL AND t.enddate &gt;= ?) OR (t.enddate IS NULL AND t.startdate &lt;= ? + INTERVAL 6 DAY) OR (t.startdate &lt;= ? + INTERVAL 6 DAY AND t.enddate &gt;= ?)) AND ( ( tr.day &lt;= ? + INTERVAL 7 DAY AND tr.day &gt;= ? + INTERVAL -14 DAY ) OR ( NOT EXISTS(SELECT * FROM TIMERECORD tr2 INNER JOIN EMPL_PROJ ON tr2.empl_id=EMPL_PROJ.empl_id INNER JOIN PROJECT ON PROJECT.id=tr2.proj_id WHERE tr2.day BETWEEN ? + INTERVAL -14 DAY AND ? + INTERVAL 7 DAY) ) ) ORDER BY p.id, t.id </code></pre> <p>Biggest contribution was the answer suggesting the <code>NOT EXISTS</code> approach (which I marked as being correct) and the comment not to mix the <code>explicit</code> and <code>implicit JOIN</code>'s. <p> Thanks to all!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjb10210
    1. USjb10210
    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
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POQuery performance issue with query inside where clause
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO"probably" can be upgraded to "certainty" by trying the query with, and without, the suspect part to see if its introduction causes the slowness. Doing this may also allow you to clear away irrelevant code in an effort to create a [SSCCE](http://www.sscce.org), thus making your question more applicable to others and more easily answerable
      singulars
      1. POQuery performance issue with query inside where clause
      1. USBohemian
    2. CO@Bohemian :) I did this already, and for sure (certainly) the inner select statement is the cause of the performance issue. Just don't really know how to rewrite the query without it.
      singulars
      1. POQuery performance issue with query inside where clause
      1. USjb10210
    3. COStep 1 would be using newer `JOIN` syntax instead of this old deprecated cross joins with where filters stuff.
      singulars
      1. POQuery performance issue with query inside where clause
      1. USJNK
 

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