0
\$\begingroup\$
SELECT v.valuation_id
FROM valuation v
LEFT JOIN valuation_task vt
on vt.valuation_id = v.valuation_id 
      AND vt.valuation_task_id = 
        (
           SELECT valuation_task_id
           FROM valuation_task y
           WHERE y.valuation_id = v.valuation_id AND y.valuation_task_name = 'Listing live' AND y.valuation_task_status = '3'
        )

For each valuation (valuation_id), I want to search the valuation_task table, for a task with the attributes taskname = 'Listing live' and task status = '3' (which means the task is completed). There are approximately 8 valuation tasks against each valuation. The query takes over a minute to execute which is obviously unworkable.

Screenshot of the valuation_task table attached. There are 15069 rows in the valuation_task table and circa 2000 rows in the valuation table.

Happy to provide any further information required.

Screenshot

\$\endgroup\$
8
  • \$\begingroup\$ What immediately caught my eye is the naming of the table's columns. All valuation_task_* prefixes are redundant and should be removed, since the table is already called valuation_task. Hence it is obvious, that a field id would constitute the valuation_task's id. \$\endgroup\$ Jul 10 at 11:08
  • \$\begingroup\$ Thanks @RichardNeumann, I will keep that in mind. Would changing the field names improve the speed of the query? \$\endgroup\$
    – Michael LB
    Jul 10 at 11:14
  • 1
    \$\begingroup\$ No. Certainly not. It would just improve the query readability imho. \$\endgroup\$ Jul 10 at 11:15
  • \$\begingroup\$ I also wonder why you do this weird sub-query, which most likely is responsible for the performance penalty. Can't you just slap the WHERE clause onto the outer select? It's difficult to judge whether that'll provide the desired result, since I don't know what your're trying to achieve here. \$\endgroup\$ Jul 10 at 11:17
  • \$\begingroup\$ @RichardNeumann I have just added more information to the question with regards to what I'm trying to achieve. I can't put a WHERE clause on the outer select and the query needs to know what valuation_task to join to in the first instance. There are 8 valuation tasks against each valuation \$\endgroup\$
    – Michael LB
    Jul 10 at 11:31

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.