MySQL Forums
Forum List  »  Optimizer & Parser

Re: Bad performance caused by DISTINCT?
Posted by: KimSeong Loh
Date: November 19, 2006 09:29PM

Try this and see what do you have,

SELECT count(*)
FROM application_requests
WHERE (retailer_id = '111111-1111-11111')
AND(year_for_reports = 2006)
AND(month_for_reports = 9)

Increase your sort_buffer_size to about 300 * the value returned.

From your EXPLAIN, it is about 1 million rows, if it is indeed so, you will need about 300MB of sort buffer to do the sorting in memory. The slow speed is probably cause by the sort merge using the disk.
You can confirm this with
SHOW STATUS LIKE 'sort_merge_passes';
before and after your query and check the difference, zero difference mean in-memory sort, and non-zero means disk merge sort.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Bad performance caused by DISTINCT?
4343
November 19, 2006 09:29PM
3387
December 13, 2006 04:46AM


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.