Re: Bad performance caused by DISTINCT?

[KimSeong Loh]

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.