MySQL hangs when query is run. How can I make this more efficient?

Question

We are trying to run a query to get all unpaid invoices. When I run the query it hangs our whole system. I am just wondering if there is a way I can make this more efficent. SQL isn't my strong point.

$query = "SELECT SQL_CALC_FOUND_ROWS i.order_id, o., acct., (SELECT SUM(items.item_qty) AS qty FROM items WHERE items.order_id = o.order_id ) AS order_qty
FROM items AS i
INNER JOIN orders AS o ON o.order_id = i.order_id
INNER JOIN transactions AS t ON t.order_id = o.order_id
INNER JOIN accounts AS acct ON o.acct_id = acct.acct_id
INNER JOIN ship_to AS st ON o.ship_id = st.ship_id
WHERE o.order_status=7 AND o.order_date > '2009-05-01 00:00:00'
AND (SELECT SUM(items.item_price) AS price FROM items WHERE items.order_id = o.order_id) * (SELECT SUM(items.item_qty) AS qty FROM items WHERE items.order_id = o.order_id) + o.order_ship_amount-(SELECT sum(trans_amount) FROM transactions WHERE t.order_id = o.order_id AND trans_pending = 0)!=0
AND acct.is_wholesale=1
GROUP BY o.order_id
ORDER BY o.order_date
LIMIT $offset, $limit";

Here is the table layout information for the needed tables:

CREATE TABLE items (
item_id int(11) NOT NULL auto_increment,
order_id int(11) NOT NULL default '0',
prod_id int(11) NOT NULL default '0',
scat_id int(11) NOT NULL default '0',
inv_id int(11) NOT NULL default '0',
item_qty int(11) NOT NULL default '0',
item_price float(10,3) NOT NULL default '0.000',
item_mfg varchar(200) NOT NULL default '0',
item_group int(11) NOT NULL default '0',
item_ship_date datetime NOT NULL default '0000-00-00 00:00:00',
date_created datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (item_id),
KEY prod_id (prod_id),
KEY order_id (order_id),
KEY inv_id (inv_id),
KEY scat_id (scat_id)
) ENGINE=MyISAM AUTO_INCREMENT=834659 DEFAULT CHARSET=latin1

CREATE TABLE orders (
order_id int(11) NOT NULL auto_increment,
acct_id int(11) NOT NULL default '0',
order_date timestamp NOT NULL default CURRENT_TIMESTAMP,
order_confirm_date datetime NOT NULL default '0000-00-00 00:00:00',
order_approval_date datetime NOT NULL default '0000-00-00 00:00:00',
order_deposit_date datetime NOT NULL default '0000-00-00 00:00:00',
order_sent_to_mfg datetime NOT NULL default '0000-00-00 00:00:00',
order_due_date datetime NOT NULL default '0000-00-00 00:00:00',
order_ship_date datetime NOT NULL default '0000-00-00 00:00:00',
order_exp_ship_date datetime NOT NULL default '0000-00-00 00:00:00',
order_ship_type text NOT NULL,
order_ship_amount float(10,2) NOT NULL default '0.00',
order_mfg_name int(11) NOT NULL default '0',
order_notes text NOT NULL,
order_status int(11) NOT NULL default '0',
ship_id int(11) NOT NULL default '0',
bill_id int(11) NOT NULL default '0',
order_requested_quote int(11) NOT NULL default '0',
order_submitted int(11) NOT NULL default '0',
order_origin int(11) NOT NULL default '0',
order_po_no varchar(25) NOT NULL default '',
qd_id int(11) NOT NULL default '0',
order_inactive int(11) NOT NULL default '0',
order_cancelled datetime NOT NULL default '0000-00-00 00:00:00',
site_id int(11) NOT NULL default '0',
PRIMARY KEY (order_id),
KEY ship_id (ship_id),
KEY bill_id (bill_id),
KEY acct_id (acct_id),
KEY site_id (site_id)
) ENGINE=MyISAM AUTO_INCREMENT=20311622 DEFAULT CHARSET=latin1

CREATE TABLE transactions (
trans_id int(11) NOT NULL auto_increment,
order_id int(11) NOT NULL default '0',
trans_pnref text NOT NULL,
trans_card_type text NOT NULL,
trans_date datetime NOT NULL default '0000-00-00 00:00:00',
trans_amount float(10,2) NOT NULL default '0.00',
trans_type text NOT NULL,
trans_tender text NOT NULL,
trans_po_no text NOT NULL,
trans_origin text NOT NULL,
trans_rep int(11) NOT NULL default '0',
trans_po_received datetime NOT NULL default '0000-00-00 00:00:00',
trans_inactive int(11) NOT NULL default '0',
trans_pending int(11) NOT NULL default '0',
trans_secured int(11) NOT NULL default '0',
site_id int(11) NOT NULL default '0',
PRIMARY KEY (trans_id),
KEY cod_id (order_id),
KEY site_id (site_id)
) ENGINE=MyISAM AUTO_INCREMENT=211554 DEFAULT CHARSET=latin1

