CODEWITHSUNDEEP
mysqlsql
Allan Jason
Allan Jason

Reputation: 119

sql query chokes the server

When I run this query mysql server cpu usages stays at 100% and chokes the server. What am I doing wrong?

SELECT * 
FROM projects p, orders o, invoices i
WHERE p.project_state =  'product'
AND (
p.status =  'expired'
OR p.status =  'finished'
OR p.status =  'open'
)
AND p.user_id =  '12'
AND i.projectid =0
GROUP BY i.invoiceid
LIMIT 0 , 30

Upvotes: 0

Views: 599

Answers (3)

Dylan Smith
Dylan Smith

Reputation: 22245

You didn't put any joins on the tables. I believe by default that will do a cross join. That means if you have 1000 projects, 100,000 orders and 100,000 invoices the resultset will be 1,000,000,000,000 (1 trillion) records.

You probably want to put some inner joins between those tables.

Upvotes: 0

Ariel
Ariel

Reputation: 26753

You are including the orders table but not joining to it. This will make a full cross join that can potentially produce millions of rows.

Upvotes: 5

Ed Heal
Ed Heal

Reputation: 60007

Use EXPLAIN to find out the query plan. From that you can work out what indexes will be required. Those indexes will vastly improve performance.

Also you are not limiting the orders in any way.

Upvotes: 0

Related Questions