MySQL Forums
Forum List  »  Performance

Re: Simple Join Taking Too Long
Posted by: Björn Steinbrink
Date: September 08, 2007 03:37AM

How common is a value of 0 for b.desctype? If the shown value of 1.8m rows is accurate, you're probably better off _without_ the desctype index, as that basically just causes a lot of random seeks instead of sequential reads.

SELECT
    b.authname AS b_authname,
    i.authname AS i_authname
FROM
    bill b IGNORE INDEX (desctype)
INNER JOIN
    invoices i USING (invoice)
WHERE
    i.affid = 654248 AND
    b.desctype = 0 AND
    i.commpaid = 0;

But more interestingly looking is invoices.affid. How about creating an index for that column?

To speed up the index creation, increase the value of the myisam_sort_buffer_size variable for the session in which you create the index. My usual choice is about the amount of memory I can safely assign to that job without having to fear that the box will start to swap stuff out ;-)

Options: ReplyQuote


Subject
Views
Written By
Posted
5110
September 03, 2007 08:33AM
2019
September 03, 2007 08:37AM
Re: Simple Join Taking Too Long
2204
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.