CREATE TABLE accounts (
acct_id int(11) NOT NULL auto_increment,
acct_signup timestamp NOT NULL default CURRENT_TIMESTAMP,
acct_first text NOT NULL,
acct_last text NOT NULL,
acct_company text NOT NULL,
acct_email text NOT NULL,
acct_email_cc text NOT NULL,
acct_email_bcc text NOT NULL,
acct_phone text NOT NULL,
acct_fax text NOT NULL,
acct_password text NOT NULL,
acct_default_ship int(11) NOT NULL default '0',
acct_default_bill int(11) NOT NULL default '0',
is_account int(1) NOT NULL default '0',
is_wholesale int(1) NOT NULL default '0',
site_id int(11) NOT NULL default '0',
tpsg_id int(11) NOT NULL default '0',
PRIMARY KEY (acct_id),
KEY acct_default_ship (acct_default_ship),
KEY acct_default_bill (acct_default_bill),
KEY site_id (site_id),
KEY tpsg_id (tpsg_id)
) ENGINE=MyISAM AUTO_INCREMENT=264476 DEFAULT CHARSET=latin1

CREATE TABLE ship_to (
ship_id int(11) NOT NULL auto_increment,
acct_id int(11) NOT NULL default '0',
ship_first text NOT NULL,
ship_last text NOT NULL,
ship_company text NOT NULL,
ship_address1 text NOT NULL,
ship_address2 text NOT NULL,
ship_city text NOT NULL,
ship_state text NOT NULL,
ship_zip text NOT NULL,
ship_country text NOT NULL,
ship_phone text NOT NULL,
ship_fax text NOT NULL,
ship_notes text NOT NULL,
ship_inactive int(1) unsigned NOT NULL default '0',
PRIMARY KEY (ship_id),
KEY acct_id (acct_id)
) ENGINE=MyISAM AUTO_INCREMENT=241339 DEFAULT CHARSET=latin1

What I want this query to do is to pull the order information for all orders that have a status of 7 are after May 1, 2009 and add up the prices for all items in the order multiplied by the quantity then add the shipment amount and and subtract from it the amount paid and check if it's not 0.Also, the account has to be a wholesale account.

I know this query is very inefficient but I am not sure how else to do it. Our system is so overwhelmed it is very laggy in general.

I would appreciate any help!

Answer 1

Use explain to analyze query execution plan: http://dev.mysql.com/doc/refman/5.1/en/using-explain.html and http://dev.mysql.com/doc/refman/5.1/en/explain.html

In general subqueries in WHERE are very slow, try to rewrite them as JOIN.

Answer 2

It is the subqueries in the WHERE clause. Naktibala hit the nail on the head there...

You can also try to create some temporary tables for the data you are getting for your subqueries. Basically, put the data you will need to temp table(s), then JOIN those table(s) normally to your base query. Once that all works you can streamline/tweak.

Answer 3

As you allready join items and transactions by order_id, I think that you can drop your subquery condition, and move checks to HAVING.

Try running this query:

SELECT SQL_CALC_FOUND_ROWS i.order_id, o.*, acct.*, SUM(items.item_qty) AS order_qty
FROM items AS i
INNER JOIN orders AS o ON o.order_id = i.order_id
INNER JOIN transactions AS t ON t.order_id = o.order_id
INNER JOIN accounts AS acct ON o.acct_id = acct.acct_id
INNER JOIN ship_to AS st ON o.ship_id = st.ship_id
WHERE o.order_status=7 AND o.order_date > '2009-05-01 00:00:00'
AND acct.is_wholesale=1
GROUP BY o.order_id
HAVING ( SUM(items.item_price) * SUM(items.item_qty) + o.order_ship_amount - SUM(transactions.trans_amount) ) != 0
ORDER BY o.order_date
LIMIT $offset, $limit

Answer 4

See if this describes your query a little more simply (and isn't a bit more efficient.)

SELECT   
    o.order_id,  
    o.order_ship_amount,  
    SUM(i.item_qty * i.item_price) AS item_amount,  
    SUM(trans_amount) AS trans_amount,  
    ... etc.
FROM  
    orders AS o  
INNER JOIN accounts AS acct ON o.acct_id = acct.acct_id  
INNER JOIN ship_to AS st ON o.ship_id = st.ship_id  
INNER JOIN items AS i ON o.order_id = i.order_id  
LEFT JOIN transactions AS t ON t.order_id = o.order_id  
    AND trans_pending = 0  
WHERE   
    o.order_status = 7   
    AND o.order_date > '2009-05-01 00:00:00'  
    AND acct.is_wholesale = 1   
GROUP BY o.order_id  
HAVING item_amount + order_ship_amount - trans_amount != 0  
ORDER BY o.order_date  

Make sure you can read and understand it - I haven't tested it.

Answer 5

SELECT   
    o.order_id,  
    o.order_ship_amount,  
    SUM(i.item_qty * i.item_price) AS item_amount,  
    (   SELECT SUM(trans_amount) FROM transactions
        WHERE order_id = o.order_id AND trans_pending = 0
    ) AS trans_amount,
    ... etc.
FROM  
    orders AS o  
INNER JOIN accounts AS acct ON o.acct_id = acct.acct_id  
INNER JOIN ship_to AS st ON o.ship_id = st.ship_id  
INNER JOIN items AS i ON o.order_id = i.order_id  
WHERE   
    o.order_status = 7   
    AND o.order_date > '2009-05-01 00:00:00'  
    AND acct.is_wholesale = 1   
GROUP BY o.order_id  
HAVING item_amount + order_ship_amount - trans_amount != 0  
ORDER BY o.order_date