Simple Join Taking Too Long
Posted by:
Curtis Poe
Date: September 03, 2007 08:33AM
This is mysql 4.0.18 (We are not in a position to upgrade right now. I have no say in the matter).
Any idea what I can do to improve the performance of the following query? The query takes almost 50 seconds to run and locks the entire tables as it runs (sorry for the poor formatting. Can't tell how to override this). Also, any links to a good online resource which covers basic performance tuning would be great!
SELECT cost_net,
b.authname AS b_authname,
i.authname AS i_authname
FROM bill b,
invoices i
WHERE i.affid = 654248
AND i.invoice = b.invoice
AND b.desctype = 0
AND i.commpaid = 0;
+----------+------------+------------+
| cost_net | b_authname | i_authname |
+----------+------------+------------+
| 5.18 | jpcdesign | jpcdesign |
| 5.18 | jpcdesign | jpcdesign |
| 5.18 | jpcdesign | jpcdesign |
| 5.18 | jpcdesign | jpcdesign |
| 17.98 | jpcdesign | jpcdesign |
| 5.18 | jpcdesign | jpcdesign |
+----------+------------+------------+
6 rows in set (48.17 sec)
And the EXPLAIN on the exact same query:
+----+-------------+-------+--------+------------------+----------+---------+----------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------+----------+---------+----------------+---------+-------------+
| 1 | SIMPLE | b | ref | desctype,invoice | desctype | 4 | const | 1879054 | |
| 1 | SIMPLE | i | eq_ref | PRIMARY | PRIMARY | 4 | data.b.invoice | 1 | Using where |
+----+-------------+-------+--------+------------------+----------+---------+----------------+---------+-------------+
2 rows in set (0.05 sec)
And the ridiculously small record counts.
mysql> select count(*) from bill;
+----------+
| count(*) |
+----------+
| 3770990 |
+----------+
1 row in set (0.05 sec)
mysql> select count(*) from invoices;
+----------+
| count(*) |
+----------+
| 2207704 |
+----------+
1 row in set (0.00 sec)
And the table definitions:
CREATE TABLE `invoices` (
`invoice` int(11) NOT NULL auto_increment,
`refnum` int(11) NOT NULL default '0',
`affid` int(10) unsigned default NULL,
`commpaid` int(10) unsigned default NULL,
`from_account` decimal(9,2) default '0.00',
`from_card` decimal(9,2) default '0.00',
`authname` varchar(20) default NULL,
`to_account` decimal(9,2) default '0.00',
`to_card` decimal(9,2) default '0.00',
`charge_vat` enum('Y','N') default 'Y',
`datepaid` date default '0000-00-00',
`euvat_number` varchar(30) NOT NULL default '',
PRIMARY KEY (`invoice`),
KEY `authname` (`authname`),
KEY `refnum` (`refnum`)
) ENGINE=MyISAM AUTO_INCREMENT=2307778 DEFAULT CHARSET=latin1;
CREATE TABLE `bill` (
`authname` varchar(20) NOT NULL default '',
`domainname` varchar(100) NOT NULL default '',
`desctype` int(2) NOT NULL default '0',
`refnum` int(11) NOT NULL default '0',
`cost_year` decimal(9,2) default NULL,
`num_years` int(11) default NULL,
`cost_net` decimal(9,2) default NULL,
`cost_vat` decimal(9,2) default NULL,
`cost_gross` decimal(9,2) default NULL,
`other` int(11) NOT NULL default '0',
`regdate` datetime default NULL,
`invoice` int(11) default NULL,
`currency` varchar(4) NOT NULL default '',
`site` varchar(20) NOT NULL default '',
PRIMARY KEY (`domainname`,`desctype`,`other`),
KEY `authname` (`authname`),
KEY `desctype` (`desctype`),
KEY `domainname` (`domainname`),
KEY `invoice` (`invoice`),
KEY `refnum` (`refnum`),
KEY `regdate` (`regdate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I've tried adding an index on bill.desctype, bill.invoice but the database grinds to a halt.
CREATE INDEX idx_bill_desc_invoice ON bill ( desctype, invoice );
(Sorry for all the edits. I'm trying to make the problem as clear as possible)
Edited 5 time(s). Last edit at 09/03/2007 10:11AM by Curtis Poe.