MySQL Forums
Forum List  »  Performance

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Simple Join Taking Too Long
5110
September 03, 2007 08:33AM
2019
September 03, 2007 08:37AM
2205
September 08, 2007 03:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.