Grouper Users - Open Discussion List

[Chris Hyzer <>]

It was intentional, just saving space.  I added indexes to all string cols in 
the audit table (6,7,8) in 1.6.0.  Feel free to manually add the indexes in 
other past versions of Grouper

https://bugs.internet2.edu/jira/browse/GRP-444

Thanks!
Chris

-----Original Message-----
From: Colin Hudler 
[mailto:]
 
Sent: Friday, May 28, 2010 2:27 PM
To: 

Subject: [grouper-users] question about grouper_audit_entry table indexes

Grouper 1.5.2 on mysql 5.0.77.  It takes about a minute to load audits 
for a single group using the UI, even when there are no audits for that 
group.  The table has about 2 million entries, however.  I watched the 
query log and it makes a query similar to this:

select ... from grouper_audit_entry where 
((audit_type_id='42e325db85c64f91bd231058c8fa5447' and 
string01='da4909547bb046a6ade95366417a41de')
...
string06='da4909547bb046a6ade95366417a41de') or 
(audit_type_id='cf3e9a639f444ef5a35b7680e9cdb25a' and
....
string02='da4909547bb046a6ade95366417a41de')) order by...;

(some of this was redacted for clarity).

There is no index on string06.  These are the indexes on that table:

   KEY `audit_entry_act_as_idx` (`act_as_member_id`),
   KEY `audit_entry_type_idx` (`audit_type_id`),
   KEY `audit_entry_context_idx` (`context_id`),
   KEY `audit_entry_logged_in_idx` (`logged_in_member_id`),
   KEY `audit_entry_string01_idx` (`string01`(255)),
   KEY `audit_entry_string02_idx` (`string02`(255)),
   KEY `audit_entry_string03_idx` (`string03`(255)),
   KEY `audit_entry_string04_idx` (`string04`(255)),
   KEY `audit_entry_string05_idx` (`string05`(255))

Adding a string06 index makes the query very fast again, less than 1 
second.  I also note that string07 and string08 are not indexed either, 
although this query does not include them.  (I also had to include a 
"use index" to the query, but I think this is a failure of the mysql 
query plan.)

My question is, is this an intentional omission?