Please help me optimize this query

Oddish · #1 (**permalink**) 02-19-07, 10:58

The following query takes forever to complete and I'm not exactly an SQL guru so I was hoping some of the experts here could take a quick look at it. Can you see anything seriously wrong with it?

Code:

SELECT ord_workorders.*
FROM (ord_workorders
LEFT JOIN ord_reports r1 ON (ord_workorders.id = r1.workorderid AND r1.invoiced = 1))
LEFT JOIN ord_reports r2 ON (ord_workorders.id = r2.workorderid AND r2.invoiced <> 1)
WHERE ord_workorders.completed = 1
AND (r1.workorderid IS NULL OR r2.workorderid IS NOT NULL)
GROUP BY ord_workorders.id ORDER BY ord_workorders.title DESC

When I run it in phpMyAdmin I get one column full of question marks that says (when I hover over them) "You should define a primary key for this table". I've got primary keys on all tables, so what does this message mean?

r937 · #2 (**permalink**) 02-19-07, 11:07

first of all, let's address the issue of your GROUP BY, which is invalid (on account of your use of the dreaded, evil "select star")

you want one result per ord_workorders.id, so presumably, you want distinct rows from the ord_workorders table, based on whether the row satisfies your LEFT JOINs and WHERE conditions

could you explain in words what those LEFT JOINs are supposed to do?

it looks to me like you want completed workorders that
- do not have an invoiced row in the reports table
OR
- that do have a reports row for any other invoice status

i'm not sure i understand this logic

Oddish · #3 (**permalink**) 02-20-07, 01:59

I'll try my best to explain. There are work orders (ord_workorders) and, in another table (ord_reports) there are "reports" that belong to a work order. The results of this query is supposed to show completed work orders.

A work order is considered completed when the "completed" column is "1", but if the work order contains one or more reports, and all of these reports are "invoiced", it should not be included in the result, because those work orders are called "invoiced work orders" and they are to be displayed on another page.

To sum up, I'm looking for work orders (ord_workorders) that:
1. have been set as "completed"
2. have got at least one report (ord_reports) that is not set as "invoiced"

Thanks very much in advance for anything that could help me!

r937 · #4 (**permalink**) 02-20-07, 05:23

does this work for you --

Code:

SELECT *
  FROM ord_workorders
 WHERE completed = 1
   AND EXISTS
       ( select *
           from ord_reports  
          where workorderid = ord_workorders.id 
            AND invoiced <> 1 )
ORDER 
    BY ord_workorders.title DESC

Oddish · #5 (**permalink**) 02-21-07, 04:14

I get this:

Quote:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS ( select * from ord_reports

r937 · #6 (**permalink**) 02-21-07, 06:22

what do you get for this query:

Code:

select version() as v

gvee · #7 (**permalink**) 02-21-07, 06:52

AND primarykey EXISTS in (SELECT foreignkey ...?

*shrugs*

Oddish · #8 (**permalink**) 02-28-07, 02:59

Hi again, I've been away for a while, sorry for the late response while you're trying to help me out with this.

The MySQL version on the server is (unfortunately) 4.0.20.

georgev, sorry I'm not sure what to make of your post.

r937 · #9 (**permalink**) 02-28-07, 03:11

that's what i figured

well, i think you should upgrade to 4.1

you'd be able to use subqueries

Internet Services

Web Development

Digital Marketing

Consumer Tech