The following query takes around 10 minutes to return resuls.

SELECT act.first_name,act.last_name,birth_date,STR_TO_DAT E(log_entry_time, '%Y-%m-%d') AS log_entry_date,act.badge_id AS badgeid,act.idx,sdotid, '1' AS status FROM user_master AS um RIGHT JOIN activity As act ON (act.badge_id = um.badge_id) WHERE sdotid = 50 GROUP BY log_entry_date, act.badge_id

Some additional info:

1. badge_id on both tables do not have indexes. I am in the process getting them created.

2. Max number of rows in both tables does not exceed 50000.

Any ideas to improve the performance?

Thanks,
Des

to speed up the GROUP BY log_entry_date, act.badge_id there are several sort-buffers, increase them.

if sdotid = 50 sorts out a small number of lines, create an index on this column as well.

do an optimize table on the tables.
Greetings, Martin