Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Bad performance caused by DISTINCT?
November 13, 2006, 8:58 pm
rate this thread
SELECT DAY(date_time) "day", COUNT( DISTINCT session_id ) "count"
WHERE (retailer_id = '111111-1111-11111')
AND(year_for_reports = 2006)
AND(month_for_reports = 9)
GROUP BY DAY( date_time );
EXPLAIN returns this:
"1" | "SIMPLE" | "application_requests" | "ref" | "index_for_reports" |
"index_for_reports" | "53" | "const,const,const" | "998840" | "Using
where; Using filesort"
index_for_reports is (retailer_id, year_for_reports, month_for_reports)
Table structure goes as follow:
Any idea how I could make this faster? Right now I'm clocking at 555703
ms, which is intolerable.
Re: Bad performance caused by DISTINCT?
A couple of things spring to mind, although i don't know what, if
anything, any of these will actually do for performance:
Could retailer ID be stored as an integer? if you needed the hyphens
for display purposes you could use something like this:
SELECT INSERT (
varchar 255 for session_id sounds very large. Could it be smaller?
month_for_reports is only ever going to be 1-12 so it might as well be
other than that, make sure everything is properly indexed - but I guess
you've done that already?
- » 'Access denied for user 'root'@'localhost' (using password: NO)'
- — Next thread in » MySQL Database Forum
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum