Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-33430

user_audits_log table has bad index

    Details

    • Type: Bug
    • Status: Verified
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0, 11.4
    • Fix Version/s: 12.0, 19.0
    • Component/s: Worksite Setup
    • Labels:
      None
    • 12 status:
      Resolved
    • 11 status:
      Please Merge
    • Conversion Script Required:
      Yes
    • Test Plan:
      Hide

      Please add a Test Plan here.

      Show
      Please add a Test Plan here.

      Description

      SAK-23634 created user_audits_log table with index on (id, site_id) but all queries are done against site_id. MySQL does a full table scan.

      ALTER TABLE `user_audits_log`
      MODIFY COLUMN `site_id` varchar(99) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `id`,
      MODIFY COLUMN `role_name` varchar(99) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `user_id`,
      DROP INDEX `user_audits_log_index`,
      ADD INDEX `user_audits_log_index`(`site_id`);

      Here are the queries I see in the logs:

      sql = "select user_id, role_name, action_taken, audit_stamp, source, action_user_id from user_audits_log where site_id = '" + siteId + "' order by audit_stamp desc";
      String sql = "delete from user_audits_log where site_id = ?";
      

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  ottenhoff Sam Ottenhoff
                  Reporter:
                  ottenhoff Sam Ottenhoff
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Git Source Code