Bad performance caused by DISTINCT?

[Heist]

This is the query:
SELECT DAY(date_time) "day", COUNT( DISTINCT session_id ) "count"
FROM application_requests
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:
id(int)
retailer_id(varchar[40])
date_time(datetime)
session_id(varchar[255])
controller(varchar[255])
action(varchar[255])
parameters(text)
product_line_id(int)
merchandise_id(int)
month_for_reports(int)
year_for_reports(int)

Any idea how I could make this faster? Right now I'm clocking at 555703
ms, which is intolerable.

[strawberry]

Heist wrote:

This is the query:
SELECT DAY(date_time) "day", COUNT( DISTINCT session_id ) "count"
FROM application_requests
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:
id(int)
retailer_id(varchar[40])
date_time(datetime)
session_id(varchar[255])
controller(varchar[255])
action(varchar[255])
parameters(text)
product_line_id(int)
merchandise_id(int)
month_for_reports(int)
year_for_reports(int)

Any idea how I could make this faster? Right now I'm clocking at 555703
ms, which is intolerable.

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 (

INSERT (
retailer_id,
7,
0,
'-'
),
12,
0,
'-'
)

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
tinyint

other than that, make sure everything is properly indexed - but I guess
you've done that already?
HIH

[Heist]

retailer_id cannot be stored as an integer sadly, but session_id could
be smaller. And yes, indexes are seemingly optimal. Thanks for the
answer.

strawberry wrote:

Heist wrote:

This is the query:
SELECT DAY(date_time) "day", COUNT( DISTINCT session_id ) "count"
FROM application_requests
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:
id(int)
retailer_id(varchar[40])
date_time(datetime)
session_id(varchar[255])
controller(varchar[255])
action(varchar[255])
parameters(text)
product_line_id(int)
merchandise_id(int)
month_for_reports(int)
year_for_reports(int)

Any idea how I could make this faster? Right now I'm clocking at 555703
ms, which is intolerable.

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 (

INSERT (
retailer_id,
7,
0,
'-'
),
12,
0,
'-'
)

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
tinyint

other than that, make sure everything is properly indexed - but I guess
you've done that already?
HIH

[strawberry]

Heist wrote:

retailer_id cannot be stored as an integer sadly, but session_id could
be smaller. And yes, indexes are seemingly optimal. Thanks for the
answer.

You're welcome. Sorry it wasn't much help.