Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    12

    Unanswered: Query performance

    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

  2. #2
    Join Date
    Feb 2005
    Location
    Germany
    Posts
    22
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •